Tricky 'unpivot' data question

Question

Tricky 'unpivot' data question

Naomi 6,571

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.

Yitzhak Khabinsky 20,646 Reputation points

2022-02-08T20:29:25.987+00:00

@Naomi ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Naomi 6,571 Reputation points

2022-02-08T20:46:39.703+00:00

I'll answer just the last question - the SQL version on production server is SQL 2016. I don't have sample data, unfortunately.
Naomi 6,571 Reputation points

2022-02-08T21:46:12.703+00:00

BTW, completely unrelated question - how can I find out who is
M.O..R*&* user? He looks like someone I may know, but I may be just mistaken.
Yitzhak Khabinsky 20,646 Reputation points

2022-02-09T14:08:10.623+00:00

@Naomi ,

Nobody will be able to help you without all of them: ##1-4.
Naomi 6,571 Reputation points

2022-02-09T21:40:58.183+00:00

I re-wrote the query today already. Decided to select into temp table first, then use CROSS APPLY technique from it. In my tests it took longer to generate result than the original UNPIVOT version, but I think my version may be better from the readability point of the view, so I'm keeping it.

Yitzhak Khabinsky 20,646 Reputation points

2022-02-08T20:29:25.987+00:00

@Naomi ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Naomi 6,571 Reputation points

2022-02-08T20:46:39.703+00:00

I'll answer just the last question - the SQL version on production server is SQL 2016. I don't have sample data, unfortunately.
Naomi 6,571 Reputation points

2022-02-08T21:46:12.703+00:00

BTW, completely unrelated question - how can I find out who is
M.O..R*&* user? He looks like someone I may know, but I may be just mistaken.
Yitzhak Khabinsky 20,646 Reputation points

2022-02-09T14:08:10.623+00:00

@Naomi ,

Nobody will be able to help you without all of them: ##1-4.
Naomi 6,571 Reputation points

2022-02-09T21:40:58.183+00:00

I re-wrote the query today already. Decided to select into temp table first, then use CROSS APPLY technique from it. In my tests it took longer to generate result than the original UNPIVOT version, but I think my version may be better from the readability point of the view, so I'm keeping it.

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.

Naomi 6,571 Reputation points

2022-02-08T21:04:57.967+00:00

What is also not clear as why we even need LEFT JOIN with the sub table if we don't seem to be using any columns from it.
Erland Sommarskog 72,416 Reputation points MVP

2022-02-08T22:51:22.287+00:00

What is also not clear as why we even need LEFT JOIN with the sub table if we don't seem to be using any columns from it.

Well, if the join can hit more than one row in sub, the left join can serve to increase the number of rows...

I'm afraid that I'm passing on your main question. What I can say is that I generally do unpivoting with CROSS APPLY, and I never use the UNPIVOT operator. Here is a simple example:

SELECT d.name, v.flag, v.value FROM sys.databases d CROSS APPLY (VALUES('read_commmited_snapshot_on', d.is_read_committed_snapshot_on), ('allow_snapshot_isolation', d.snapshot_isolation_state), ('trustworthy', d.is_trustworthy_on)) AS v(flag, value)
Naomi 6,571 Reputation points

2022-02-08T23:02:06.603+00:00

Hi Erland,

I understand the CROSS APPLY, but in my case I'm using a LEFT JOIN so it seems to call for OUTER APPLY?

Unless we first want to get all the data into CTE and go into CROSS APPLY to unpivot from there. I was lazy to re-write it this way...

For now I just removed the sub table JOIN (I think it could have been cause for dups), removed the UDF which seemed to be unneeded, optimized the where clause a bit and sent the generated output for inspection. I'll take a look later if I would be able to re-write it.
Erland Sommarskog 72,416 Reputation points MVP

2022-02-08T23:04:57.443+00:00

I understand the CROSS APPLY, but in my case I'm using a LEFT JOIN so it seems to call for OUTER APPLY?

Maybe. it is left as an exercise to the reader. :-) As I said, it was a quick example.
Naomi 6,571 Reputation points

2022-02-09T00:05:29.807+00:00

Tried re-writing and it seems to be taken longer to execute. It also returned the wrong results. I think I'll keep the UNPIVOT version.

Tricky 'unpivot' data question

1 answer

Activity