sql-server - Convert Month Days Into Weekdays Per Week In SQL

Question

I have a calendar table and I want to show each month in a grid, and when I select a month from the table, I have some row with monthday and weekday columns, but I must convert it to multiple rows per week to show the month in the grid. my select command to get month info is something like this

SELECT Monthday, Weekday FROM Calendar Where Month = 5

that Results to this :

Monthday | Weekday
--------- --------
   1          4
   2          5
   3          6
   4          7
   5          1
   6          2
   7          3
   8          4
   .          .
   .          .
   .          .

and I Want to Convert it To Something like this

1 | 2 | 3 | 4 | 5 | 6 | 7 
-- --- --- --- --- --- --
            1   2   3   4
5   6   7   8   .   .   .

just like a calendar grid.
I think the answer is by Pivot, but I don't know how, Do you know a solution how to convert my select command?

标签： sql-servertsqlselectcalendargrid

Answer 1

Let's suppose you have a calendar table with the structure mentioned below, which is populated with a query like the following:

CREATE TABLE Calendar (
    TheDate DATE PRIMARY KEY,
    YearNumber SMALLINT,
    MonthNumber SMALLINT,
    DayNumber SMALLINT,
    WeekdayNumber SMALLINT
)

INSERT INTO dbo.Calendar (TheDate, YearNumber, MonthNumber, DayNumber, WeekdayNumber)
SELECT x.TheDate, 
    YEAR(x.TheDate) AS YearNumber, MONTH(x.TheDate) AS MonthNumber, DAY(x.TheDate) AS DayNumber, 
    (DATEPART(WEEKDAY,x.TheDate)+@@DATEFIRST-2)%7+1 AS WeekdayNumber
FROM (
    SELECT TOP 365 DATEADD(DAY,N-1,'20210101') AS TheDate
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY low) AS N FROM master..spt_values) t 
    ORDER BY N
) x

The formula for WeekdayNumber is written this way to ignore the SET DATEFIRST setting and always consider Monday as the first day of week. If you prefer another day to be the first in the week, adjust -2 to another value.

To display something like a calendar for a particular month, you can use a query like this:

SELECT * FROM (
    SELECT DayNumber, WeekdayNumber, 
        DENSE_RANK() OVER (ORDER BY DayNumber-WeekdayNumber) AS WeekNumber
    FROM dbo.Calendar WHERE YearNumber=2021 AND MonthNumber=5
) t
PIVOT (MAX(DayNumber) FOR WeekdayNumber IN ([1],[2],[3],[4],[5],[6],[7])) p

This produces the following result:

WeekNumber           1      2      3      4      5      6      7
-------------------- ------ ------ ------ ------ ------ ------ ------
1                    NULL   NULL   NULL   NULL   NULL   1      2
2                    3      4      5      6      7      8      9
3                    10     11     12     13     14     15     16
4                    17     18     19     20     21     22     23
5                    24     25     26     27     28     29     30
6                    31     NULL   NULL   NULL   NULL   NULL   NULL

I am using just the DayNumber and WeekdayNumber columns to compute a week number and then I am using PIVOT to arrange the values for DayNumber in the desired format.