asked on Dynamic column name with CTE
Hello Guys
I made a query as you can see below:
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
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
* Microsoft sql 2017Microsoft SQL Server
Last Comment
Khan Consultoria
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
(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.
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
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
It is safer to avoid "between" and use the following construct insteadwhere a.datped between '20200101' and '20201231'
where a.datped >= '20200101' and a.dated < '20210101'
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.
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.
Not using between ( using >= and < ) is workable for all date/datetime/datetime2/timestamp data types and hence
(in my view) preferable in all cases.
ASKER
Guys,
Thanks a lot for your help, I got it.
Thanks a lot for your help, I got it.
Good luck.