I have a table which millions of records like this:

enter image description here

The desired format of the data is this:

enter image description here

how do i achieve this? tried a cross join but didnt help. pivot is taking too long to execute as the dataset is huge.

  • Which database system and version are you using?
    – J.D.
    Sep 1 at 11:56
  • Im using snowflake. Btw I tried the query with case, but it’s pulling up only null values for the amt field.
    – Dipanjana
    Sep 2 at 6:32

1 Answer 1


I think you don't need any cross join.

in SQL, you can write statement like this

select JD_WS_CD || '_' || JD_WS_SUB_CD || '_' || JD_SHP_TO, 
case when YM='202301' then AMT1 end as JAN_AMT1,
case when YM='202302' then AMT1 end as FEB_AMT1,
case when YM='202303' then AMT1 end as MAR_AMT1
FROM table

in excel,

First Column =A2 & "_" & B2& "_" & C2
JAN_AMT1 Column =IF(D2=202301;E2;"")
FEB_AMT1 Column =IF(D2=202302;E2;"")
MAR_AMT1 Column =IF(D2=202303;E2;"")
  • Yes, but like I said the dataset is huge. I’ve shown only a tiny portion of the data for illustration purposes. Using cases for each of them will not be possible.
    – Dipanjana
    Sep 1 at 0:44
  • This is returning nulls for the amt columns btw
    – Dipanjana
    Sep 2 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.