CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
007
007

Reputation: 2186

T-SQL | Dynamic Transpose

I have the following scenario.

CREATE TABLE #Temp1  (ID INT , Summary varchar(50))
CREATE TABLE #Temp2  (ID INT , D_ID INT, Detail1 varchar(50), Detail2 varchar(50))

INSERT INTO #Temp1 (ID,Summary)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Four'

INSERT INTO #Temp2 (ID, D_ID, Detail1,Detail2)
SELECT 1,1,'One','_1_'
UNION ALL
SELECT 2,2,'Two','_2_'
UNION ALL
SELECT 2,3,NULL,'_3_'
UNION ALL
SELECT 3,4,'Three',NULL
UNION ALL
SELECT 3,5,NULL,NULL
UNION ALL
SELECT 3,6,'Three_2','_32_'


DROP TABLE #Temp1
DROP TABLE #Temp2

I am looking for following outcome.

S_ID, S_Summary, D_Detail1_1, D_Detail1_2, D_Detail2_1, D_Detail2_2, D_Detail3_1, D_Detail3_2...)
1,    First,     One,         _1_          NULL         NULL         NULL         NULL...
2,    Second,    Two,         _2_          NULL         _3_          NULL         NULL...
3,    Third,     Three,       NULL         NULL         NULL         Three_2      _32_...
4,    Four,      NULL,        NULL         NULL         NULL         NULL         NULL...

There can be one to many relationship between Summary and Detail table and can vary in amount of detail records (dynamic).

I am not sure how to go about PIVOTing this dynamically and still get the desired outcome. PIVOT requires defined set of rows to transpose it to columns if I am not mistaken. Any help would be appreciated.

Upvotes: 1

Views: 208

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

This is done using a Dynamic Crosstab. You can read it here: http://www.sqlservercentral.com/articles/Crosstab/65048/

DECLARE @sql1 VARCHAR(2000) = ''
DECLARE @sql2 VARCHAR(2000) = ''
DECLARE @sql3 VARCHAR(2000) = ''

SELECT @sql1 = 
'SELECT
    t.ID AS S_ID
    , t.Summary AS S_Summary
'

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN RN = ' + CONVERT(VARCHAR(10), RN) + ' THEN Detail1 END) AS D_Detail' + CONVERT(VARCHAR(10), RN) + '_1' + CHAR(10) +
'   , MAX(CASE WHEN RN = ' + CONVERT(VARCHAR(10), RN) + ' THEN Detail2 END) AS D_Detail' + CONVERT(VARCHAR(10), RN) + '_2' + CHAR(10)
FROM(
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY D_ID) AS RN FROM #Temp2
)t
ORDER BY RN

SELECT @sql3 = 
'FROM (
    SELECT
        t1.ID,
        t1.Summary,
        t2.D_ID,
        t2.Detail1,
        t2.Detail2,
        RN = ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY D_ID)
    FROM #Temp1 t1
    LEFT JOIN #Temp2 t2
        ON t2.ID = t1.ID
) t
GROUP BY t.ID, t.Summary
ORDER BY t.ID'

Upvotes: 1

Related Questions