Tricky 'unpivot' data question

Naomi 7,361 Reputation points
2022-02-08T20:18:53.353+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,663 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-02-08T20:59:59.29+00:00

    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.