troubleshooting Question

Dynamic column name with CTE

Avatar of Khan Consultoria
Khan ConsultoriaFlag for Brazil asked on
Microsoft SQL Server* Microsoft sql 2017
7 Comments1 Solution28 ViewsLast Modified:
Hello Guys

I made a query as you can see below:

create view vendasG 
as
with Vendas as
(
   select codcad, 
             case when month(datped) = 01 then sum(totped) else 0 end as M01,
             case when month(datped) = 02 then sum(totped) else 0 end as M02,
             case when month(datped) = 03 then sum(totped) else 0 end as M03,
             case when month(datped) = 04 then sum(totped) else 0 end as M04,
             case when month(datped) = 05 then sum(totped) else 0 end as M05,
             case when month(datped) = 06 then sum(totped) else 0 end as M06,
             case when month(datped) = 07 then sum(totped) else 0 end as M07,
             case when month(datped) = 08 then sum(totped) else 0 end as M08,
             case when month(datped) = 09 then sum(totped) else 0 end as M09,
             case when month(datped) = 10 then sum(totped) else 0 end as M10,
             case when month(datped) = 11 then sum(totped) else 0 end as M11,
             case when month(datped) = 12 then sum(totped) else 0 end as M12
         from arfat a
         where a.datped between '20200101' and '20201231' 
           and a.flagcan = 0    
               and a.FLAGENF = 1
               group by codcad, datped
)
,
vendaT as
(select codcad, sum(M01) as jan, sum(M02) as Fev, Sum(M03) as Mar, Sum(M04) as Abr, Sum(M05) as Maio, Sum(M06) as Jun
 from vendas
   group by codcad
)
select a.*, b.nomcad from vendaT a
inner join arcad b on (b.codcad=a.codcad)



Today I have the month fix creating for each month a Mxx - As you can see, I have a two dates '20200101' and '20201231' that is one year.

Now, Imagine that I have two dates '20190601' and '20200631'

How could I create a dinamic columns with Month and Year? Is it possible?

thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros