troubleshooting Question

Dynamic column name with CTE

Avatar of Khan Consultoria
Khan ConsultoriaFlag for Brazil asked on
* Microsoft sql 2017Microsoft SQL Server
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)


Open in new window


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
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
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