在SQL Server 2008中将行转换为列

I have a table that has records like this:

我有一个表有这样的记录:

Particluar       | Amount
Excise Duty 12 % | 2775.00
Edu CESS 2%      | 56.00
HR Edu CESS 1%   | 28.00
Sale CST 2%      | 520.00

How do get the following from a select query?

如何从选择查询中获取以下内容?

Excise Duty 12 % | Edu CESS 2% | HR Edu CESS 1% | Sale CST 2%
2775.00          | 56.00       | 28.00          | 520.00

2 个解决方案

#1

There are several ways that you can PIVOT the data from rows into columns.

有几种方法可以将数据从行数据分组到列中。

You can use an aggregate function with a CASE expression:

您可以将聚合函数与CASE表达式一起使用:

select 
  sum(case when Particluar='Excise Duty 12 %' then amount else 0 end) [Excise Duty 12 %],
  sum(case when Particluar='Edu CESS 2%' then amount else 0 end) [Edu CESS 2%],
  sum(case when Particluar='HR Edu CESS 1%' then amount else 0 end) [HR Edu CESS 1%],
  sum(case when Particluar='Sale CST 2%' then amount else 0 end) Sale CST 2%]
from yourtable;

Starting in SQL Server 2005, the PIVOT function can transform the data into columns:

从SQL Server 2005开始,PIVOT函数可以将数据转换为列:

select *
from
(
  select Particluar, Amount
  from yourtable
) d
pivot
(
  sum(amount)
  for Particluar in ([Excise Duty 12 %], [Edu CESS 2%], 
                     [HR Edu CESS 1%], [Sale CST 2%])
) piv;

If you have an unknown number of Particluar values, then you can use dynamic SQL to get the result:

如果您具有未知数量的Particluar值,则可以使用动态SQL来获取结果:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Particluar) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
                select Particluar, Amount
                from yourtable
            ) x
            pivot 
            (
                sum(Amount)
                for Particluar in (' + @cols + ')
            ) p '

execute(@query)

#2

Try this one -

试试这个 -

SELECT pt.*
FROM dbo.table1 o
PIVOT
(
    COUNT(o.Amount)
    FOR o.Particluar IN (
          [Excise Duty 12 %]  
        , [Edu CESS 2%] 
        , [HR Edu CESS 1%]
        , [Sale CST 2%]
    )
) pt

#1