1

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
Improve this question

2 Answers 2

Reset to default
3

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
Improve this answer
1

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
Improve this answer
3
  • 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
    Commented Sep 22, 2011 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 ♦
    Commented Sep 22, 2011 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
    Commented Sep 22, 2011 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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