Avatar of Khan Consultoria
Khan Consultoria
Flag for Brazil asked on

Dynamic column name with CTE

Hello Guys

I made a query as you can see below:

create view vendasG 
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?

* Microsoft sql 2017Microsoft SQL Server

Avatar of undefined
Last Comment
Khan Consultoria

8/22/2022 - Mon
Jim Horn

Not entirely following what you mean by 'dinamic columns with Month and Year? ',  but if you're trying to create custom calendars I have two articles out there that speaks to that with downloadable code:   SQL Server Calendar Table and SQL Server Calendar Table:  Fiscal Years.

Good luck.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Horn

(this might be it)
If you're building a feed, and absolutely have to have column names like M##, then ignor this comment.

If you're building a report, a trick I often used when creating PIVOT queries like what ste5an has provided, and name them something like cm (for current month), cm_minus_one, cm_minus_two, etc.  Then in your reporting layer you can get all fancy and pass whatever the current month is, and re-label them 'July', 'August', 'September', etc.

That way, both your query and the report doesn't have to change as the months march on.

Beware of Between

Imagine please that you have [datep] that includes the time of day e.g. 2020-12-31 09:10:36.123456
If that is true then this following where clause will NOT include that data
where a.datped between '20200101' and '20201231'  

Open in new window

It is safer to avoid "between" and use the following construct instead

where a.datped >= '20200101' and a.dated < '20210101'

Open in new window

Then you can be assured that every row relating to 2020 will be included even 2020-12-31 09:10:36.123456 
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Between over DATE is absolutely fine. The real caveat is in the design and semantics of the data model:

A column named date must contain a DATE, no DATETIME or anything different. Only exception it may by a dimension key as lexical sortable INT, but then it should be named somethingDateKey.

Just my 2 cent.

It  is true, if the column is the date data type then between is workable.
Not using between ( using >= and < ) is workable for all date/datetime/datetime2/timestamp data types and hence
(in my view) preferable in all cases.

Khan Consultoria

Thanks a lot for your help, I got it.

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.