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 JOIN
s 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:
user_id | user_name | extra_data |
---|---|---|
U1 | John | ["IS_EMPLOYED","HAS_OUTSTANDING_PAYCHECK","LIKES_CATS"] |
Related Topics
When to Use Grouping Sets, Cube and Rollup
Join One Row to Multiple Rows in Another Table
Postgresql Visual Interface Similar to PHPmyadmin
Selecting Distinct Combinations
Difference Between Inner Join and Left Semi Join
How to Execute Table Valued Function
Postgresql Constraint - Only One Row Can Have Flag Set
Select Column, If Blank Select from Another
SQL Server Output Parameter Issue
Trying to Flatten Rows into Columns
Finding All Children in a Hierarchy SQL
How to Select the Set of Rows Where Each Item Has the Greatest Timestamp
SQL - Select Distinct Only on One Column
Rollback Event Triggers in Postgresql
Return Boolean Value on SQL Select Statement
What's the Right Way to Compare an Ntext Column with a Constant Value
Delete Oldest Records from Database
Creating a New Database and New Connection in Oracle SQL Developer