Hi,I'm trying to write some code to convert a set of rows into different columns. This is unlike a pivot table, as best I understand, because I do not want any summarization performed.. instead I want to perform a set of SELECTs on this data afterwards to get the info I need.The tables I am using look something like this:CREATE TABLE #CrossTab (batchid int,testid int,attributeid int,value varchar(20))CREATE TABLE #Attributes (attributeid int,attribute varchar(20))-- Insert some fake data into the CrossTab tableINSERT #CrossTab VALUES (1,1,1,'123')INSERT #CrossTab VALUES (1,1,2,'5')INSERT #CrossTab VALUES (1,1,3,'1')INSERT #CrossTab VALUES (1,1,4,'0')INSERT #CrossTab VALUES (1,2,1,'100')INSERT #CrossTab VALUES (1,2,2,'5')INSERT #CrossTab VALUES (1,2,3,'1')INSERT #CrossTab VALUES (1,2,4,'0')INSERT #CrossTab VALUES (1,3,1,'109')INSERT #CrossTab VALUES (1,3,2,'5')INSERT #CrossTab VALUES (1,3,3,'0')INSERT #CrossTab VALUES (1,3,4,'1')-- Insert some fake attributesINSERT #Attributes VALUES (1,'Time')INSERT #Attributes VALUES (2,'Spelling')INSERT #Attributes VALUES (3,'Grammar')INSERT #Attributes VALUES (4,'Greeting')
What I want to have at the end of the process is a table with the following structure:BatchID TestID Time Spelling Grammar Greeting=======================================================1 1 123 5 1 01 2 100 5 1 01 3 109 5 0 1
I was working on something that created the temp table and then looped over using a cursor (eek!) to ALTER the table. Problem with this approach is that I would also need to write a CASE statement on the fly that maps the ids to the column names..I looked at the crosstab SP and tried it, but I get a Invalid object name ##pivot and also Invalid column name on the column I specify to pivot on.I'm just not sure if it would be possible to do what I want using the crosstab SP created by robvolk (http://www.sqlteam.com/item.asp?ItemID=2955).Is there another way to accomplish this?Thanks in advance,Greg
Convert Rows to Columns, kinda Pivot Table
Posted - 2001-09-07 : 16:52:09