I've got a pivoted query which is outputting something like:
[ID][Value][DateField1][Val1][Val2][Val3] 1 R2 2014-01-01 0.3 3.2 3.1 1 R1 2014-01-02 NULL 2.2 0.5 1 R2 2014-01-02 0.7 NULL NULL
I need to further modify it such that the Value Column is turned into:
[ID][DateField1][Val1][Val2][Val3][Val1R][Val2R][Val3R] 1 2014-01-01 0.3 3.2 3.1 R2 R2 R2 1 2014-01-02 0.7 2.2 0.5 R2 R1 R1
It wouldn't be a pivot but I'm not entirely sure how to go about it. If someone could point me in the right direction, I'd really appreciate it. Thanks!
with D as ( yourquery ) select ID, DateField1, sum(Val1) as Val1, sum(Val2) as Val2, sum(Val3) as Val3, min(case when Val1 is not null then Value end) as Val1R, min(case when Val2 is not null then Value end) as Val2R, min(case when Val3 is not null then Value end) as Val3R, from D group by ID, DateField1