CODEWITHSUNDEEP
sqlsql-serverpivot
SQLISHARD
SQLISHARD

Reputation: 87

How to alias fields defined in Pivot SQL Server without getting duplicates

I'm trying to figure out how to rename my pivoted columns. IE 201601 to January, 201602 to February and etc.

The rest of the code is fine.

When I add the alias to the select statement, it creates another column with the month name but, leaves the original so I end up with 201601, 201602, etc followed by columns January, February, March, etc.

SELECT *, 

[201601] as January,  
[201602] as February,   
[201603] as March,   
[201604] as April,   
[201605] as May,   
[201606] as June, 
[201607] as July,     
[201608] as August,       
[201609] as September,    
[201610] as October,       
[201611] as November,   
[201612] as December


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.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  ='85000' 
    AND c.INCCAT  ='RNT'  
    AND c.SRCCODE NOT LIKE 'CR'
    AND c.SRCCODE NOT LIKE 'PR'
    AND DESCRPTN NOT LIKE 'SECURITY APPLIED'


    AND c.PERIOD > '201512'
) as t
PIVOT (
SUM(TRANAMT) 
FOR PERIOD IN ([201601],[201602],[201603],[201604],[201605],[201606],[201607],[201608],[201609],[201610],[201611],[201612]) 




) revenueperspace

Upvotes: 1

Views: 3575

Answers (1)

Andrew
Andrew

Reputation: 7880

You are getting both columns because you are using SELECT * in the first line. You should use this instead:

SELECT BLDGID, LEASID, OCCPNAME, SUITID, INCCAT, SRCCODE,
    [201601] as January,  
    [201602] as February,   
    [201603] as March,   
    [201604] as April,   
    [201605] as May,   
    [201606] as June, 
    [201607] as July,     
    [201608] as August,       
    [201609] as September,    
    [201610] as October,       
    [201611] as November,   
    [201612] as December

But of course, what will happen next year? You will have to keep updating the query. With this, you can avoid that:

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', 
        DATENAME(MONTH, DATEADD(MONTH, CAST(SUBSTRING(c.PERIOD, 5, 2) AS INT), -1)) AS 'MONTH'
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.SRCCODE NOT LIKE 'PR'
    AND DESCRPTN NOT LIKE 'SECURITY APPLIED'
    AND c.PERIOD > '201512'
) as t
PIVOT (
SUM(TRANAMT) 
FOR MONTH IN ('January','February','March','April','May','June','July','August','September','October','November','December') 

) revenueperspace

The first change is convert your period to month name, and I do that in the subquery (201606 -> 'June', for example), so the pivot table doesn't even know that yyyymm format exists. Then just use the months you want in the list used for the pivot table and you are done.

Upvotes: 1

Related Questions