I have a table which millions of records like this:
The desired format of the data is this:
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.Commented Sep 1, 2023 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.– DipanjanaCommented Sep 2, 2023 at 6:32
Add a comment
|
1 Answer
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. Commented Sep 1, 2023 at 0:44
-