SQL Query Solution

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;

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.