• Status: Solved
• Priority: Medium
• Security: Public
• Views: 491

# Pivot Rows to Columns

I have a table the holds 2 values for each item, the Max and Min values along with the flag for those values. I need the Values and the levels to be on the same row so that I only have 1 item number with those values, I was thinking of using Pivot but not sure how to do it dynamically as there are 760 rows. Any help would be greatly appreciated.

``````SELECT
[ITEMNMBR] = EXT00103.PT_UD_Key ,[VALUE ] = EXT00103.TOTAL,[LEVELS] = EXT00103.PT_UD_Number
FROM         EXT00103
``````
0
skull52
• 2
• 2
1 Solution

Commented:
like this ?

select itemnmbr,[1] as max,[2] as min
from ext00103 as a
pivot (max(value) for levels in ([1],[2])) as pvt
where levels in ('1','2')
order by 1
0

Author Commented:
Thanks for the quick response Low, but I am getting the following  errors

Msg 207, Level 16, State 1, Line 3
Invalid column name 'value'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'levels'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'levels'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'levels'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'itemnmbr'.
0

Commented:
yes because i just read you column names from the result table you showed rather than your query

try this

select itemnmbr,[1] as max,[2] as min
from (SELECT
[ITEMNMBR] = PT_UD_Key ,[VALUE] = TOTAL,[LEVELS] = PT_UD_Number
FROM         EXT00103
where PT_UD_Number  in ('1','2')) as a
pivot (max(value) for levels in ([1],[2])) as pvt

order by 1
0

Author Commented:
Perfect, Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.