Combine multiple rows into multiple columns dynamically in SQL Server
multiple-columns, rows, sql, sql-server
Solution
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
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
Problem
I have a large database table on which I need to perform the action below dynamically using 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: ``` 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. 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 (`KDA` and `Match`) and become a new column. 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. 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.