在SQL Server中动态将多行合并为多列 [英] Combine multiple rows into multiple columns dynamically in SQL Server

问题描述

我有一个很大的数据库表，我需要使用Microsoft SQL Server在其上动态执行以下操作.

I have a large database table on which I need to perform the action below dynamically using 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来获得类似结果:

To a result like this using 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列的单行.

Even if there are 30 rows, it also will combine into a single row with 60 columns.

我目前可以通过硬编码(请参见下面的示例)来做到这一点，但不能动态进行.

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

我需要一个MSSQL语句，该语句允许我将多行合并为一行.第3列(Job)内容将与第4列和第5列标题(KDA和Match)组合在一起，并成为新列.

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 (KDA and Match) and become a new column.

因此，如果Job有6个不同的值(例如Job1至Job6)，则结果将有12列，例如:Job1_KDA，Job1_Match，Job2_KDA，等，按徽章和名称分组.

So, if there are 6 distinct values for Job (say Job1 through Job6), then the result will have 12 columns, e.g.: Job1_KDA, Job1_Match, Job2_KDA, Job2_Match, etc., grouped by badge and name.

我需要一条语句，该语句可以遍历第3列数据，因此不需要硬编码(对每个可能的Job值重复查询)或使用临时表.

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 Job value) or use a temp table.

推荐答案

我会使用动态sql做到这一点，但这是(

I would do it using dynamic sql, but this is (http://sqlfiddle.com/#!6/a63a6/1/0) the PIVOT solution:

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，但这对我来说是一个很好的练习:

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 :

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

这篇关于在SQL Server中动态将多行合并为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！