tSQL - Transpose rows to columns

[Andy Nguyen]

[FONT=Verdana, Arial, Helvetica]Already posted this question in the approriate sql forum but like to see if anyone on Quantnet wants to take a shot at this.

I have a sproc that takes a date and produces something like this
1AAA
2AAA
3AAA
...

If I run this sproc against some dynamic range of dates, I will have this result
1AAA
2AAA
3AAA
1BBB
2BBB
3BBB
1CCC
2CCC
3CCC
1DDD
2DDD
3DDD

AAA,BBB are just to indicate they belong to the same date.

I'd like to have my result displayed in this way (one column for a date)
1AAA 1BBB 1CCC 1DDD
2AAA 2BBB 2CCC 2DDD
3AAA 3BBB 3CCC 3DDD[/FONT]

 

[alain]

Access has some function called Pivot that does the trick. It might be in the latest version of SQL Server. If not, Dynamic SQL is your friend. Google for pivoting tables.

 

[Chris]

Also if you are using Visual Studio you can use SQL Server Reporting Services and use the Matrix to pivot your SQL data so you don't have to go into writing complicated queries.

 

[Andy Nguyen]

I'll keep that in mind when I use VS, Chris.
For the time being, looks like I'm stuck with writing complicated queries to dump the data into Excel models.

 

[joe_bradley]

Hi Andy,

You've probably figured that there isn't a straighforward command to do this.
If you are *forced* to do SQL queries to produce a "crosstab" report, you can try something like this: (excuse me if my syntax is rusty; I don't do SQL enough to know the syntax off the top of my head)

SELECT
MAX(CASE IF [condition to test for AAAA] then [AAA field] else '') AS colA,
MAX(CASE IF [condition to test for BBBB] then [BBB field] else '') AS colB,
etc. etc.
GROUP BY colA, colB, etc.

the idea being that if there is a unique match for each condition statement, then the MAX will match the "maximum" string, i.e. the only non-empty one.

Yes it's ugly, but I've had to write these sorts of reports myself and this is how I did it.

 

[Andy Nguyen]

My prayers have been answered. Here is the solution to my problem using PIVOT. For future reference, I'm posting it here.
I want to transpose
[high]2008-02-10 4.5
2008-02-10 3.7
2008-02-09 4.6
2008-02-09 5.5 [/high]to
[high]2008-02-10 | 2008-02-9
----------------------------
4.5 | 4.6
3.7 | 5.5[/high]Prepare the data
[high]CREATE TABLE TEMP_TAB (
MY_DATE DATETIME,
MY_VALUE FLOAT)

INSERT INTO TEMP_TAB (MY_DATE, MY_VALUE)
SELECT '2/10/2008', 4.5
UNION ALL
SELECT '2/10/2008', 3.7
UNION ALL
SELECT '2/9/2008', 4.6
UNION ALL
SELECT '2/9/2008', 5.5

SELECT * FROM TEMP_TAB[/high]
Pivot the table
[high]
DECLARE @cols NVARCHAR(2000)

SELECT @COLS = COALESCE(@COLS + ',[' + CONVERT(VARCHAR(10),A.MY_DATE,101) + ']',
'[' + CONVERT(VARCHAR(10),A.MY_DATE,101) + ']')
FROM (SELECT DISTINCT MY_DATE AS MY_DATE
FROM TEMP_TAB) A
ORDER BY A.MY_DATE DESC

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT ' + @cols + '
FROM ( select row_number() over (partition by my_date order by my_date) as rowid, my_date, my_value from temp_tab
) p PIVOT ( SUM(my_value)
FOR [my_date]
IN (' + @cols + ' )
) AS pvt;'

EXECUTE( @query)

DROP TABLE TEMP_TAB[/high]