Combine Multiple Rows into Multiple Columns Dynamically in SQL Server

Combine multiple rows into multiple columns dynamically in SQL Server

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

SQL Combine Multiple rows into Multiple Columns

try below solution :

DECLARE @Columns VARCHAR(max)
;WITH cte
AS (SELECT Min(Filter) minimum,
Max(Filter) maximum
FROM yourtable)
SELECT @Columns = Stuff((SELECT ',' + '[ ' + CONVERT(VARCHAR(30), number, 121) + ']'
FROM master..spt_values N
WHERE EXISTS (SELECT 1
FROM cte
WHERE n.number BETWEEN cte.minimum AND cte.maximum)
AND type = 'P'
FOR XML PATH('')), 1, 1, '')

DECLARE @sql NVARCHAR(max)= 'select [ID],[ParentItem],[ItemTitle],' + @Columns +
' from (select [ID],[ParentItem],[ItemTitle],[Filter],[FilterValue] from yourtable) t
pivot
(MAx(FilterValue)
FOR Filter in(' + @Columns + ')
)as pvt'

EXEC sp_executesql
@sql

sqlfiddle

Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic

Here is a dynamic way

Slightly different approach from your static query. To make it dynamic, Use while loop or CURSOR to generate the Task1 + Task2 + ..TaskN. Then use it in Select query.

DECLARE @columns VARCHAR(50)='Task1,Task2,Task3', -- Pass the list of column names 
@int INT = 1,
@sql VARCHAR(8000)

SET @sql = ' ;WITH cte
AS (SELECT *,
''Cycle-'' + Cast(CycleNum AS VARCHAR(10)) + ''::'' '

WHILE @int <= Len(@columns) - Len(Replace(@columns, ',', '')) -- To find the number of Tasks in list
BEGIN
SET @sql += + '+''Task' + Cast(@int AS VARCHAR(10))
+ '~''+' + 'Cast(Task'
+ Cast(@int AS VARCHAR(10))
+ ' AS VARCHAR(50)) + '','''
SET @int += 1
END

SET @sql += ' AS concat_dates
FROM #tempTable)
SELECT DISTINCT FacilityName,
LEFT(CycleData, Len(CycleData) - 1)
FROM cte a
CROSS apply(SELECT b.concat_dates + '',''
FROM cte b
WHERE a.FacilityName = b.FacilityName
FOR xml path('''')) cs (CycleData)

'
--print @sql -- uncomment it to debug if you have any error when executing dynamic code
EXEC (@sql)

Not to worry about the usage of While Loop/CURSOR since we are not doing any resource intensive operations inside the loop.

Static Query will looking like this

;WITH cte
AS (SELECT *,
'Cycle-' + Cast(CycleNum AS VARCHAR(10))
+ '::' + 'Task1~' + Cast(Task1 AS VARCHAR(50))
+ ',' + 'Task2~' + Cast(Task2 AS VARCHAR(50))
+ ',' AS concat_dates
FROM #tempTable)
SELECT DISTINCT FacilityName,
LEFT(CycleData, Len(CycleData) - 1)
FROM cte a
CROSS apply(SELECT b.concat_dates + ','
FROM cte b
WHERE a.FacilityName = b.FacilityName
FOR xml path('')) cs (CycleData)

Group by column and multiple Rows into One Row multiple columns

As I mention in the comments, what you need here is a PIVOT or Cross tab; I prefer the latter so what I am going to use.

The non-dynamic solution to this would be as follows:

WITH RNs AS(
SELECT WorkOrder,
TestType,
Result,
ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column
FROM dbo.Result)
SELECT WorkOrder,
TestType,
MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
TestType;

The problem, however, is that this "locks" you into 3 results, but you suggest there is an indeterminate number of results. Therefore you need a dynamic solution.

The below will work up to 100 results. if you do need more columns than than, then add more CROSS JOINs to N in the CTE Tally. This results is something like this (which is quite messy).

DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10),
@MaxTally int;

SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
FROM dbo.Result
GROUP BY WorkOrder,
TestType) R;

WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2) --100 rows, add more Ns for more rows
SELECT @SQL = N'WITH RNs AS(' + @CRLF +
N' SELECT WorkOrder,' + @CRLF +
N' TestType,' + @CRLF +
N' Result,' + @CRLF +
N' ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column' + @CRLF +
N' FROM dbo.Result)' + @CRLF +
N'SELECT WorkOrder,' + @CRLF +
N' TestType,' + @CRLF +
--Using FOR XML PATH due to not knowing SQL Server version
STUFF((SELECT N',' + @CRLF +
CONCAT(N' MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I)
FROM Tally T
ORDER BY T.I ASC
FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @CRLF +
N'FROM RNs R' + @CRLF +
N'GROUP BY WorkOrder,' + @CRLF +
N' TestType;';

PRINT @SQL; --Your best friend.

EXEC sys.sp_executesql @SQL;

JOIN two tables with multiple rows to a single row, with dynamic columns

SELECT *
FROM user
LEFT JOIN (SELECT user_ref_id, array_to_json(array_agg(extradata.DYNAMIC_EXTRA_FIELD)) AS extra_data
FROM extradata
GROUP BY user_ref_id) extra on extra.user_ref_id = user.user_id

Will produce:



Leave a reply



Submit