在 SQL Server 中将多行动态合并为多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21692871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine multiple rows into multiple columns dynamically in SQL Server
提问by chabu
I have a large database table on which I need to perform the action below dynamically using Microsoft SQL Server.
我有一个大型数据库表，我需要使用 Microsoft SQL Server 在该表上动态执行以下操作。
From a result like this:
从这样的结果：
badge | name | Job | KDA | Match
- - - - - - - - - - - - - - - -
T996 | Darrien | AP | 3.0 | 20
T996 | Darrien | ADC | 2.8 | 16
T996 | Darrien | TOP | 5.0 | 120
To a result like this using SQL:
使用 SQL 得到这样的结果：
badge | name | AP_KDA | AP_Match | ADC_KDA | ADC_Match | TOP_KDA | TOP_Match
- - - - - - - - -
T996 | Darrien | 3.0 | 20 | 2.8 | 16 | 5.0 | 120
Even if there are 30 rows, it also will combine into a single row with 60 columns.
即使有 30 行，它也会合并为 60 列的单行。
I am currently able to do it by hard coding (see the example below), but not dynamically.
我目前可以通过硬编码来完成（请参见下面的示例），但不能动态完成。
Select badge,name,
(
SELECT max(KDA)
FROM table
WHERE (h.badge = badge) AND (h.name = name)
AND (Job = 'AP')
) AP_KDA,
(
SELECT max(Match)
FROM table
WHERE (h.badge = badge) AND (h.name = name)
AND (Job = 'AP')
) AP_Match,
(
SELECT max(KDA)
FROM table
WHERE (h.badge = badge) AND (h.name = name)
AND (Job = 'ADC')
) ADC_KDA,
(
SELECT max(Match)
FROM table
WHERE (h.badge = badge) AND (h.name = name)
AND (Job = 'ADC')
) ADC_Match,
(
SELECT max(KDA)
FROM table
WHERE (h.badge = badge) AND (h.name = name)
AND (Job = 'TOP')
) TOP_KDA,
(
SELECT max(Match)
FROM table
WHERE (h.badge = badge) AND (h.name = name)
AND (Job = 'TOP')
) TOP_Match
from table h
I need an MSSQL statement that allows me to combine multiple rows into one row. The column 3 (Job) content will combine with the column 4 and 5 headers (KDAand Match) and become a new column.
我需要一个 MSSQL 语句，它允许我将多行合并为一行。第 3 列 ( Job) 内容将与第 4 列和第 5 列标题 (KDA和Match)合并，成为一个新列。
So, if there are 6 distinct values for Job(say Job1through Job6), then the result will have 12 columns, e.g.: Job1_KDA, Job1_Match, Job2_KDA, Job2_Match, etc., grouped by badge and name.
所以，如果有6倍不同的值Job（比如Job1通过Job6），那么结果将有12列，如：Job1_KDA，Job1_Match，Job2_KDA，Job2_Match，等，由徽章和名称分组。
I need a statement that that can loop through the column 3 data so I don't need to hardcode (repeat the query for each possible Jobvalue) or use a temp table.
我需要一个可以循环遍历第 3 列数据的语句，因此我不需要硬编码（对每个可能的Job值重复查询）或使用临时表。
采纳答案by Roman Pokrovskij
I would do it using dynamic sql, but this is (http://sqlfiddle.com/#!6/a63a6/1/0) the PIVOT solution:
我会使用动态 sql 来做，但这是（http://sqlfiddle.com/#!6/a63a6/1/0）PIVOT解决方案：
SELECT badge, name, [AP_KDa], [AP_Match], [ADC_KDA],[ADC_Match],[TOP_KDA],[TOP_Match] FROM
(
SELECT badge, name, col, val FROM(
SELECT *, Job+'_KDA' as Col, KDA as Val FROM @T
UNION
SELECT *, Job+'_Match' as Col,Match as Val FROM @T
) t
) tt
PIVOT ( max(val) for Col in ([AP_KDa], [AP_Match], [ADC_KDA],[ADC_Match],[TOP_KDA],[TOP_Match]) ) AS pvt
Bonus: This how PIVOT could be combined with dynamic SQL (http://sqlfiddle.com/#!6/a63a6/7/0), again I would prefer to do it simpler, without PIVOT, but this is just good exercising for me :
奖励：这是 PIVOT 可以与动态 SQL ( http://sqlfiddle.com/#!6/a63a6/7/0) 结合的方式，同样我更愿意在没有 PIVOT 的情况下做得更简单，但这只是很好的锻炼我 ：
SELECT badge, name, cast(Job+'_KDA' as nvarchar(128)) as Col, KDA as Val INTO #Temp1 FROM Temp
INSERT INTO #Temp1 SELECT badge, name, Job+'_Match' as Col, Match as Val FROM Temp
DECLARE @columns nvarchar(max)
SELECT @columns = COALESCE(@columns + ', ', '') + Col FROM #Temp1 GROUP BY Col
DECLARE @sql nvarchar(max) = 'SELECT badge, name, '+@columns+' FROM #Temp1 PIVOT ( max(val) for Col in ('+@columns+') ) AS pvt'
exec (@sql)
DROP TABLE #Temp1
回答by galgil
Combine multiple rows and columns in a row and group by ID
将多行多列组合成一行并按 ID 分组
IF OBJECT_ID('usr_CUSTOMER') IS NOT NULL
DROP TABLE usr_CUSTOMER
--------------------------CRATE TABLE---------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[usr_CUSTOMER](
[Last_Name] [nvarchar](50) NULL,
[First_Name] [nvarchar](50) NULL,
[Middle_Name] [nvarchar](50) NOT NULL,
[ID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'gal', N'ornon', N'gili', 111)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'porat', N'Yahel', N'LILl', 44444)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'Shabtai', N'Or', N'Orya', 2222)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'alex', N'levi', N'dolev', 33)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'oren', N'cohen', N'ornini', 44444)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'ron', N'ziyon', N'amir', 2222)
GO
----------------------------script---------------------------------------------
IF OBJECT_ID('tempdb..#TempString') IS NOT NULL
DROP TABLE #TempString
IF OBJECT_ID('tempdb..#tempcount') IS NOT NULL
DROP TABLE #tempcount
IF OBJECT_ID('tempdb..#tempcmbnition') IS NOT NULL
DROP TABLE #tempcmbnition
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SELECT ID,
[Last_Name] + '#' + [First_Name] + '#' + ISNULL([Middle_Name], '') as StringRow
INTO #TempString
FROM [dbo].[usr_CUSTOMER]
ORDER BY StringRow
select distinct id
into #tempcount
from usr_CUSTOMER
CREATE TABLE [dbo].[#tempcmbnition](
[ID] [int] NULL,
[combinedString] [nvarchar](max) NULL
)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
DECLARE @tableID table(ID int)
insert into @tableID(ID) (select distinct Id from #tempcount)
DECLARE @CNT int
SET @CNT = (select count(*) from @tableID)
declare @lastRow int
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
WHILE (@CNT >=1 )
BEGIN
SET @lastRow = (SELECT TOP 1 id FROM #tempcount ORDER BY id DESC)
DECLARE @combinedString VARCHAR(MAX)
set @combinedString = ''
SELECT @combinedString = COALESCE(@combinedString + '^ ', '') + StringRow
from #TempString
where ID = @lastRow
insert into #tempcmbnition (ID, [combinedString]) values(@lastRow ,@combinedString)
SET @CNT = @CNT-1
DELETE #tempcount where ID = @lastRow
END
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- if you what remove first char
-- UPDATE #tempcmbnition
-- SET combinedString = RIGHT(combinedString, LEN(combinedString) - 1)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
select *from #TempString
select * from #tempcmbnition
