SQL将多行组合成一行

问题描述：

我试图将具有相同ID的行放回同一行。我的数据看起来像如下：SQL将多行组合成一行

ID Fruit 
1 Banana 
1 Apple 
1 Grapefruit 
2 Cherry 
2 Blueberry 
3 Lime 
3 Pear

而且我想它看起来就像这样：

ID Fruit Fruit1 Fruit2 
1 Banana Apple Grapefruit 
2 Cherry Blueberry NULL

我已经试过这是一个查询，但我似乎并没有被多少运气：

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

有人可以帮忙吗？

谢谢！

的可能的复制[如何在数据库中旋转未知数量的列和没有聚合？]（https://stackoverflow.com/questions/22772481/how-to-pivot-unknown-number-of-columns-no-aggregate-in-sql-server ） –

答

可以使用与MAX()一个CASE表达式得到的结果是你想要使用像row_number视窗函数的组合，然后一些有条件聚集：

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;

看到一个Demo。 row_number()函数为每个id创建一个唯一编号，然后使用此编号以及CASE和MAX，您将将数据行转换为列。

这工作得很好，很容易让我轻松掌握，谢谢！ – Samatag

答

可以使用枢轴如下做到这一点：

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

答

如果水果数量不固定，你可以使用动态脚本：

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 ('[email protected]+')) 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

SQL将多行组合成一行

相关推荐