SQL Server 2008 R2 Pivot问题

I have a Table1 shown here:

我有一个Table1显示在这里:

;WITH Table1 AS (
SELECT *
FROM (VALUES 
(164021, 'Balajee', 100, 100, 'YTD'),
(164021, 'Balajee', 200, 100, 'YTD'),
(164021, 'Balajee', 400, 300, 'MTD'),
(164021, 'Balajee', 200, 100, 'MTD')
) as t (EmpID, [Emp Name], GROSS_PAY, NET_PAY, DUR_TYPE)
)

This table has column called duration type which can have value as YTD or MTD. Now I want my table after doing a select using Pivot to look like the one shown in the table below. Can someone help me to do this? I'm very new to this Pivot and need some help.

此表具有称为持续时间类型的列,其值可以是YTD或MTD。现在,在使用Pivot进行选择后,我想要我的表,看起来像下表中显示的那样。有人可以帮我这样做吗?我对这个Pivot很新,需要一些帮助。

EmpID   Empname Grosspay_ytd    net_pay_ytd Grosspay_mtd    netpay_mtd
------- ------- --------------- ----------- --------------- -----------
164021  Balajee 300             200         600             400

2 个解决方案

#1

You can use the PIVOT function to get the result however part of the problem is that you have the two columns that you want to pivot in two columns.

您可以使用PIVOT函数来获取结果,但问题的一部分是您有两列要在两列中进行透视。

Since your data to pivot is in two columns there are a few ways that you can get the result. You can use an aggregate function with a CASE expression:

由于要转移的数据分为两列,因此有几种方法可以获得结果。您可以将聚合函数与CASE表达式一起使用:

select empid, empname,
  sum(case when dur_type='YTD' then gross_pay else 0 end) gross_pay_ytd,
  sum(case when dur_type='YTD' then net_pay else 0 end) net_pay_ytd,
  sum(case when dur_type='MTD' then gross_pay else 0 end) gross_pay_mtd,
  sum(case when dur_type='MTD' then net_pay else 0 end) net_pay_mtd
from yt
group by empid, empname;

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

If you want to use the PIVOT function then you can UNPIVOT the data in the gross_pay and net_pay columns so the data exists in one column and then apply the PIVOT function. Since you are using SQL Server 2008 you can unpivot the data using CROSS APPLY with a VALUES clause:

如果要使用PIVOT函数,则可以对gross_pay和net_pay列中的数据进行UNPIVOT,以便数据存在于一列中,然后应用PIVOT函数。由于您使用的是SQL Server 2008,因此您可以使用带有VALUES子句的CROSS APPLY来取消数据:

select empid, empname, col+'_'+dur_type as col, value
from yt
cross apply 
(
  values
    ('gross_pay', gross_pay), ('net_pay', net_pay)
) c (col, value)

See Demo. This will convert the current data into the result:

见演示。这会将当前数据转换为结果:

|  EMPID | EMPNAME |           COL | VALUE |
--------------------------------------------
| 164021 | Balajee | gross_pay_YTD |   100 |
| 164021 | Balajee |   net_pay_YTD |   100 |
| 164021 | Balajee | gross_pay_YTD |   200 |
| 164021 | Balajee |   net_pay_YTD |   100 |

As you can see, we know have multiple rows for each employee with a column that contains the names of the columns you want in the final pivot result. When you apply the PIVOT function the code will be:

如您所见,我们知道每个员工都有多行,其中一列包含最终数据结果中所需列的名称。当您应用PIVOT函数时,代码将是:

select empid, empname,
  gross_pay_ytd, net_pay_ytd, gross_pay_mtd, net_pay_mtd
from
(
  select empid, empname, col+'_'+dur_type as col, value
  from yt
  cross apply 
  (
    values
      ('gross_pay', gross_pay), ('net_pay', net_pay)
  ) c (col, value)
) d
pivot
(
  sum(value)
  for col in (gross_pay_ytd, net_pay_ytd, gross_pay_mtd, net_pay_mtd)
) piv;

See SQL Fiddle with Demo. Both this version and the CASE version will give a result:

请参阅SQL Fiddle with Demo。这个版本和CASE版本都会给出一个结果:

|  EMPID | EMPNAME | GROSS_PAY_YTD | NET_PAY_YTD | GROSS_PAY_MTD | NET_PAY_MTD |
--------------------------------------------------------------------------------
| 164021 | Balajee |           300 |         200 |           600 |         400 |

#2

Try this sql.

试试这个sql。

select empid,empname,sum(case dur_type when dur_type='YTD' then gross_pay else 0 END)as grosspay_ytd,
sum(case when dur_type='MTD' then gross_pay else 0 END) as grosspay_mtd  ,
sum(case when dur_type='YTD' then net_pay else 0 END) as netpay_ytd,
sum(case when dur_type='MTD' then net_pay else 0 END) as netpay_mtd
from employee
group by empid,empname,dur_type

#1