在SQL Server中动态地将多行合并为多列

Question

我需要使用 Microsoft SQL Server 动态地对一个大型的数据库表执行下面的操作。

假设有以下结果：

 badge   |   name   |   Job   |   KDA   |   Match 
 - - - - - - - - - - - - - - - -
 T996    |  Darrien |   AP    |   3.0   |   20
 T996    |  Darrien |   ADC   |   2.8   |   16
 T996    |  Darrien |   TOP   |   5.0   |   120

使用 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      

即使有30行，它也会合并成一行，共有60列。

我目前能够通过硬编码来实现这一点（请参见下面的示例），但无法动态地实现。

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

我需要一条MSSQL语句，可以将多行合并为一行。第3列(Job)的内容将与第4和第5列的标头(KDA和Match)组合，并成为一个新列。

所以，如果存在6个不同的 Job 值（比如 Job1 到 Job6 ），则结果将有12列，例如： Job1_KDA ， Job1_Match ， Job2_KDA ， Job2_Match 等，按徽章和姓名分组。

我需要一条语句，可以循环遍历第3列的数据，这样我就不需要硬编码（针对每个可能的 Job 值重复查询）或使用临时表。

- chabu

---

大家好，我想问的问题是一个 MSSQL 语句，它允许我将多行合并为一行。第三列的内容将与第四、五列的标题组合成一个新列。因此，如果第三列中有6个不同的数据，则会产生12列。例如：Job1_KDA、Job1_Match、Job2_KDA、Job2_Match、Job3_KDA、Job3_Match、Job4_KDA、Job4_Match。 - chabu

这里有很多相关的问题。搜索[sql-server] pivot可以找到许多关于如何做你所问的事情的例子。例如，这个问题执行了类似的操作（尽管它将六行转换为三行）。 - Ken White

Answer 1

我会使用动态SQL来完成，但这里有一个PIVOT解决方案：http://sqlfiddle.com/#!6/a63a6/1/0

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

优势：这是如何将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

- Roman Pokrovskij

---

嗨Roman，感谢您提供解决方案。但是这并没有解决我的问题，因为我需要动态创建列名而不是硬编码它们。我该如何使PIVOT语句不是静态变量，例如AP_KDA、AP_Match，而是像Job+'_KDA'、Job+'_Match'这样的东西？ - chabu

我记得我已经回答过你了，说的是“使用动态SQL”。你需要帮忙编写动态查询吗？我写PIVOT只是因为我很少用它们，这对我来说很有趣。 - Roman Pokrovskij

看起来没问题，但是在最后一行出现了错误。 DECLARE @sql nvarchar(max) = 'SELECT badge, name, '+@columns+' FROM #Temp1 PIVOT ( max(val) for Col in ('+@columns+') ) AS pvt' 它给我这个错误。 Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 6 Must declare the scalar variable "@sql"。我搜索了错误的解决方案，所有用户都提到需要将列转换为nvarchar，所以我也这样做了。我已经将所有列转换为nvarchar，但仍然无法解决此错误。 - chabu

没事了，我自己解决了，看起来是我的内部数据有一个百分号导致出错了。现在它运行得很好，谢谢！ - chabu

Answer 2

将多行和列组合在一行中，按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

- galgil