I have the below requirement.
CREATE TABLE #TempData
(
Columnname1 NVARCHAR(50),
Value1 NVARCHAR(50),
Columnname2 NVARCHAR(50),
Value2 NVARCHAR(50),
Columnname3 NVARCHAR(50),
Value3 NVARCHAR(50)
);
Insert sample data into the temporary table
INSERT INTO #TempData (Columnname1, Value1, Columnname2, Value2, Columnname3, Value3)
VALUES
('CustomerID', '1', 'CustomerName', 'A', 'CustomerCity', 'Hyd'),
('CustomerID', '2', 'CustomerName', 'B', 'CustomerCity', 'Mum'),
('CustomerID', '3', 'CustomerName', 'C', 'CustomerCity', 'Pune'),
('CustomerID', '4', 'CustomerName', 'D', 'CustomerCity', 'Nagpur'),
('CustomerID', '5', 'CustomerName', 'E', 'CustomerCity', 'Delhi'),
('CustomerID', '6', 'CustomerName', 'F', 'CustomerCity', 'Noida'),
('CustomerID', '1', 'CustomerDOB', 'July', NULL, NULL),
('CustomerID', '2', 'CustomerDOB', 'August', NULL, NULL),
('CustomerID', '3', 'CustomerDOB', 'September', NULL, NULL),
('CustomerID', '4', 'CustomerDOB', 'October', NULL, NULL),
('CustomerID', '5', 'CustomerDOB', 'November', NULL, NULL),
('CustomerID', '6', 'CustomerDOB', 'December', NULL, NULL);`
Above is just a sample. Apart from the columnname1, rest of the columns(Column name2,3 etc) can have multiple columns as shown in the columnname2.
So I need to make sure that the result can look as below.
Result set:
CustomerID CustomerName CustomerCity CustomerDOB
--------------------------------------------------------
1 A Hyd July
2 B Mum August
3 C Pune September
4 D Nagpur October
5 E Delhi November
6 F Noida December
All this need to be dynamic as the column names are not static. It may vary with each and every requirement. Can you please provide me with the dynamic SQL in SQL Server 2014 only to make this work?
I tried the below. But I'm unable to make it work at once for an entire #temp table:
DECLARE @DynamicPivotQuery1 AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery2 AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery3 AS NVARCHAR(MAX)
DECLARE @ColumnName1 AS NVARCHAR(MAX)
DECLARE @ColumnName2 AS NVARCHAR(MAX)
DECLARE @ColumnName3 AS NVARCHAR(MAX)
-- Get distinct values of the PIVOT Column
SELECT @ColumnName1 = ISNULL(@ColumnName1 + ',','') + QUOTENAME(Columnname1)
FROM (SELECT DISTINCT Columnname1 FROM #TempData) AS Columnnames
SELECT @ColumnName2 = ISNULL(@ColumnName2 + ',','') + QUOTENAME(Columnname2)
FROM (SELECT DISTINCT Columnname2 FROM #TempData) AS Columnnames
SELECT @ColumnName3 = ISNULL(@ColumnName3 + ',','') + QUOTENAME(Columnname3)
FROM (SELECT DISTINCT Columnname3 FROM #TempData) AS Columnnames
-- Create the dynamic PIVOT query
SET @DynamicPivotQuery1 =
N'SELECT DISTINCT ' + @ColumnName1 + '
FROM
(
SELECT Columnname1, Value1, Columnname2, Value2,Columnname3, Value3
FROM #TempData
) AS SourceTable
PIVOT(MAX(Value1)
FOR Columnname1 IN (' + @ColumnName1 + ')) AS PivotedTable'
PRINT @DynamicPivotQuery1
EXEC sp_executesql @DynamicPivotQuery1
SET @DynamicPivotQuery2 =
N'SELECT ' + @ColumnName2 + '
FROM
(
SELECT Columnname1, Value1, Columnname2, Value2,Columnname3, Value3
FROM #TempData
) AS SourceTable
PIVOT(MAX(Value2)
FOR Columnname2 IN (' + @ColumnName2 + ')) AS PivotedTable'
-- Execute the dynamic query
PRINT @DynamicPivotQuery2
EXEC sp_executesql @DynamicPivotQuery2
SET @DynamicPivotQuery3 =
N'SELECT ' + @ColumnName3 + '
FROM
(
SELECT Columnname1, Value1, Columnname2, Value2,Columnname3, Value3
FROM #TempData
WHERE Columnname3 IS NOT NULL
) AS SourceTable
PIVOT(MAX(Value3)
FOR Columnname3 IN (' + @ColumnName3 + ')) AS PivotedTable'
--MAX(CASE WHEN ColumnName1 = 'CustomerID' THEN Value1 END) as CustomerID
-- Execute the dynamic query
PRINT @DynamicPivotQuery3
EXEC sp_executesql @DynamicPivotQuery3
When you have to PIVOT more than one column, I find it much easier to conceptualize using conditional aggregation. If we think backward from a conditional aggregate query that will generate the results you need, given that ColumnName1 is fixed and that Value1 will always hold the CustomerID:
SELECT CustomerID = Value1,
CustomerName = MAX(CASE WHEN ColumnName2 = 'CustomerName' THEN Value2 END),
CustomerCity = MAX(CASE WHEN ColumnName3 = 'CustomerCity' THEN Value3 END),
CustomerDOB = MAX(CASE WHEN ColumnName2 = 'CustomerDOB' THEN Value2 END)
FROM #TempData
GROUP BY Value1;
Since you are on an ancient and unsupported version without the benefit of STRING_AGG(), we can get there from dynamic SQL using FOR XML PATH:
DECLARE @cases nvarchar(max),
@sql nvarchar(max);
WITH x AS
(
-- one way to UNPIVOT first is to use UNION ALL
SELECT s = N'ColumnName2', c = ColumnName2, v = N'Value2'
FROM #TempData GROUP BY ColumnName2
UNION ALL
SELECT s = N'ColumnName3', c = ColumnName3, v = N'Value3'
FROM #TempData GROUP BY ColumnName3
)
SELECT @cases = (SELECT CONCAT(',', char(13), char(10),
QUOTENAME(c), N' = MAX(CASE WHEN ', s,
N' = ', QUOTENAME(c, char(39)), N' THEN ', v, N' END)')
FROM x WHERE c IS NOT NULL
FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)');
SELECT @sql = CONCAT(N'SELECT CustomerID = Value1',
@cases, N'
FROM #TempData GROUP BY Value1;');
PRINT @sql;
EXEC sys.sp_executesql @sql;