CODEWITHSUNDEEP
sqlsql-serverpivot
Aldo
Aldo

Reputation: 303

SQL Server: PIVOT data stored in multiple rows to columns

I'm trying to pivot a data that is stored in this way:

ID|Text     |A_ID
--+---------+-------
01|WestPC   |4
01|Win7     |5
01|i3-3240  |6
02|EastPC   |4
02|WinXP    |5
02|i5-6500  |6

and i need to pivot it to obtain this:

ID|4      |5    |6
--+-------+-----+--------
01|WestPC |Win7 |i3-3240
02|EastPC |WinXP|i5-6500

so far, I've come to this code:

with ComputerData as (
    SELECT ID,Text,A_ID
    FROM MyTable
)

Select ID,[4],[5],[6]
from (
    select ID,A_ID,Text
    from ComputerData 
) c
PIVOT 
(
    Text
    FOR A_ID in (4,5,6)
) P;

but it is not working.. what I'm doing wrong (all SQL Server tells me is I got a syntax error, but I can't understand which one).. can someone please help me? Many thanks!

Upvotes: 0

Views: 149

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

If you still want the PIVOT. Personally, I prefer the conditional aggregation. It allows for a bit more flexibility.

Example

with ComputerData as (
    SELECT ID,Text,A_ID
    FROM MyTable
)
Select *
 From  ComputerData src
 Pivot (max([Text]) for A_ID in ([4],[5],[6]) ) P

Returns

ID  4       5       6
01  WestPC  Win7    i3-3240
02  EastPC  WinXP   i5-6500

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Just use conditional aggregation:

select id,
       max(case when a_id = 4 then text end) as [4],
       max(case when a_id = 5 then text end) as [5],
       max(case when a_id = 6 then text end) as [6]
from mytable
group by id;

Your specific problem is probably the lack of escape characters around "4", "5", and "6". SQL interprets them as numbers.

Upvotes: 3

Related Questions