SQL SERVER PIVOT WITHOUT AGGREGATION

Question

I have a query whose result set looks like the below table.
The Name column has a "C1, C2, C3" set which is well known

 Id | Name  | Value
-------------------------------
 1  | C1    | 1C1
 1  | C2    | 1C2
 1  | C3    | 1C3
 2  | C1    | 2C1
 2  | C2    | 2C2
 2  | C3    | 2C3

I need to transpose it to the following

 Id | C1  | C2  | C3
-------------------------------
 1  | 1C1 | 1C2 | 1C3
 2  | 2C2 | 2C2 | 2C3

Tried to achieve it with Pivots but they don't seem to play nice for string values (i.e.) non-aggregates. Any pointers on how I can go about this ?

MAX() is an aggregate that can work with strings and there's no point in providing special facilities for when the developer happens to know that each aggregate will only receive one row's value. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 27, 2014 at 15:04

jpw · Accepted Answer · 2014-10-27 15:15:22Z

1

This code is pretty much taken from the sample in the SQL Server documentation; the subquery isn't really needed here, but that's how it's written in the sample.

SELECT * FROM (
    SELECT Id, Name, Value 
    FROM your_table_or_query
    ) AS p
PIVOT
    (MAX(value) 
    FOR Name IN
       (C1,C2,C3)
) AS pvt
ORDER BY ID;

Output:

Id  C1  C2  C3
1   1C1 1C2 1C3
2   2C1 2C2 2C3

answered Oct 27, 2014 at 15:15

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Lentine · Accepted Answer · 2014-10-27 15:14:55Z

1

Something like the following should do the trick:

DECLARE @source TABLE (ID INT NOT NULL, 
                      [Name] VARCHAR(20) NOT NULL, 
                      [Value] VARCHAR(20) NOT NULL);

INSERT INTO @source ( ID, Name, Value )
VALUES  (1,'C1','1C1'),
        (1,'C2','1C2'),
        (1,'C3','1C3'),
        (2,'C1','2C1'),
        (2,'C2','2C2'),
        (2,'C3','2C3');

 SELECT ID, [C1], [C2], [C3]
 FROM @source
 PIVOT (MAX([Value]) FOR [Name] IN ([C1], [C2], [C3])) AS pivotTable;

answered Oct 27, 2014 at 15:14

Tim Lentine

7,9225 gold badges38 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL SERVER PIVOT WITHOUT AGGREGATION

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related