CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008pivot
stoniel2
stoniel2

Reputation: 93

Display Rows side by side, Kind of pivot

I have a table like this

Column1 | Column2
-------------------
A       | 1
A       | 2
A       | 3
B       | 4
B       | 5
B       | 3
C       | 2
C       | 2
C       | 2
D       | 7
D       | 8
D       | 9

I want to output it as 

A  |  B  |  C  |  D
--------------------
1  |  4  |  2  |  7
2  |  5  |  2  |  8
3  |  3  |  2  |  9

It will have fixed Rows/Columns like A,B,C,D. Could you suggest a query in SQL Server 2005/2008?

Upvotes: 0

Views: 991

Answers (1)

rsdot
rsdot

Reputation: 11

it's better to know your clustered key in the table, since the order might be different after the result. Martin is right, try this out, it will get you started:

SELECT pvt.A,
       pvt.B,
       pvt.C,
       pvt.D
FROM   (SELECT *,
               row=ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY (SELECT 1))
        FROM   yourtable) AS A 
        PIVOT (MIN(Column2) FOR Column1 IN ([A], [B], [C], [D]))
       AS pvt

Upvotes: 1

Related Questions