SQL Query Solution

Question

Need Help in how to write a sql query to get output as follows
Table

DP SICS_No UY LOB Rate
TR 0003468A 2006 20 30
TR 0003468A 2006 14 27
TR 0003468A 2006 93 17
TR 0003468A 2006 A2 9
TR 0003468A 2006 52 17

Output
DP SICS_NO UY LOB RATE LOB RATE LOB RATE LOB RATE
TR 0003468A 2006 20 30 14 27 93 17 A2 9

Please let me know on how to proceed. I am trying with pivot but it is not working

This was what i was trying

SELECT *
FROM (
SELECT DP, SICS_No, UY, LOB, Rate
FROM #temp
) AS source_table
PIVOT (
MAX(Rate)
FOR LOB IN ([20], [14], [93], [A2])
) AS pivot_table;

Amit Mohanty · Answer

Check this: CREATE TABLE #temp (DP VARCHAR(2), SICS_No VARCHAR(8), UY INT, LOB VARCHAR(2), Rate INT); INSERT INTO #temp (DP, SICS_No, UY, LOB, Rate) VALUES ('TR', '0003468A', 2006, '20', 30), ('TR', '0003468A', 2006, '14', 27),('TR', '0003468A', 2006, '93', 17), ('TR', '0003468A', 2006, 'A2', 9),('TR', '0003468A', 2006, '52', 17); DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SELECT @cols = COALESCE(@cols + ',', '') + QUOTENAME(LOB) FROM (SELECT DISTINCT LOB FROM #temp) AS LOBList; SET @query = 'SELECT DP, SICS_No, UY, ' + @cols + ' FROM (SELECT DP, SICS_No, UY, LOB, Rate FROM #temp) AS SourceTable PIVOT (MAX(Rate) FOR LOB IN (' + @cols + ')) AS PivotTable'; EXEC sp_executesql @query; DROP TABLE #temp;

Lokendra Singh · Answer

WITH NumberedRows AS ( SELECT DP, SICS_No, UY, LOB, Rate, ROW_NUMBER() OVER (PARTITION BY DP, SICS_No, UY ORDER BY LOB) AS RowNum FROM temp ) SELECT DP, SICS_No, UY, MAX(CASE WHEN RowNum = 1 THEN LOB END) AS LOB, MAX(CASE WHEN RowNum = 1 THEN Rate END) AS RATE, MAX(CASE WHEN RowNum = 2 THEN LOB END) AS LOB, MAX(CASE WHEN RowNum = 2 THEN Rate END) AS RATE, MAX(CASE WHEN RowNum = 3 THEN LOB END) AS LOB, MAX(CASE WHEN RowNum = 3 THEN Rate END) AS RATE, MAX(CASE WHEN RowNum = 4 THEN LOB END) AS LOB, MAX(CASE WHEN RowNum = 4 THEN Rate END) AS RATE, MAX(CASE WHEN RowNum = 5 THEN LOB END) AS LOB, MAX(CASE WHEN RowNum = 5 THEN Rate END) AS RATE FROM NumberedRows GROUP BY DP, SICS_No, UY; Output: DP SICS_No UY LOB RATE LOB RATE LOB RATE LOB RATE LOB RATE -- ---------- ----------- --- -------- --- ----------- --- ----------- --- ----------- --- --------- TR 0003468A 2006 14 27 20 30 52 17 93 17 A2 9 The NumberedRows CTE assigns a unique row number to each combination of DP, SICS_No, and UY, ordered by LOB. This is done using the ROW_NUMBER() window function. This row number (RowNum) helps in transposing the rows into columns. The main query uses MAX() combined with CASE statements to pivot the rows into columns. MAX() is used to collapse multiple rows into a single row per group.