Transposing Rows to columns without aggregation and Displaying column header as First column In sql server 2008

Question

My Input is :

date      Costed   Celldata
8-Apr-13    1       15
15-Apr-13   1       24
22-Apr-13   1       36

And I need output as :

date       8-Apr-13  15-Apr-13 22-Apr-13
Costed        1         1          1         
Celldata     15         24         36

Query I have applied is :

CREATE TABLE testing1(Spot_dt date, Costed INT, celldata INT)

INSERT INTO testing1 (Spot_dt,Costed,celldata) values ('2012-04-08',1,15);
INSERT INTO testing1 (Spot_dt,Costed,celldata) values ('2012-04-15',1,24);
INSERT INTO testing1 (Spot_dt,Costed,celldata) values ('2012-04-22',1,36);


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Spot_dt) 
                    from testing1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Costed,' + @cols + ' 
             from 
             (
                select Spot_dt, Costed, CellData
                from testing1
            ) x
            pivot 
            (
                min(CellData)
                for Spot_dt in (' + @cols + ')
            ) p '

execute(@query)

And Out put I am recieving is:

8-Apr-13    15-Apr-13   22-Apr-13   
1           1           1           
15          24          36

So Basically I need an extra column in beginning in which i can have column headers coming as row values.

Possible duplicate of http://stackoverflow.com/questions/428041/sql-server-2005-turn-columns-into-rows and probably many more. Please search before posting a question. — lc., Apr 25 '13 at 07:24
The data in your supplied code doesn't match that in the table of input data at the start of your question - in particular, there's no uncosted data in your code. Also, the output you say you want exactly matches the output you say you are receiving (which doesn't match the output from your supplied code). Please can you clarify your question? — , Apr 25 '13 at 07:29
Hi @MarkBannister, Thanks For your qucik reply. I have posted Correct data and Query Now. Waiting for your response. My main concern here is the first column that i need in output. Column header of first table should come as row values. Like we do simple transpose in EXCEL. — Panwar, Apr 25 '13 at 07:44
@lc. - This question is more complicated than a simple pivot - it is similar to http://stackoverflow.com/questions/4050086 , but with the added complication of using dynamic SQL (for dynamically selected columns). — , Apr 25 '13 at 08:27

score 0 · Accepted Answer · answered Apr 25 '13 at 08:24

The following should work:

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Spot_dt) 
                    from testing1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT valtype dates, ' + @cols + ' 
              from (select Spot_dt, Costed, CellData from testing1) as x
              unpivot (vals for valtype in (Costed, celldata)) as u
              pivot (min(vals) for spot_dt in (' + @cols + ')) as p'

execute(@query)

(Before pivoting the data around date, you need to unpivot the separate columns so that each column in your initial dataset gets its own row for each date.)

SQLFiddle here.

Transposing Rows to columns without aggregation and Displaying column header as First column In sql server 2008

1 Answers1