SQL Map column values ​​to other columns

advertisements

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