SQLISHARD SQLISHARD - 3 months ago 15
SQL Question

Data error when trying to pivot

Trying to pivot data in SQL Server. Getting errors and have never written anything like this before.

How it looks now (We have hundred of categories for each of the categories, please keep this in mind with your suggestions)

BLDGID LEASID SUITID INCCAT AMOUNT PERIOD
87000 100 AZ1 TAD 800 201601
87000 200 AZ2 TAD 900 201603
87000 300 AZ3 TAD 100 201607
88000 400 AZ4 TAD 100 201607
89000 500 AZ5 TAD 200 201609
89000 600 AZ6 TAD 900 201611
90000 700 AZ7 TAD 500 201612
90000 800 AZ8 TAD 950 201602


How I want it to look

BLDGID LEASID SUITID INCCAT Jan Feb Mar Apr May June Jul Aug Sept Oct Nov Dec
87000 100 AZ1 TAD 800
87000 200 AZ2 TAD 900
87000 300 AZ3 TAD 1000
88000 400 AZ4 TAD 100
89000 500 AZ5 TAD 200
89000 600 AZ6 TAD 900
90000 700 AZ7 TAD 500
90000 800 AZ8 TAD 950


The code I've been trying (Not sure if I'm on the right track here)

SELECT c.BLDGID AS 'BLDGID',
c.LEASID AS 'LEASID',
l.OCCPNAME AS 'OCCPNAME',
l.SUITID AS 'SUITID',
c.INCCAT AS 'INCCAT',
c.SRCCODE AS 'SRCCODE',
c.TRANAMT AS 'TRANAMT',
c.DESCRPTN AS 'DESCRPTN',
c.PERIOD AS 'PERIOD'
FROM SQLDATA.dbo.LEAS l
INNER JOIN SQLDATA.dbo.CMLEDG c
ON l.BLDGID = c.BLDGID AND SQLDATA.dbo.LEAS.LEASID = c.LEASID
PIVOT (
MAX(TRANAMT)
FOR PERIOD IN ([201601],[201602],[201603],[201607],[201609],[201611],[201612])
) as REVENUEPERITEM
WHERE c.BLDGID &SPARM01
AND c.INCCAT &SPARM02
AND c.SRCCODE NOT LIKE 'CR'
AND c.DESCRPTN NOT LIKE 'CREDITAPPLY'
AND c.DESCRPTN NOT LIKE 'RECEIPT'
AND c.PERIOD > '201512'


As a side note, we use a special program at work the SPARM things you see are items for that program allow me to edit the query without having to re-write it.

The issue I'm getting is an error that says,


There is an error in the SQL statement, Pivot grouping columns must be comparable. The type of column "ADDLDESC" is "text", which is not comparable.

Answer

Your query is almost there, try to rebuild it like this:

SELECT *
FROM (
    SELECT  c.BLDGID AS 'BLDGID', 
            c.LEASID AS 'LEASID', 
            l.OCCPNAME AS 'OCCPNAME', 
            l.SUITID AS 'SUITID', 
            c.INCCAT AS 'INCCAT', 
            c.SRCCODE AS 'SRCCODE', 
            c.TRANAMT AS 'TRANAMT', 
            c.DESCRPTN AS 'DESCRPTN', 
            c.PERIOD AS 'PERIOD'
    FROM SQLDATA.dbo.LEAS l
    INNER JOIN SQLDATA.dbo.CMLEDG c
        ON l.BLDGID = c.BLDGID AND l.LEASID = c.LEASID
    WHERE  c.BLDGID &SPARM01
        AND c.INCCAT &SPARM02 
        AND c.SRCCODE NOT LIKE 'CR'
        AND c.DESCRPTN NOT LIKE 'CREDITAPPLY' 
        AND c.DESCRPTN NOT LIKE 'RECEIPT'
        AND c.PERIOD > '201512'
    ) as t
PIVOT (
    MAX(TRANAMT) 
    FOR PERIOD IN ([201601],[201602],[201603],[201607],[201609],[201611],[201612])
) as REVENUEPERITEM

And here is how your can pivot what you have (I use CTE because I dont have your tables):

;WITH cte AS (
SELECT *
FROM (VALUES
(87000,   100,     'AZ1',        'TAD',    800,   '201601'),
(87000,   200,     'AZ2',        'TAD',    900,   '201603'),
(87000,   300,     'AZ3',        'TAD',    100,   '201607'),
(88000,   400,     'AZ4',        'TAD',    100,   '201607'),                                     
(89000,   500,     'AZ5',        'TAD',    200,   '201609'),
(89000,   600,     'AZ6',        'TAD',    900,   '201611'),
(90000,   700,     'AZ7',        'TAD',    500,   '201612'),
(90000,   800,     'AZ8',        'TAD',    950,   '201602')
) as t([BLDGID], [LEASID], [SUITID], [INCCAT], [AMOUNT], [PERIOD])
)

SELECT *
FROM cte
PIVOT (
    MAX([AMOUNT]) FOR [PERIOD] IN ([201601],[201602],[201603],[201604])
) pvt

Output:

BLDGID  LEASID  SUITID  INCCAT  201601  201602  201603  201604
87000   100     AZ1     TAD     800     NULL    NULL    NULL
87000   200     AZ2     TAD     NULL    NULL    900     NULL
87000   300     AZ3     TAD     NULL    NULL    NULL    NULL
88000   400     AZ4     TAD     NULL    NULL    NULL    NULL
89000   500     AZ5     TAD     NULL    NULL    NULL    NULL
89000   600     AZ6     TAD     NULL    NULL    NULL    NULL
90000   700     AZ7     TAD     NULL    NULL    NULL    NULL
90000   800     AZ8     TAD     NULL    950     NULL    NULL
Comments