CODEWITHSUNDEEP
sqlsql-serverpivot
SQLISHARD
SQLISHARD

Reputation: 87

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'

SEE EDITS AT BOTTOM

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.

WHERE NEW EDIT STARTS

My current results looks like this from GOFR's solution. 

BLDGID  LEASID  SUITID  INCCAT  Jan Feb Mar Apr May June    Jul Aug Sept    Oct Nov Dec
87000   100      AZ1    TAD     800                                         
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

See how row 1 and 2 have the same suitid, I would like to combine these into a single row. The entire table has duplicates of each suitid so it would look like the below. 

BLDGID  LEASID  SUITID  INCCAT  Jan Feb Mar Apr May June    Jul Aug Sept    Oct Nov Dec
87000   100      AZ1    TAD     800 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

Upvotes: 1

Views: 91

Answers (1)

gofr1
gofr1

Reputation: 15987

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

Upvotes: 3

Related Questions