如何将列转换为行

I have a table data where the data will be shown like this :

我有一个表数据,其中数据将显示如下:

DatePeriod  Good    Fair    Poor    NotCategorised  NotTested   GoodPercentage  FairPercentage  PoorPercentage  NotCategorisedPercentage    NotTestedPercentage
Feb-13  4   0   0   0   60  6.25    0   0   0   93.75

And for this one I have written query using UNPIVOT.

对于这一个,我使用UNPIVOT编写了查询。

Select DatePeriod,Legend FROM 
(
select DatePeriod,Good,Fair,Poor,NotCategorised,NotTested,GoodPercentage,
FairPercentage,poorPercentage,NotCategorisedPercentage,NotTestedPercentage  from #cte 

)P
UNPIVOT(Legend FOR  #CTE IN (Good,Fair,Poor,NotCategorised,NotTested))AS UNPVT;

I am not getting the required output

我没有得到所需的输出

my result set need to be like this :

我的结果集需要像这样:

year    Legend  percent count
Dec-12  Good    13.89   35
Dec-12  Fair    0   0
Dec-12  Poor    0   0
Dec-12  NC  0   0
Dec-12  NoData  86.11   217

Suggest me the best way.

建议我最好的方法。

2 个解决方案

#1

You did not specify what version of SQL Server you are using but using UNPIVOT for your data might be more difficult since you want to unpivot the columns in sets. It might be easier to use CROSS APPLY to get the result:

您没有指定正在使用的SQL Server版本,但是由于您想要取消集合中的列,因此使用UNPIVOT作为数据可能会更加困难。使用CROSS APPLY获取结果可能更容易:

select dateperiod,
  Legend,
  [Percent],
  [Count]
from yourtable t
cross apply
(
  select 'Good', Good, GoodPercentage union all
  select 'Fair', Fair, FairPercentage union all
  select 'Poor', Poor, PoorPercentage union all
  select 'NotCategorised', NotCategorised, NotCategorisedPercentage union all
  select 'NotTested', NotTested, NotTestedPercentage
) c (Legend, [Percent], [Count]);

See SQL Fiddle with Demo. If you are using a version of SQL Server that supports the VALUES clause then you could alter the above to the following:

请参阅SQL Fiddle with Demo。如果您使用的是支持VALUES子句的SQL Server版本,则可以将上述内容更改为以下内容:

select dateperiod,
  Legend,
  [Percent],
  [Count]
from yourtable t
cross apply
(
  values
    ('Good', Good, GoodPercentage),
    ('Fair', Fair, FairPercentage),
    ('Poor', Poor, PoorPercentage),
    ('NotCategorised', NotCategorised, NotCategorisedPercentage),
    ('NotTested', NotTested, NotTestedPercentage)
) c (Legend, [Percent], [Count]);

See SQL Fiddle with Demo. These give a result:

请参阅SQL Fiddle with Demo。这些结果如下:

| DATEPERIOD |         LEGEND | PERCENT | COUNT |
|     Feb-13 |           Good |       4 |  6.25 |
|     Feb-13 |           Fair |       0 |     0 |
|     Feb-13 |           Poor |       0 |     0 |
|     Feb-13 | NotCategorised |       0 |     0 |
|     Feb-13 |      NotTested |      60 | 93.75 |

#2

You can unpivot twice.

你可以两次解开。

Select 
    DatePeriod,
    Legend,
    amount,
    pc      
FROM 
(
select DatePeriod,
Good,Fair,Poor,NotCategorised,NotTested,GoodPercentage,
FairPercentage,poorPercentage,NotCategorisedPercentage,NotTestedPercentage  
from yourtable    
)P
UNPIVOT(Amount FOR  Legend IN (Good,Fair,Poor,NotCategorised,NotTested))AS UNPVT
unpivot(pc for var in (GoodPercentage, FairPercentage, PoorPercentage, NotCategorisedPercentage,NotTestedPercentage)) u2
where REPLACE(var,'Percentage','')=Legend

Or, if as it seems your data contains redundant information, do the percentage calculation

或者,如果您的数据似乎包含冗余信息,请执行百分比计算

Select 
    DatePeriod,
    Legend,
    amount, 
    100.0*Amount/nullif(SUM(Amount) over (partition by dateperiod),0)

FROM 
(
select DatePeriod,
Good,Fair,Poor,NotCategorised,NotTested,GoodPercentage,
FairPercentage,poorPercentage,NotCategorisedPercentage,NotTestedPercentage  from @t

)P
UNPIVOT(Amount FOR  Legend IN (Good,Fair,Poor,NotCategorised,NotTested))AS UNPVT

#1