Reputation: 303
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
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
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