I just cant switch some columns SQL server (simple)

Question

My query:

select (sum( c20 ))as total , año
FROM(
select  distinct  datepart(year,DATEADD(DAY,CONVERT(FLOAT(10),KDUD.C16),A1.C7))as año, (a1.c20) as c20
from

KDUE A1
LEFT JOIN KDMM ON KDMM.C1='U' AND A1.C3=KDMM.C2 AND A1.C4=KDMM.C3 AND       A1.C5=KDMM.C4
LEFT JOIN KDMS ON A1.C1=KDMS.C1
LEFT JOIN KDUD ON A1.C2=KDUD.C2
LEFT JOIN KDUV ON A1.C18=KDUV.C2
where a1.c20 != 0) as aas

group by año

this gives this table as a result:

total                                   año
--------------------------------------- -----------
409782.45                               2013
2442993.24                              2014
10810460.01                             2015

but i wish i could get the years as headers and the totals as a single row... help :c

Search for sql server pivot. A lot of examples here and on other websites. — Zohar Peled, Jul 16 '15 at 19:00
nevermind, fixed it already i was dumb and missed a typo in the group by.... sorry to bother you ill close this question — ivan, Jul 16 '15 at 19:11

score 3 · Answer 1 · answered Jul 16 '15 at 19:08

with x as 
    (select (sum( c20 ))as total , año
FROM(
select  distinct  datepart(year,DATEADD(DAY,CONVERT(FLOAT(10),KDUD.C16),A1.C7))as año, (a1.c20) as c20
from

KDUE A1
LEFT JOIN KDMM ON KDMM.C1='U' AND A1.C3=KDMM.C2 AND A1.C4=KDMM.C3 AND       A1.C5=KDMM.C4
LEFT JOIN KDMS ON A1.C1=KDMS.C1
LEFT JOIN KDUD ON A1.C2=KDUD.C2
LEFT JOIN KDUV ON A1.C18=KDUV.C2
where a1.c20 != 0) as aas

group by año)
select case when ano = '2013' then total end as '2013',
       case when ano = '2014' then total end as '2014',
       case when ano = '2015' then total end as '2015'
from x

This is one way to do it but will be more manual as you have more than a handful of values in ano column.

ah, i just fixed the problem by adding this: group by kdud.c16,a1.c7 and a simple pivot, thanks though — ivan, Jul 16 '15 at 19:13

I just cant switch some columns SQL server (simple)

1 Answers1