0

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'
pivot
(
FOR Date BETWEEN '1/19/2019' AND '1/25/2019';
)piv

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).

closed as unclear what you're asking by Andriy M, mustaccio, MDCCL, dezso, Philᵀᴹ May 17 at 10:01

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Here is the syntax for PIVOT. – SqlWorldWide May 14 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 at 14:17
0

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.

Setup:

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

SET @StartDate = DATEADD(YEAR, -1, GETDATE())

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

    SET @Counter = @Counter + 1
END
GO

Query Example:

SELECT
    [01/02/2019], [02/02/2019], [03/02/2019], [04/02/2019], [05/02/2019], [06/02/2019], [07/02/2019]
FROM
(
    SELECT CONVERT(VARCHAR(25), DateVal, 103) AS DateVal, StrVal 
    FROM ##DateDimension
    WHERE DateVal BETWEEN '2019-02-01' AND '2019-02-08'
) src
PIVOT
(
    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

Results:

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))
GO
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')
GO

CREATE TABLE ##DateDimension (DateVal DATETIME)
GO
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()
BEGIN
    INSERT INTO ##DateDimension (DateVal)
    VALUES (DATEADD(DAY, @Counter, @StartDate))

    SET @Counter = @Counter + 1
END
GO

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]
    FROM
    (
        SELECT LicPlate, DateVal, Department FROM ##LicencePlates
    ) src
    PIVOT
    (
        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
  • 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 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 at 2:17
  • This makes a lot of sense. Thank you! I really appreciate your insight – SeidlerAM May 16 at 18:45

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