Sql-server – Create multiple variable columns from single column value

sql server

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

Best Answer

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.

Related Question