Tricky 'unpivot' data question

Question

Hi everybody,

Long time no see :) I don't have sample data, so I'll try to ask this question in a generic way. I reviewed this very nice article https://social.technet.microsoft.com/wiki/contents/articles/32230.sql-server-unpivot-vs-apply.aspx but I'm not exactly sure how to apply it to more complex case I have.

I have a complex select statement involving several tables where I'm using (not my code, BTW) LEFT JOIN to join main table with another table (not to mention couple of other tables).

Anyway, main table has these two columns: principal_procedure_cd and principal_procedure_dt, the other table which is LEFT JOINed with the first one has 12 codes and 12 date columns procedure_1_cd - through procedure_12_cd and procedure_1_dt through procedure_12_dt. All these columns are nullable in addition to being LEFT JOINEd. In the final output I need to transpose the columns into rows when we have values (not blank and not NULL). My assumption is that code and date columns should be together (e.g. if I have procedure_2_cd I would need to list corresponding _dt code assuming both have values, also I assume that I may have several cd/dt columns populated in the other table and I also may assume that 1 row in the main table may correspond to multiple rows in the other table. The desired output should list all distinct values per main table). I'm struggling (in my mind so far) to actually write that statement correctly - I assume I need OUTER APPLY values but it's not clear to me yet. The current code in the procedure uses UNPIVOT for codes field syntax with 12 dates column and CASE statement to select correct date. I can see that I got duplicates in my final result right now.

Can you please suggest the correct syntax? I hope I explained the issue with enough details and sorry for not having a sample data. The actual output produces one line concatenated with ~ character between columns

It produced these two lines, for example:

F338X0285~6~P~0B110F4~20220126~20191011}
F338X0285~6~P~0B110F4~20220126~20191011}

which we can see are identical. I can obviously add distinct to the original select statement, but I'm somehow unsure it even produced correct results to begin with.

Thanks in advance.

Answer 1

This is current code (I changed table names)

select  
LTRIM(RTRIM(ISNULL(CLM_ID, '')))  
+'~'+ LTRIM(RTRIM(ISNULL(CLM_VRSN_NUM, '')))  
+'~'+  
CASE  
       WHEN codes = 'pr' THEN 'P'  
       WHEN codes LIKE 'proc_%' THEN 'S'  
       ELSE ''  
END --AS PROC_TYPE_CD,  
+'~'+ LTRIM(RTRIM(ISNULL(ICD10_PROC_CD, '')))  
+'~'+ LTRIM(RTRIM(ISNULL(convert(varchar(10), CLM_PRCSD_OR_PD_DT, 112), '')))  
+'~'+  
ISNULL(CASE  
       WHEN codes = 'pr' THEN LTRIM(RTRIM(dt0))  
       WHEN codes = 'proc_1' THEN LTRIM(RTRIM(dt1))  
       WHEN codes = 'proc_2' THEN LTRIM(RTRIM(dt2))  
       WHEN codes = 'proc_3' THEN LTRIM(RTRIM(dt3))  
       WHEN codes = 'proc_4' THEN LTRIM(RTRIM(dt4))  
       WHEN codes = 'proc_5' THEN LTRIM(RTRIM(dt5))  
       WHEN codes = 'proc_6' THEN LTRIM(RTRIM(dt6))  
       WHEN codes = 'proc_7' THEN LTRIM(RTRIM(dt7))  
       WHEN codes = 'proc_8' THEN LTRIM(RTRIM(dt8))  
       WHEN codes = 'proc_9' THEN LTRIM(RTRIM(dt9))  
       WHEN codes = 'proc_10' THEN LTRIM(RTRIM(dt10))  
       WHEN codes = 'proc_11' THEN LTRIM(RTRIM(dt11))  
       WHEN codes = 'proc_12' THEN LTRIM(RTRIM(dt12))  
       ELSE ''  
END, '') --AS PROC_DT  
+'}' AS EXTRACT  
from  
(  
select  
ISNULL(left(MI.patient_control_nbr, len(MI.patient_control_nbr)-4), '') as CLM_ID,  
ISNULL(right(MI.claim_increment_id, 4), '') as CLM_VRSN_NUM,  
ISNULL(MI.principal_procedure_cd, '') as pr,  
  
ISNULL(PO.procedure_1_cd, '') as proc_1,  
ISNULL(PO.procedure_2_cd, '') as proc_2,  
ISNULL(PO.procedure_3_cd, '') as proc_3,  
ISNULL(PO.procedure_4_cd, '') as proc_4,  
ISNULL(PO.procedure_5_cd, '') as proc_5,  
etc,  
ISNULL(PO.procedure_12_cd, '') as proc_12,  
  
  
ISNULL(convert(varchar(10), payment_dt.dt, 112), '') as CLM_PRCSD_OR_PD_DT,  
  
ISNULL(convert(varchar(10), MI.principal_procedure_dt, 112), '') as dt0,  
ISNULL(convert(varchar(10), PO.procedure_1_dt, 112), '') as dt1,  
ISNULL(convert(varchar(10), PO.procedure_2_dt, 112), '') as dt2,  
  
etc.  
ISNULL(convert(varchar(10), PO.procedure_10_dt, 112), '') as dt10,  
ISNULL(convert(varchar(10), PO.procedure_11_dt, 112), '') as dt11,  
ISNULL(convert(varchar(10), PO.procedure_12_dt, 112), '') as dt12  
  
from  
MI  
left join sub  
       ON MI.inst_claim_info_seq = sub.inst_claim_info_seq  
left join PO  
       ON MI.inst_claim_info_seq =PO.inst_claim_info_seq  
  
inner join   
       (select   
       max(adjudication_or_payment_dt) as dt,   
       inst_claim_info_seq as inst_claim_info_seq  
             from MI  
             group by inst_claim_info_seq ) as payment_dt  
              ON MI.inst_claim_info_seq = payment_dt.inst_claim_info_seq  
  
join LFD  
       ON LFD.load_file_detail_id = MI.load_file_detail_id  
where left(MI.patient_control_nbr, len(MI.patient_control_nbr)-4) + right(MI.patient_control_nbr, 4) = MI.patient_control_nbr   
         
    and LFD.source_file_type_id in (204,2204,3204)  
       and LFD.file_status_type_id  IN (400,401,450,0)  
      AND payment_dt.dt BETWEEN @start_cycle_dt --First day of previous month  
									  AND	  @end_cycle_dt --Last Day of previous month  
         
) src   
unpivot   
(  
       ICD10_PROC_CD for codes in (  
       pr,   
       proc_1, proc_2, proc_3, proc_4, proc_5, proc_6, proc_7, proc_8, proc_9, proc_10, proc_11, proc_12)  
) o  
where o.ICD10_PROC_CD <> '';  

I changed the table names but other than that the select is as is right now.