SQL Query using Pivot

I have a table as follows:

PriorityText  Priority  LoRes   Partial  Unknown  N_A    开发者_如何学运维  HiRes
------------------------------------------------------------------
Very High     5         0.0612  0.0000   0.0612   0.0612   0.2041
High          4         0.1429  0.0000   0.1633   0.0000   0.1633
Medium        3         0.0000  0.0000   0.1020   0.0000   0.0408
Low-Medium    2         0.0000  0.0000   0.0000   0.0000   0.0000
Low           1         0.0000  0.0000   0.0000   0.0000   0.0000

I am tying to transpose the table into this:

PriorityText  Low  Low-Medium  Medium  High    Very High
--------------------------------------------------------
Priority      1    2           3       4       5
LoRes         0    0           0       0.1429  0.0612
Partial       0    0           0       0       0
Unknown       0    0           0.102   0.1633  0.0612
N_A           0    0           0       0       0.0612
HiRes         0    0           0.0408  0.1633  0.2041

I am using SQL 2008. I am having trouble coming up with the SQL syntax to perform a pivot on the data.

Can someone please share a SQL snippet that will solve this for me?

I have used the following to successfully pivot one row, but I do not know how to make it do all my rows.

SELECT VeryHigh AS VeryHigh, 
       High AS High, 
       Medium AS Medium, 
       [Low-Medium] AS [Low-Medium], 
       Low AS Low
 FROM  (SELECT [PriorityText], [LoRes] FROM @tbTemp) p
PIVOT (SUM(LoRes) FOR [PriorityText] in ([VeryHigh], [High], [Medium], [Low-Medium], [Low])) pvt

My test data in my table is as follows:

Priority PriorityText   LoRes   Partial  Unknown   N_A    HiRes
1        VeryHigh       0.05    11       54        0      9
2        High           0.14    22       54        0      3
3        Medium         0.07    33       65        0      7
4        Low-Medium     0.01    44       87        0      4
5        Low            0       55       9         0      0
NULL     NULL           NULL    NULL     NULL      NULL   NULL

Thank for any help!!

---

Not sure how hardcoded you can make this, but what your wanting to do is Transpose the Rows with the Columns.

SELECT p.[Type] as 'PriorityText', Low, [Low-Medium], Medium,High,[VeryHigh]
FROM (SELECT PriorityText, [Holder],[Type]
      FROM (SELECT PriorityText,Priority,LoRes,[Partial],Unknown,N_A,HiRes
              FROM Test) as sq_source
      UNPIVOT ([Holder] FOR [Type] IN
        (Priority,LoRes,[Partial],Unknown,N_A,HiRes)) as sq_up
     ) as sq 
 PIVOT (
    MIN([Holder])
    FOR PriorityText IN
       (VeryHigh,High,Medium,[Low-Medium],Low)
       ) as p
    order by CASE p.[Type] WHEN 'Priority' THEN 1
    WHEN 'LoRes' THEN 2
    WHEN 'Partial' THEN 3
    WHEN 'Unknown' THEN 4
    WHEN 'N_A' THEN 5
    ELSE 6 END ASC;

This should get you what you need. One thing to note is this only works if the columns:

• Priority
• LoRes
• Partial
• Unknown
• N_A
• HiRes

are of the same data type (in my test it was decimal(5,4)). If yours are deferent then you will need to do an initial select and convert them to a common data type and use that as the sq_source.