CODEWITHSUNDEEP
sqlsql-serverpivottranspose
b135i11
b135i11

Reputation: 23

SQL Transposing columns to rows

I'm attempting to transpose a column of text and values to row headers. I've researched the PIVOT and UNPIVOT function but this function relies on aggregation from what I've gathered. Below is what I'm interested in achieving.

Source Table Schema:

    [ID]    [Category]  [TextName]
       1         A          u
       1         B          v
       1         C          w
       2         A          x
       2         B          y
       2         C          z

Resulting transpose:

[ID]    [A]   [B]   [C]
   1     u     v     w
   2     x     y     z

Is this possible?

Upvotes: 2

Views: 221

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

This is still a kind of aggregation even that we have a single value per cell (row-column combination).
Min/Max will give you the desired values since any basic type including strings have definition of Min/Max. 

select  *
from    t pivot (min([TextName]) for [Category]  in (A,B,C)) p    

+----+---+---+---+
| ID | A | B | C |
+----+---+---+---+
| 1  | u | v | w |
+----+---+---+---+
| 2  | x | y | z |
+----+---+---+---+

Upvotes: 1

Teja
Teja

Reputation: 13544

SELECT id,
       MIN( CASE WHEN Category = 'A' THEN TextName END ) AS A,
       MIN( CASE WHEN Category = 'B' THEN TextName END ) AS B,
       MIN( CASE WHEN Category = 'C' THEN TextName END ) AS C
  FROM Table
GROUP BY id;

Upvotes: 1

Related Questions