I am trying to take rows with the same ID and return them on the same row. My data looks like the follow:
ID Fruit
1 Banana
1 Apple
1 Grapefruit
2 Cherry
2 Blueberry
3 Lime
3 Pear
And I would like it to look like this:
ID Fruit Fruit1 Fruit2
1 Banana Apple Grapefruit
2 Cherry Blueberry NULL
I have tried this as a query, but I don't seem to be having much luck:
SELECT a.[ID],a.[Fruit],b.[Fruit]
FROM [test].[dbo].[Fruit] a
JOIN [test].[dbo].[Fruit] b
ON a.ID = b.ID
WHERE a.FRUIT <> b.FRUIT
Can anybody help with this?
Thanks!
You can use combination of a windowing function like row_number and then some conditional aggregation using a CASE expression with MAX() to get the result that you want:
select
Id,
Fruit = max(case when rn = 1 then Fruit end),
Fruit1 = max(case when rn = 2 then Fruit end),
Fruit2 = max(case when rn = 3 then Fruit end)
from
(
select
Id,
Fruit,
rn = row_number() over(partition by Id order by Id)
from [test].[dbo].[Fruit]
) d
group by Id;
See a Demo. The row_number() function creates a unique number for each id, then using this number along with CASE and MAX you will convert your rows of data into columns.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments