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?](https://stackoverflow.com/questions/22772481/how-to-pivot-unknown-number-of-columns-no-aggregate-in-sql-server) — Tab Alleman, May 30 '17 at 16:07

score 1 · Answer 1 · answered May 30 '17 at 18:27

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

score 0 · Accepted Answer · edited Jan 18 '21 at 12:32

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.

This worked well and was simple enough for me to grasp easily, thanks! — Samatag, May 31 '17 at 08:53

score 0 · Answer 3 · answered May 30 '17 at 16:06

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 '17 at 16:06

Kannan Kandasamy

13,405
3
25
38

SQL combine multiple rows into 1 row

3 Answers3