3

Imagine I have this table:

Column A | Column B | Column C
------------------------------
   111         X        10
   111         Y        12

How can I query this table to show the results like these:

Column A |     X     |      Y
-----------------------------------
   111         10           12
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Eduardo Brites
  • 4,597
  • 5
  • 36
  • 51
  • 1
    What exactly is not clear after reading the many online examples of the `pivot` operator? What have you tried already? – fvu Jun 27 '12 at 16:25
  • 1
    Note that although pivoting inside SQL server is functional and can help in certain situations, if you exceed around 10 pivots (columns) then performance will drop like a rock. – NotMe Jun 27 '12 at 17:04
  • @ChrisLively - Thanks so much for this useful info! – Caffeinated Jul 10 '12 at 19:25

2 Answers2

7

You can perform this via a PIVOT. You can use either a static PIVOT where you know the number of columns that you want to rotate or you can use a dynamic PIVOT

Static Pivot (see SQL Fiddle with Demo)

SELECT *
FROM 
(
  select *
  from t1
) x
pivot
(
  min(columnc)
  for columnb in ([X], [Y])
) p

Dynamic Pivot (see SQL Fiddle with Demo)

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

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

set @query = 'SELECT columna, ' + @cols + ' from 
             (
                select *
                from t1
            ) x
            pivot 
            (
                min(ColumnC)
                for ColumnB in (' + @cols + ')
            ) p '

execute(@query)

Both versions will give the same results. The second works when you have an unknown number of columns that will be transformed.

Taryn
  • 242,637
  • 56
  • 362
  • 405
3

Try:

DECLARE @tbl TABLE (ColumnA INT, ColumnB CHAR(1), ColumnC INT)
INSERT @tbl VALUES (111, 'X', 10), (111, 'Y', 12)

SELECT  *
FROM    @tbl
PIVOT   
(
    MAX(ColumnC) FOR ColumnB IN ([X], [Y])
) pvt
Ivan Golović
  • 8,732
  • 3
  • 25
  • 31