SQL Query assistance 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 tbale 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      开发者_C百科     0    0           0       0       0.0612
HiRes         0    0           0.0408  0.1633  0.2041

I am using SQL 2008. I am habing 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], [Low-Medium], [Medium], [High], [VeryHigh] 
          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!!

You need to UNPIVOT data and then re-PIVOT values using desired column heads:

SELECT  pvt.*
FROM
(    
    SELECT  unpvt.PriorityText
            ,unpvt.PriorityText2
            ,unpvt.MyValueMyValue
    FROM    SourceTable src
    UNPIVOT( MyValueMyValue FOR PriorityText2 IN ([Priority],[LoRes],[Partial],[Unknown],[N_A],[HiRes]) ) unpvt
) src2
PIVOT( MAX(src2.MyValueMyValue) FOR src2.PriorityText IN ([Low],[Low-Medium],[Medium],[High],[Very High]) ) pvt

This solution is kind of ugly, but i think it will do what you are asking for and is pretty straight forward. There are more elegant and dynamic ways to transpose data with dynamic sql and with xml.

for example http://sql-tricks.blogspot.com/2011/04/sql-server-rows-transpose.html

-- POPULATE SAMPLE DATA
DECLARE @tbTemp table (PriorityText varchar(50), Priority float, LoRes float, Partial float, Unknown float, N_A float, HiRes float)

insert into @tbTemp (PriorityText,Priority,LoRes,Partial,Unknown,N_A, HiRes)
values 
    ('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)

;

with sourcetable ([Key],ColumnName,Value) -- Transposing into key/value-pair for each column
as
(
    select 'Priority', PriorityText, Priority from @tbTemp
    union all
    select 'LoRes', PriorityText, LoRes from @tbTemp
    union all
    select 'Partial', PriorityText, Partial from @tbTemp
    union all
    select 'Unknow', PriorityText, Unknown from @tbTemp
    union all
    select 'N_A', PriorityText, N_A from @tbTemp
    union all
    select 'HiRes', PriorityText, HiRes from @tbTemp
) 
select 
    grouptable.PriorityText,
        (select Value from sourcetable 
        where sourcetable.ColumnName = 'Low' 
        and sourcetable.[Key] = grouptable.PriorityText) as Low,
        (select Value from sourcetable 
        where sourcetable.ColumnName = 'Low-Medium' 
        and sourcetable.[Key] = grouptable.PriorityText) as [Low-Medium],
        (select Value from sourcetable 
        where sourcetable.ColumnName = 'Medium' 
        and sourcetable.[Key] = grouptable.PriorityText)as Medium,
        (select Value from sourcetable 
        where sourcetable.ColumnName = 'High' 
        and sourcetable.[Key] = grouptable.PriorityText) as High,
        (select Value from sourcetable 
        where sourcetable.ColumnName = 'Very High' 
        and sourcetable.[Key] = grouptable.PriorityText) as [Very High]
    from (
        select 'Priority' as PriorityText
        union all
        select 'LoRes' as PriorityText
        union all
        select 'Partial' as PriorityText
        union all
        select 'Unknow' as PriorityText     
        union all
        select 'N_A' as PriorityText        
        union all
        select 'HiRes' as PriorityText      
    ) grouptable -- Creating each row