Please help on Pivot/UN Pivot

Transact-SQL
1

CREATE TABLE #TEMP_STG_MERGE_TEMP
(
[ROW_SEQ] INT IDENTITY
,[POLICY_NO] CHAR(30) NULL
,[INSUR_TYPE] CHAR(2) NULL
,[EFF_DT] CHAR(10) NULL
,[TERM_DT] CHAR(10) NULL

)

INSERT INTO #TEMP_STG_MERGE_TEMP(POLICY_NO,INSUR_TYPE,EFF_DT,TERM_DT)
select 'ABC001','01','01/01/2022','12/31/2022' union all
select 'ABC001','02','02/01/2022','12/31/2022' union all
select 'ABC001','03','03/01/2022','12/31/2022' union all
select 'ABC001','04','03/01/2022','12/31/2022' union all
select 'ABC002','05','01/01/2022','12/31/2022' union all
select 'ABC002','06','02/01/2022','12/31/2022'
--drop table #TEMP_STG_MERGE_TEMP

--select * from #TEMP_STG_MERGE_TEMP

I am looking for a result in sucha a way that for a particular POLICY_NO data INSUR_TYPE,EFF_DT & TERM_DT should come as columns.There can be upto 13 INSUR_TYPE for a POLICY_NO

The results shouls be like below

sql
sql1045×127 50.2 KB

Thanks in advance.

2

one idea

.. self join ..

join 
     tableabc alias1  
join 
    tableabc alias2 
    on 
         alias1.POLICY_NO  = alias2.POLICY_NO
             AND 
         alias1.INSUR_TYPE + 1 = alias2.INSUR_TYPE
3

this is the idea

image

4 
;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY POLICY_NO 
        ORDER BY CAST(EFF_DT AS date)) AS row_num
    FROM #TEMP_STG_MERGE_TEMP
)
SELECT 
    POLICY_NO,
    MAX(CASE WHEN row_num = 1 THEN INSUR_TYPE END) AS INSUR_TYPE1,
    MAX(CASE WHEN row_num = 1 THEN EFF_DT END) AS EFF_DT1,
    MAX(CASE WHEN row_num = 1 THEN TERM_DT END) AS TERM_DT1,
    MAX(CASE WHEN row_num = 2 THEN INSUR_TYPE END) AS INSUR_TYPE1,
    MAX(CASE WHEN row_num = 2 THEN EFF_DT END) AS EFF_DT2,
    MAX(CASE WHEN row_num = 2 THEN TERM_DT END) AS TERM_DT2,
    MAX(CASE WHEN row_num = 3 THEN INSUR_TYPE END) AS INSUR_TYPE2,
    MAX(CASE WHEN row_num = 3 THEN EFF_DT END) AS EFF_DT3,
    MAX(CASE WHEN row_num = 3 THEN TERM_DT END) AS TERM_DT3,
    MAX(CASE WHEN row_num = 4 THEN INSUR_TYPE END) AS INSUR_TYPE4,
    MAX(CASE WHEN row_num = 4 THEN EFF_DT END) AS EFF_DT4,
    MAX(CASE WHEN row_num = 4 THEN TERM_DT END) AS TERM_DT4,
    MAX(CASE WHEN row_num = 5 THEN INSUR_TYPE END) AS INSUR_TYPE5,
    MAX(CASE WHEN row_num = 5 THEN EFF_DT END) AS EFF_DT5,
    MAX(CASE WHEN row_num = 5 THEN TERM_DT END) AS TERM_DT5,
    MAX(CASE WHEN row_num = 6 THEN INSUR_TYPE END) AS INSUR_TYPE6,
    MAX(CASE WHEN row_num = 6 THEN EFF_DT END) AS EFF_DT6,
    MAX(CASE WHEN row_num = 6 THEN TERM_DT END) AS TERM_DT6,
    MAX(CASE WHEN row_num = 7 THEN INSUR_TYPE END) AS INSUR_TYPE7,
    MAX(CASE WHEN row_num = 7 THEN EFF_DT END) AS EFF_DT7,
    MAX(CASE WHEN row_num = 7 THEN TERM_DT END) AS TERM_DT7,
    MAX(CASE WHEN row_num = 8 THEN INSUR_TYPE END) AS INSUR_TYPE8,
    MAX(CASE WHEN row_num = 8 THEN EFF_DT END) AS EFF_DT8,
    MAX(CASE WHEN row_num = 8 THEN TERM_DT END) AS TERM_DT8,
    MAX(CASE WHEN row_num = 9 THEN INSUR_TYPE END) AS INSUR_TYPE9,
    MAX(CASE WHEN row_num = 9 THEN EFF_DT END) AS EFF_DT9,
    MAX(CASE WHEN row_num = 9 THEN TERM_DT END) AS TERM_DT9,
    MAX(CASE WHEN row_num =10 THEN INSUR_TYPE END) AS INSUR_TYPE10,
    MAX(CASE WHEN row_num =10 THEN EFF_DT END) AS EFF_DT10,
    MAX(CASE WHEN row_num =10 THEN TERM_DT END) AS TERM_DT10,
    MAX(CASE WHEN row_num =11 THEN INSUR_TYPE END) AS INSUR_TYPE11,
    MAX(CASE WHEN row_num =11 THEN EFF_DT END) AS EFF_DT11,
    MAX(CASE WHEN row_num =11 THEN TERM_DT END) AS TERM_DT11,
    MAX(CASE WHEN row_num =12 THEN INSUR_TYPE END) AS INSUR_TYPE12,
    MAX(CASE WHEN row_num =12 THEN EFF_DT END) AS EFF_DT12,
    MAX(CASE WHEN row_num =12 THEN TERM_DT END) AS TERM_DT12,
    MAX(CASE WHEN row_num =13 THEN INSUR_TYPE END) AS INSUR_TYPE13,
    MAX(CASE WHEN row_num =13 THEN EFF_DT END) AS EFF_DT13,
    MAX(CASE WHEN row_num =13 THEN TERM_DT END) AS TERM_DT13
FROM cte_add_row_num
GROUP BY POLICY_NO
ORDER BY POLICY_NO
5

Hi Scott,

Thak you so much for your help.I got the result as expected.

Regards,
Binto

6

You're welcome!

Btw, that technique is known as a "crosstab" if you want to look up further info on it.

7

Sure. I will look into this. Thanks again