How to Pivot Table From Simple #TEMP Sample Data?

Question

How to Pivot Table From Simple #TEMP Sample Data?

montgomery johnson

Default port

Points: 1466
More actions
October 16, 2023 at 11:05 pm

#4309357

I know this is easy for someone. We want to create a pivot table from the sample data below.
Result columns would be:
ap_claim_idM, Code1, Code2, Code3, Code4, Code5 (using values in icd_prcdr_code column, based on order by p_claim_icd_prcdr_sequence_number)
Sample data:
SELECT 1785668 AS ap_claim_idM,1 AS ap_claim_icd_prcdr_sequence_number,'0SG30AJ' AS icd_prcdr_code
INTO #TEMP

INSERT INTO #TEMP (ap_claim_idM,ap_claim_icd_prcdr_sequence_number,icd_prcdr_code)
VALUES
(1785668, 2, '0SG3071'),
(1785668, 3, '01NB0ZZ'),
(1785668, 4, '0SB40ZZ'),
(1785668, 5, '8E0WXBG');

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18334 More actions · Answer 1

SELECT ap_claim_idM,
       MAX(CASE WHEN ap_claim_icd_prcdr_sequence_number = 1 THEN icd_prcdr_code END) AS Code1,
       MAX(CASE WHEN ap_claim_icd_prcdr_sequence_number = 2 THEN icd_prcdr_code END) AS Code2,
       MAX(CASE WHEN ap_claim_icd_prcdr_sequence_number = 3 THEN icd_prcdr_code END) AS Code3,
       MAX(CASE WHEN ap_claim_icd_prcdr_sequence_number = 4 THEN icd_prcdr_code END) AS Code4,
       MAX(CASE WHEN ap_claim_icd_prcdr_sequence_number = 5 THEN icd_prcdr_code END) AS Code5
  FROM #TEMP
 GROUP BY ap_claim_idM
;

montgomery johnson Default port Points: 1466 More actions · Answer 2

Works like a charm. I will share.

Thank you very much.

montgomery johnson Default port Points: 1466 More actions · Answer 3

The non-PIVOT solution works fine, and I should have thought of it.

Just curios though. Could the same thing be done with less code using PIVOT?

Phil Parkin SSC Guru Points: 246944 More actions · Answer 4

montgomery johnson wrote:

The non-PIVOT solution works fine, and I should have thought of it.
Just curios though. Could the same thing be done with less code using PIVOT?

If you are simply trying to shrink the above code, here is a possibility

SELECT t.ap_claim_idM,
       Code1 = MAX(IIF(x.y = 1,x.z,'0')),
       Code2 = MAX(IIF(x.y = 2,x.z,'0')),
       Code3 = MAX(IIF(x.y = 3,x.z,'0')),
       Code4 = MAX(IIF(x.y = 4,x.z,'0')),
       Code5 = MAX(IIF(x.y = 5,x.z,'0'))
  FROM #TEMP t
  CROSS APPLY (SELECT y = t.ap_claim_icd_prcdr_sequence_number, z = t.icd_prcdr_code ) x
 GROUP BY t.ap_claim_idM

But you'll find that most of the established coders here usually avoid PIVOT because

The MAX (CASE …) syntax is more intuitive and easier to remember, and
It tends to perform worse than the equivalent MAX(CASE …) version

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

montgomery johnson Default port Points: 1466 More actions · Answer 5

montgomery johnson

Default port

Points: 1466

October 17, 2023 at 11:15 pm

#4310016

That works too.

Thank you