T-SQL中的数据透视 [英] Pivot data in T-SQL

问题描述

我有一群人.我们称它们为A，B，C.我有一张桌子，显示每个月要付给他们多少钱....

I have a group of people. Lets call them A,B,C. I have a table that shows how much they were paid each month....

PERSON|MONTH|PAID
A      JAN   10
A      FEB   20   
B      JAN   10   
B      FEB   20   
B      SEP   30   
C      JAN   10   
C      JUNE  20   
C      JULY  30   
C      SEP   40 

此表可以并且确实可以持续多年..

THIS table can and does go on for years and years..

是否有一种方法可以使该表旋转(在我看来，实际上不需要汇总，通常在数据透视中完成)?

Is there a way to pivot this table (nothing as I see really needs to be aggregated which is usually done in pivots) In a table that looks like the following?

     JAN    FEB    MAR    APR    MAY    JUN    JUL    AGU    SEP
A    10     20
B    10     20     -      -      -      -      -      -      30
C    10     -      -      -      -      20     30     -      40

以前没有遇到过这样的事情，但是假设任何想法都是一个普遍的问题吗?

Haven't run into something like this before but assume it is a common problem any ideas?

推荐答案

如果您使用的是SQL Server 2005(或更高版本)，则代码如下:

If you are using SQL Server 2005 (or above), here is the code:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([Month])
                        FROM YourTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Person, Month, Paid
       FROM YourTable ) base
       PIVOT (Sum(Paid) FOR [Person]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

无论您拥有多少种不同的状态，这都将起作用.它使用PIVOT动态组装查询.使用动态列执行PIVOT的唯一方法是动态组装查询，这可以在SQL Server中完成.

This will work no matter how many different status you have. It dynamically assembles a query with PIVOT. The only way you can do PIVOT with dynamic columns is by assembling the the query dynamically, which can be done in SQL Server.

其他示例:

• 也许是SQL Server PIVOT?

• SQL Server PIVOT perhaps?
• How do I build a summary by joining to a single table with SQL Server?

这篇关于T-SQL中的数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！