CODEWITHSUNDEEP
sqlsql-serverpivot
user3725626
user3725626

Reputation: 35

SQL PIVOT query is repeating the same values for all pivoted rows

I have the following script:

select OFFC, 201501, 201412, 201411
from 
(
    select [OFFC], [PERIOD], [BILL_NUM] 
    from BLT_BILL
) as UP
PIVOT
(
    max([BILL_NUM]) 
    FOR [PERIOD] in ([201501],[201412],[201411])
) as pvt
order by OFFC

I wanted it to show me the last bill number created for each office. The results being returned are:

OFFC   (No column name) (No column name) (No column name)

NULL   201501   201412  201411
CHES   201501   201412  201411
LIV    201501   201412  201411
LON    201501   201412  201411
MCR    201501   201412  201411
SHEF   201501   201412  201411

Can someone please explain why my perdios are showing in the data rather than in the headers and why my max(bill_num) is not showing in the data area?

Upvotes: 1

Views: 661

Answers (2)

StuartLC
StuartLC

Reputation: 107237

By stating 

select OFFC, 201501, 201412, 201411

You are telling sql to return the literal (constant) numbers

You need to escape these:

select OFFC, [201501], [201412],[201411]

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The values in the select are numeric constants. You need square braces:

select OFFC, [201501], [201412], [201411]
from 
(
    select [OFFC], [PERIOD], [BILL_NUM] 
    from BLT_BILL
) as UP
PIVOT
(
    max([BILL_NUM]) 
    FOR [PERIOD] in ([201501],[201412],[201411])
) as pvt
order by OFFC

Upvotes: 2

Related Questions