Help with a matrix or pivot table?

I'm trying to create a table that is a combination of two tables, and the number of columns is dynamic. So I have 2 tables, Students and Assignments. I'd like to get a result with the students on the left and the assignments across the top. I'm not sure where to start, any help would be great. Thanks

What your describing is not (1) a table, (2) a view nor (3) a table valued function because none of these can contain a dynamic number of columns. Where you need to start is by describing more clearly your needs.

If you are using SQL Server 2005 you probably use the ROW_NUMBER() function and PIVOT to slot different classes into ordinally assigned columns. This might be done with either a view or a TVF; however, if you truly need a dynamic number of columns, first rethink this at least a little. Once you decide you need a dynamic number of columns, you will need to settle with dynamic SQL.

|||

I'll try to be more clear. I need to display a table that has a row for each student. For each student row I'd like to have a column for each assignment completed. The tables look like this. The number of assignments could be 0 to 100, so the number of columns is dynamic.

tblStudent

StudentId

Name

tblAssignment

AssignmentId

StudentId

Score

Example

Assignment 1 | Assignment 2 | Assignment 3

Student X 10 20 30

Student Y 5 15 40

Help using dynamic queries in temp table

The dynamic sql is used for link server. Can someone help. Im getting an error

CREATEPROCEDURE GSCLink

(

@.LinkCompanynvarchar(50),@.Pageint,@.RecsPerPageint)

AS

SET

NOCOUNTON

--Create temp table

CREATE

TABLE #TempTable

(

IDintIDENTITY,Companynvarchar(50),AcctIDint,IsActivebit)

INSERT

INTO #TempTable(Name, AccountID, Active)

--dynamic sql

DECLARE

@.sqlnvarchar(4000)

SET

@.sql='SELECT a.Name, a.AccountID, a.Active

FROM CRMSBALINK.'

+ @.LinkCompany+'.dbo.AccountTable a

LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b

ON a.AccountID = b.oaAccountID

WHERE oaAccountID IS NULL

ORDER BY Name ASC'

EXEC

sp_executesql @.sql

--Find out the first and last record

DECLARE

@.FirstRecint

DECLARE

@.LastRecint

SELECT

@.FirstRec=(@.Page- 1)* @.RecsPerPage

SELECT

@.LastRec=(@.Page* @.RecsPerPage+ 1)

--Return the set of paged records, plus an indication of more records or not

SELECT

*,(SELECTCOUNT(*)FROM #TempTable TIWHERE TI.ID>= @.LastRec)AS MoreRecords

FROM

#TempTable

WHERE

ID> @.FirstRecAND ID< @.LastRec

Error:

Msg 156, Level 15, State 1, Procedure GSCLink, Line 22

Incorrect syntax near the keyword 'DECLARE'.

Here is the problem:

INSERT

INTO #TempTable(Name, AccountID, Active)

It could be something like this:

INTO #TempTable values('Name', 'AccountID', 'Active')

or INTO #TempTable values(@.Name, @.AccountID, @.Active) (if those variables are defined)

or ...

Hope it helps.

|||Isnt the result of the dynamic sql put on the temp table automatically? If not... how can I capture the results of the dynamic sql?|||I got it working... Thanks anyway

Help required: Using OLE Automation SPs

Hi,
I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
execute a piece of dynamic SQL.
My dynamic SQL needs to return a resultset so I basically did the following:
SET @.vSQL = 'SELECT * FROM MyTable'
EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
I now have the contents of MyTable in @.vResult (which is a handle). I used
the following to return the results as a string into @.pResults (which is a
varchar):
EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
This is all well and good but I don't want the results returned as a string,
I want it returned as a recordset which will ultimately be the output from
the SP that wraps all of this up. Furthermore the max string that I can
return is 8000 chars (i.e. max length of a varchar) so with any decent sized
dataset this doesn't return everything anyway.
So my question is: Once I have the results as handled by @.vResult...how do I
navigate over it and return the results as a normal rowset? The
GetRangeString function is obviously not the answer.
Thanks in advance
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspxWhat is @.vObject a handle to? Normally you don't need to use OLE Automation
SPs to execute dynamic SQL. I think you will have to explain a little bit
better why you want to shove a table into an output variable and then
process it back into a resultset again. Why not just EXEC('SELECT * FROM
MyTable') ... (and I assume that dynamic SQL is actually required here for
some reason, and that your query is a little more complicated than this...
otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> Hi,
> I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
> execute a piece of dynamic SQL.
> My dynamic SQL needs to return a resultset so I basically did the
following:
> SET @.vSQL = 'SELECT * FROM MyTable'
> EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
> I now have the contents of MyTable in @.vResult (which is a handle). I used
> the following to return the results as a string into @.pResults (which is a
> varchar):
> EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
> This is all well and good but I don't want the results returned as a
string,
> I want it returned as a recordset which will ultimately be the output from
> the SP that wraps all of this up. Furthermore the max string that I can
> return is 8000 chars (i.e. max length of a varchar) so with any decent
sized
> dataset this doesn't return everything anyway.
>
> So my question is: Once I have the results as handled by @.vResult...how do
I
> navigate over it and return the results as a normal rowset? The
> GetRangeString function is obviously not the answer.
> Thanks in advance
>
> Regards
> Jamie Thomson
> An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
>
>|||Hi Aaron,
Yes, you're absolutely right. My real query is alot more complicated than
this. :)
The reason I am doing it is a permissions issue. Under our current
application security model the calling app (an ASP.Net app) doesn't have
access to MyTable therefore EXEC(...) doesn't work. I am not allowed to
change the security model either (its not under my control) :o( but I AM
allowed to access it using this method.
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:udXW3iSGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> What is @.vObject a handle to? Normally you don't need to use OLE
> Automation
> SPs to execute dynamic SQL. I think you will have to explain a little bit
> better why you want to shove a table into an output variable and then
> process it back into a resultset again. Why not just EXEC('SELECT * FROM
> MyTable') ... (and I assume that dynamic SQL is actually required here for
> some reason, and that your query is a little more complicated than this...
> otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
> message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> following:
> string,
> sized
> I
>