SQL - How to convert columns to rows

Question

I have the following SQL script:

DECLARE @Month AS INT = 5 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2015 --Set the YEAR for which you want to generate the Calendar.
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate));

SELECT
      SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 1 
               THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Sunday,
      SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 2 
               THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Monday,
      SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 3 
               THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Tuesday,
      SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 4 
               THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Wednesday,
      SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 5 
               THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Thursday,
      SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 6 
               THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Friday,
      SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 7 
               THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Saturday
FROM master.dbo.spt_values v
WHERE DATEADD(DD,number,@StartDate) BETWEEN @StartDate AND  
      DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate)) AND 
      v.type = 'P'
GROUP BY DATEPART(WEEK, DATEADD(DD,number,@StartDate))

This script generates this table:

enter image description here

But I would like to get a list like this:

enter image description here

Any clue?

I can maybe understand the sequential rows, but what is the use case for the ones with NULL? — Pittsburgh DBA, May 21 '15 at 01:19
I need the null ones because Im going to build a calendar view on a report designer. — VAAA, May 21 '15 at 01:19
possible duplicate of [SQL - How to get a complete month calendar table](http://stackoverflow.com/questions/30362411/sql-how-to-get-a-complete-month-calendar-table) — Nick.Mc, May 21 '15 at 01:28
In what way is this different to your other question? I have marked as duplicate — Nick.Mc, May 21 '15 at 01:28
If you're trying to create output in a specific format it is usually better to use a report generator; it is very awkward to output data in specific formats via SQL. — Dour High Arch, May 21 '15 at 01:54
@DourHighArch: I disagree - and the technique used to produce formats such as this is a standard practice for folding a pivoted report that has a multitude of uses besides simple formatting. — Pieter Geerkens, May 21 '15 at 01:58

score 0 · Answer 1 · answered May 21 '15 at 01:56

Try this:

DECLARE @Month AS INT = 5 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2015 --Set the YEAR for which you want to generate the Calendar.
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate));

with m1 as (
    SELECT
    SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 1 
    THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Sunday
    ,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 2 
    THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Monday
    ,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 3 
    THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Tuesday
    ,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 4 
    THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Wednesday
    ,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 5 
    THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Thursday
    ,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 6 
    THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Friday
    ,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 7 
    THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Saturday
    FROM master.dbo.spt_values v
    WHERE DATEADD(DD,number,@StartDate) BETWEEN @StartDate 
    AND DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
    AND v.type = 'P'
    GROUP BY DATEPART(WEEK, DATEADD(DD,number,@StartDate)) 
)
select
--m.RowNo,
     [WeekDay]  = d.[WeekDay]
    ,[Date]     = d.[Date]
    ,[Month]    = datename(month,@StartDate)
    ,[Year]     = @Year
from (
    select row_number() over (order by Sunday) as RowNo, * from m1
)m
cross apply (values 
     (0,'Sunday',    isnull(cast(Sunday as varchar(2)),' '))
    ,(1,'Monday',    isnull(cast(Monday as varchar(2)),' '))
    ,(2,'Tuesday',   isnull(cast(Tuesday as varchar(2)),' '))
    ,(3,'Wednesday', isnull(cast(Wednesday as varchar(2)),' '))
    ,(4,'Thursday',  isnull(cast(Thursday as varchar(2)),' '))
    ,(5,'Friday',    isnull(cast(Friday as varchar(2)),' '))
    ,(6,'Saturday',  isnull(cast(Saturday as varchar(2)),' '))
) d(DayNo, [WeekDay], [Date])
order by m.RowNo, d.DayNo
;

which yields as desired:

WeekDay   Date Month                          Year
--------- ---- ------------------------------ -----------
Sunday         May                            2015
Monday         May                            2015
Tuesday        May                            2015
Wednesday      May                            2015
Thursday       May                            2015
Friday    1    May                            2015
Saturday  2    May                            2015
Sunday    3    May                            2015
Monday    4    May                            2015
Tuesday   5    May                            2015
Wednesday 6    May                            2015
Thursday  7    May                            2015
Friday    8    May                            2015
Saturday  9    May                            2015
Sunday    10   May                            2015
Monday    11   May                            2015
Tuesday   12   May                            2015
Wednesday 13   May                            2015
Thursday  14   May                            2015
Friday    15   May                            2015
Saturday  16   May                            2015
Sunday    17   May                            2015
Monday    18   May                            2015
Tuesday   19   May                            2015
Wednesday 20   May                            2015
Thursday  21   May                            2015
Friday    22   May                            2015
Saturday  23   May                            2015
Sunday    24   May                            2015
Monday    25   May                            2015
Tuesday   26   May                            2015
Wednesday 27   May                            2015
Thursday  28   May                            2015
Friday    29   May                            2015
Saturday  30   May                            2015
Sunday    31   May                            2015
Monday         May                            2015
Tuesday        May                            2015
Wednesday      May                            2015
Thursday       May                            2015
Friday         May                            2015
Saturday       May                            2015

score 0 · Answer 2 · answered May 21 '15 at 02:53

0

Here is a SQL Fiddle that generates arbitrary sequences of dates in the format you want. I leave the UNION ALL of the NULL results as an exercise for the reader.

http://sqlfiddle.com/#!3/9eecb7/275

answered May 21 '15 at 02:53

Pittsburgh DBA

6,672
2
39
68

Giorgi Nakeuri · Answer 3 · 2015-05-21T06:59:30.670

Here is another version:

DECLARE @d DATE = '20150501';

WITH 
m AS(SELECT 1 AS d UNION ALL SELECT d+1 FROM m WHERE d < datediff(d, @d, dateadd(m, 1, @d))),
dt AS(SELECT YEAR(@d) y, DATENAME(m, @d) m, d, DATENAME(dw, DATEADD(dd, d-1, @d)) wd FROM m ),
wk AS (SELECT * FROM (VALUES (1, 'Monday'), (2, 'Tuesday'),
                     (3, 'Wednesday'), (4, 'Thursday'), (5, 'Friday'),
                     (6, 'Saturday'), (7, 'Sunday')) w(d, n))

SELECT 1 o, y, m, d, wd FROM dt
UNION ALL
SELECT 0, YEAR(@d), DATENAME(m, @d), NULL, w.n FROM wk w
WHERE d < (SELECT d FROM wk WHERE n = (SELECT TOP 1 wd FROM dt ORDER BY d))
UNION ALL
SELECT 2, YEAR(@d), DATENAME(m, @d), NULL, w.n FROM wk w
WHERE d > (SELECT d FROM wk WHERE n = (SELECT TOP 1 wd FROM dt ORDER BY d DESC))
ORDER BY o , d

Output:

o   y       m   d       wd
0   2015    May NULL    Monday
0   2015    May NULL    Tuesday
0   2015    May NULL    Wednesday
0   2015    May NULL    Thursday
1   2015    May 1       Friday
1   2015    May 2       Saturday
1   2015    May 3       Sunday
1   2015    May 4       Monday
1   2015    May 5       Tuesday
1   2015    May 6       Wednesday
1   2015    May 7       Thursday
1   2015    May 8       Friday
1   2015    May 9       Saturday
1   2015    May 10      Sunday
1   2015    May 11      Monday
1   2015    May 12      Tuesday
1   2015    May 13      Wednesday
1   2015    May 14      Thursday
1   2015    May 15      Friday
1   2015    May 16      Saturday
1   2015    May 17      Sunday
1   2015    May 18      Monday
1   2015    May 19      Tuesday
1   2015    May 20      Wednesday
1   2015    May 21      Thursday
1   2015    May 22      Friday
1   2015    May 23      Saturday
1   2015    May 24      Sunday
1   2015    May 25      Monday
1   2015    May 26      Tuesday
1   2015    May 27      Wednesday
1   2015    May 28      Thursday
1   2015    May 29      Friday
1   2015    May 30      Saturday
1   2015    May 31      Sunday

SQL - How to convert columns to rows

3 Answers3