CODEWITHSUNDEEP
sqlsql-serverpivotunpivotcross-apply
chrissy p
chrissy p

Reputation: 823

SQL - create attribute output for unpivoted data

I have a dataset which looks like the following:

Position_Date   Portfolio   Country   Weight   BM Weight
2017-09-30      Port1       Mexico    0.2      0.0
2017-09-30      Port1       Mexico    0.1      0.1
2017-09-30      Port1       USA       0.2      0.2
2017-09-30      Port1       USA       0.3      0.1

I would like to transform this stored data set into the following output using a SQL query:

Portfolio_Date  Portfolio  Dimension        Dimension_Value  Measure    Measure_Value
2017-09-30      Port1      Country          Mexico           Weight     0.3
2017-09-30      Port1      Country          Mexico           BM Weight  0.1
2017-09-30      Port1      Country          USA              Weight     0.5
2017-09-30      Port1      Country          USA              BM Weight  0.3
2017-09-30      Port1      Portfolio        Country          Weight     0.8
2017-09-30      Port1      Portfolio        Country          BM Weight  0.4

I was wondering what would be the efficient why to create the dataset? Would I have to PIVOT the data to then UNPIVOT it to create my final dataset? Or is there another way using CROSS APPLY with a GROUP BY that I could utilise which I have seen in other posts on this forum?

Thanks

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This question is much trickier than I first thought. I would do the unpivot after doing the aggregation:

select t.Portfolio_Date, t.Portfolio,
       v.*
from (select t.Portfolio_Date, t.Portfolio,
             coalesce(country, 'Country') as dimension_value,  -- coalesce is a shortcut for getting the aggregated row
             coalesce(country, 'Portfolio') as dimension,
             sum(weight) as weight, sum(bm_weight) as bm_weight
      from t
      group by grouping sets ( (t.Portfolio_Date, t.Portfolio, country), (t.Portfolio_Date, t.Portfolio) )
     ) t outer apply
     (values (dimension, dimension_value, 'Weight', weight),
             (dimension, dimension_value, 'BM Weight', bm_weight)
     ) v(dimension, dimension_value, measure, measure_value);

Upvotes: 1

Related Questions