CODEWITHSUNDEEP
sql-servert-sql
kernelman
kernelman

Reputation: 41

SQL SERVER PIVOT WITHOUT AGGREGATION

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 ?

Upvotes: 0

Views: 70

Answers (2)

jpw
jpw

Reputation: 44891

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

Upvotes: 1

Tim Lentine
Tim Lentine

Reputation: 7862

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;

Upvotes: 1

Related Questions