My problem is pivoting one single Date column into my row headings while all my current Column headings become rows.

My Column headings are [Date], which contains 50 years of days (from an awesome link provided by scsimon), and the other columns are vehicle license plates.

What I learned, from Mitch Wheat, is that using dates as individual columns, I am designing my table poorly. A google search result told me there is a hard limit on columns.

So I am back here now, after trial and error, left wanting.

My code...

SELECT [Date],[858-456],[128-5045],[111-7090],[111-7091],[111-7092],[111-7093],[133-1977],[133-1978],[133-1979],[133-9693],[113-9678],[133-1996],[115-2776],[115-2777],[128-5095]
FROM DateDimension
WHERE Date BETWEEN '1/19/2019' AND '1/25/2019'
FOR Date BETWEEN '1/19/2019' AND '1/25/2019';

My error message results in "Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'pivot'."

I really hope my question is understandable. I found plenty of tutorials, and questions, on pivoting tables that contain only small amounts of data and targeting that. My table contains 50 years worth of dates.

Now for my question. What am I doing wrong when it comes to switching all my date column to the row headers and all my column headers to the first column?

Edit: All column titles: Date, 858-456, 128-5045 111-7090, 111-7091, 111-7092, 111-7093, 133-1977, 133-1978, 133-1979, 133-9693, 113-9678, 133-1996, 115-2776, 115-2777, 128-5095

The Between part is due to only wanting to view a specific range of dates for all of the vehicles (license plates).

  • 1
    Here is the syntax for PIVOT. – SqlWorldWide May 14 '19 at 21:41
  • 1
    Have you gone through the various pivot questions on this site or on Stack Overflow? There are quite a lot of them now, which makes it all the more probable that your problem may already have a solution, sitting there and waiting to be found. Use the search bar at the top of every page to locate those solutions. Use the Help Center (top right of every page) to learn how to use the search. In short, please do your homework first. That's an important part of how sites like this one are supposed to work. We accumulate questions and answers so that everyone – including you – can benefit from them. – Andriy M May 16 '19 at 14:17

Your PIVOT syntax is incorrect. You don't filter records in the PIVOT, you do that in the inner SELECT that represents your source for the PIVOT. I've got an example below, but I also should point out that it requires manually entering the dates you're pivoting as columns. If this date range will change or is to be parameterised then you need to look into dynamically generating this PIVOT statement. There are lots of questions and answers on DBA.SE, like this, that can help you.


CREATE TABLE ##DateDimension (DateVal DATETIME, StrVal NVARCHAR(25))
    @Counter INT = 0


WHILE (SELECT COALESCE(MAX(DateVal), '1900-01-01') FROM ##DateDimension) < GETDATE()
    INSERT INTO ##DateDimension (DateVal, StrVal)
    VALUES (DATEADD(DAY, @Counter, @StartDate), 'Data')

    SET @Counter = @Counter + 1

Query Example:

    [01/02/2019], [02/02/2019], [03/02/2019], [04/02/2019], [05/02/2019], [06/02/2019], [07/02/2019]
    SELECT CONVERT(VARCHAR(25), DateVal, 103) AS DateVal, StrVal 
    FROM ##DateDimension
    WHERE DateVal BETWEEN '2019-02-01' AND '2019-02-08'
) src
    MAX(StrVal) FOR DateVal IN ([01/02/2019], [02/02/2019], [03/02/2019], [04/02/2019], [05/02/2019], [06/02/2019], [07/02/2019])
) pvt


01/02/2019  02/02/2019  03/02/2019  04/02/2019  05/02/2019  06/02/2019  07/02/2019
Data        Data        Data        Data        Data        Data        Data

EDIT: Based on comments, below is a new example pivoting the Licence Plates before left joining to the date dimension.

CREATE TABLE ##LicencePlates (LicPlate NVARCHAR(255),
    DateVal DATETIME,
    Department NVARCHAR(25))
INSERT INTO ##LicencePlates (LicPlate, DateVal, Department)
VALUES ('858-456', '2019-02-01', 'ABC'),
    ('128-5045', '2019-02-03', 'DEF'),
    ('111-7090', '2019-02-15', 'ABC'),
    ('111-7091', '2019-02-27', 'DEF'),
    ('111-7092', '2019-02-08', 'ABC'),
    ('111-7093', '2019-02-21', 'XYZ')

CREATE TABLE ##DateDimension (DateVal DATETIME)
DECLARE @StartDate DATETIME = '2019-05-16 00:00:00.000',
    @Counter INT = 0

SET @StartDate = DATEADD(YEAR, -5, @StartDate)

WHILE (SELECT COALESCE(MAX(DateVal), '1900-01-01') FROM ##DateDimension) < GETDATE()
    INSERT INTO ##DateDimension (DateVal)
    VALUES (DATEADD(DAY, @Counter, @StartDate))

    SET @Counter = @Counter + 1

WITH LicencePlatesPivot AS
    SELECT DateVal,
        COALESCE([858-456], '') AS [858-456],
        COALESCE([128-5045], '') AS [128-5045],
        COALESCE([111-7090], '') AS [111-7090],
        COALESCE([111-7091], '') AS [111-7091],
        COALESCE([111-7092], '') AS [111-7092],
        COALESCE([111-7093], '') AS [111-7093]
        SELECT LicPlate, DateVal, Department FROM ##LicencePlates
    ) src
        MAX(Department) FOR LicPlate IN ([858-456],[128-5045],[111-7090],[111-7091],[111-7092],[111-7093])
    ) pvt

SELECT dd.DateVal, 
    COALESCE(pvt.[858-456], '') AS [858-456],
    COALESCE(pvt.[128-5045], '') AS [128-5045],
    COALESCE(pvt.[111-7090], '') AS [111-7090],
    COALESCE(pvt.[111-7091], '') AS [111-7091],
    COALESCE(pvt.[111-7092], '') AS [111-7092],
    COALESCE(pvt.[111-7093], '') AS [111-7093]
FROM ##DateDimension dd
LEFT JOIN LicencePlatesPivot pvt ON pvt.DateVal = dd.DateVal
WHERE dd.DateVal BETWEEN '2019-02-01' AND '2019-02-28'

DROP TABLE ##DateDimension
DROP TABLE ##LicencePlates
| improve this answer | |
  • I feel that I am poorly communicating this the wrong way, my apologies. If this was an excel spreadsheet style view, my row 1 columns are the headers for every day for the next 50 years. Column A2 through A17 needs to be the license plates. In the middle, where the vehicle license plate intersects with the day is where the vehicle will be the department name that rents our vehicle. This is how I need to pivot the table. Maybe my idea is poor design. I'm trying to replace a whiteboard, laminated 365/366 day notebook, and file drawer of reservations. – SeidlerAM May 15 '19 at 14:01
  • Okay, so you actually need to pivot your licence plate table first, then left join that to the date dimension. See the updated answer with a new example. – HandyD May 16 '19 at 2:17
  • This makes a lot of sense. Thank you! I really appreciate your insight – SeidlerAM May 16 '19 at 18:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.