SQL combine multiple rows into 1 row

Question

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!

Possible duplicate of How to pivot unknown number of columns & no aggregate in SQL Server? — Tab Alleman
– Tab Alleman, Commented May 30, 2017 at 16:07

Nolan Shang · Accepted Answer · 2017-05-30 18:27:00Z

If the fruit count is not fixed, you can you use dynamic script:

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(ID INT,Fruit VARCHAR(100))
INSERT INTO #t(ID,Fruit)
SELECT 1,'Banana' UNION
SELECT 1,'Apple' UNION
SELECT 1,'Grapefruit' UNION
SELECT 2,'Cherry' UNION
SELECT 2,'Blueberry' UNION
SELECT 3,'Lime' UNION
SELECT 3,'Pear'
DECLARE @sql NVARCHAR(max),@cols VARCHAR(max)
SELECT @cols=ISNULL(@cols+',','')+t.col FROM (
   SELECT *,'Fruit'+LTRIM(ROW_NUMBER()OVER(PARTITION BY ID ORDER BY(SELECT 1) )) AS col FROM #t AS t
) AS t GROUP BY t.col




SET @sql='
SELECT * FROM (
   SELECT *,''Fruit''+LTRIM(ROW_NUMBER()OVER(PARTITION BY ID ORDER BY(SELECT 1) )) AS col FROM #t AS t
) AS t  PIVOT(MAX(Fruit) FOR col in ('+@cols+')) p
'
PRINT @sql
EXEC(@sql)
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t

ID          Fruit1     Fruit2     Fruit3
----------- ---------- ---------- ----------
1           Apple      Banana     Grapefruit
2           Blueberry  Cherry     NULL
3           Lime       Pear       NULL

Community · Accepted Answer · 2021-01-18 12:32:17Z

0

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.

edited Jan 18, 2021 at 12:32

CommunityBot

11 silver badge

answered May 30, 2017 at 16:04

ollie

1,0091 gold badge8 silver badges11 bronze badges

1 Comment

Samatag Over a year ago

This worked well and was simple enough for me to grasp easily, thanks!

Kannan Kandasamy · Accepted Answer · 2017-05-30 16:06:42Z

0

you can use pivot to do this as below:

Select Id, [0] as Fruit, [1] as [Fruit1], [2] as [Fruit2] from (
    Select *, RowN = Row_Number() over (partition by Id order by Fruit) - 1 from yourtable )
pivot ( max(Fruit) for RowN in ([0], [1],[2]) ) p

answered May 30, 2017 at 16:06

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL combine multiple rows into 1 row

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related