Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Msg 207, Level 16, State 1, Line 1 Invalid column name 'FullName'.

select FullName, 
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, total from Mdata) AS ST
PIVOT (
min(FullName) FOR  AltBusinessSeverity in (['1-Urgent'], ['2-High'], ['3-Medium (3000)'])
) AS PT
share|improve this question

2 Answers 2

You can't use the FullName as a SELECT from the PIVOT and as the aggregate. You need this:

select FullName, 
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, count(*) AS total from Mdata) AS ST
PIVOT (
SUM(Total) FOR  AltBusinessSeverity in (['1-Urgent'], ['2-High'], ['3-Medium (3000)'])
) AS PVT
share|improve this answer
    
this works but the totals come back as null. also i dont want a sum, i want to show the number in the total field as it is without modification for each severity level by each fullName row. –  kacalapy Sep 22 '11 at 10:12
    
@kacalapy maybe you want to show some sample data then? (Like build a temp table that people can work from, and give us the create-table and insert-data for us to work with that?) –  jcolebrand Sep 22 '11 at 14:17
    
@kacalapy, check the edited query. Without sample data as @jcolebrand pointed out it's hard to tell why it returns NULLS. However, in general with a PIVOT the inner SELECT needs to contain all of the fields you need in the PIVOT. One of those fields must be used in an aggregate within the PIVOT statement. Another field must be used after the FOR to designate the fields that are used to PIVOT. Neither of those fields may be used in the outer SELECT - instead, the values you place in the brackets will appear as columns, with the aggregate for values. –  Wil Sep 22 '11 at 15:30

If you want to show total as your data, then you need to do the following:

select FullName, 
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, total from Mdata) AS ST
PIVOT (
min(total) FOR  AltBusinessSeverity in (['1-Urgent'], ['2-High'], ['3-Medium (3000)'])
) AS PT
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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