Create multiple variable columns from single column value

Question

The following code gets the number of days for a specific month set in @month

DECLARE @month tinyint
, @Days varchar(max)
SET @month = 1 -January
;WITH DaysInMonth 
AS
(
 SELECT DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)))) D
 UNION ALL
 SELECT DATEADD(day, 1, D)
 FROM DaysInMonth
 WHERE D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime))))))
)
SELECT
 @Days = substring((SELECT ( ', ' + CONVERT(varchar(2), D, 104))
 FROM DaysInMonth
 FOR XML PATH( '' )
 ), 3, 1000 ) FROM DaysInMonth
SELECT @Days

I am getting the following output in a single column:

01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31

How can I get these values in different columns?.

The main aim for get this query is to join or union with another query that contains records marked with 1 or 0 set to a specific date. The final query will output a result similar to the one a have below but, for the full month sent in the parameter.

July 28 29 30 31 01 02 03
NULL Monday Tuesday Wednesday Thursday Friday Saturday Sunday
Jhon 1 0 1 0 1 0 0

Any other solution to get around similar output is welcome.Thank you

• Aaron Bertrand: So you just want 31 columns if the month has 31 days? What data will be in these columns? What will the columns be called? Won't you also need the year (think February)?
• Francisco: Hi @AaronBertrand, yes, I would like the number of columns based on the number of days in a specific month. I'm not worried about the year yet as the results would be only for the current year. I am planing to join this query with data that falls under the specific date for roster shifts. The main aim of this query is to display some data in a calendar view format.
• Aaron Bertrand: Can you explain your entire requirements in the question please? It's one thing to come up with dynamic column names, something completely different to put data in those columns to perform meaningful joins. If you show some actual roster data and how you want that to be output, we can probably make better recommendations (which may include formatting the data like a calendar on the client).
• Francisco: Hi @AaronBertrand, I've updated my question, please see my desired oputput. Thank you.
• Aaron Bertrand: The new sample output is confusing. Those are only seven days - is that July 28-31 and August 1-3? If so, then we are no longer talking about just the days in a single month... Can you show the full output you want to see for a given month, and for more than one user? Can you show the table structure and a few rows of sample data from the other table?

Answer 1

---

For the modifications to the question, here is a modified query:

declare @month datetime = '20140708';
set datefirst 1; -- align to Monday as first day of week
-- test data as per OP:
create table #t (
 Date Date not null primary key
 ,IsActive int not null
);
insert #t(Date,IsActive)
values ('20140728', 1)
 ,('20140729', 0)
 ,('20140730', 1)
 ,('20140731', 0)
 ,('20140801', 1)
 ,('20140802', 0)
 ,('20140803', 0);
with -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values 
 (1),(1),(1),(1),(1),
 (1),(1),(1),(1),(1)
 )E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
 select 0 as N union all
 select ROW_NUMBER() over (order by N) as N
 from E4
)
,calendar as (
 select top (datepart(dd, eomonth(@month)))
 cast(dateadd(dd,tally.N,@month) as date) as Date
 ,N
 from tally
 where N > 0
)
select
 WeekNo
 ,max(isnull(Mon,0)) as Mon
 ,max(isnull(Tue,0)) as Tue
 ,max(isnull(Wed,0)) as Wed
 ,max(isnull(Thu,0)) as Thu
 ,max(isnull(Fri,0)) as Fri
 ,max(isnull(Sat,0)) as Sat
 ,max(isnull(Sun,0)) as Sun
from (
 select
 calendar.Date
 ,cast(datename(dw,calendar.Date) as char(3)) as WeekDay
 ,datepart(ww,calendar.Date) as WeekNo
 ,data.IsActive
 from calendar
 left join #t data
 on data.Date = calendar.Date
) t
pivot ( max(IsActive) for WeekDay in (Mon,Tue,Wed,Thu,Fri,Sat,Sun)) pvt
group by WeekNo
;
go

yielding this:

WeekNo Mon Tue Wed Thu Fri Sat Sun
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
28 0 0 0 0 0 0 0
29 0 0 0 0 0 0 0
30 0 0 0 0 0 0 0
31 1 0 1 0 1 0 0
32 0 0 0 0 0 0 0

---

fro the original question: Not quite sure what this achieves, but the following works nicely:

declare @month datetime = '20140710';
with -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values 
 (1),(1),(1),(1),(1),
 (1),(1),(1),(1),(1)
 )E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
 select 0 as N union all
 select ROW_NUMBER() over (order by N) as N
 from E4
)
select -- the query requested by OP, using the CTE tally above
 [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
 [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
 [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
 [31]
from (
 select top (datepart(dd, eomonth(@month)))
 right('0' + cast(N as varchar(2)),2) as X,
 N
 from tally
 where N > 0
) t pivot (max(X) for N in (
 [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
 [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
 [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
 [31] )
) pvt
;

to yield this:

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
(1 row(s) affected)

or with @month int = '20140210' -- February we get:

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 NULL NULL NULL
(1 row(s) affected)

No RDBMS tagged, so used SQL Server syntax above.

• Aaron Bertrand: This is a little tidier I think, but I agree, I'm not sure exactly what this achieves on its own, given the requirements touched on in the comments.
• Pieter Geerkens: @AaronBertrand: I love Dynamic SQL when I can't avoid it, but in this case I will choose to avoid it. Half the query above is building a standard tally table which will (sooner or later) pre-exist as a table in most DB's.
• Aaron Bertrand: What I don't like is typing out 1-31 in both the SELECT and PIVOT sections, which you can't just magically get from a numbers table, and you'll always have 31 columns in the output even for months with < 31 days, which seems to defeat the purpose.
• Pieter Geerkens: @AaronBertrand: Fair enough - the real determinant will be the needs of the actual use-case.
• Aaron Bertrand: Also you have top 10,000 but you really only need 31 (or 42 if the OP includes days from adjacent months). And in case you need further prodding, the stacked CTE approach is not the best performer - a calendar table would be much better, but barring that, I think if I were to choose between stacked CTEs and other approaches with negligible performance differences, it would be my last choice simply due to syntax complexity - especially since you added a recursive CTE at the end, which I didn't test but I'm sure would have an impact on performance...