• <legend id='sK0MW'><style id='sK0MW'><dir id='sK0MW'><q id='sK0MW'></q></dir></style></legend>
  • <small id='sK0MW'></small><noframes id='sK0MW'>

    <i id='sK0MW'><tr id='sK0MW'><dt id='sK0MW'><q id='sK0MW'><span id='sK0MW'><b id='sK0MW'><form id='sK0MW'><ins id='sK0MW'></ins><ul id='sK0MW'></ul><sub id='sK0MW'></sub></form><legend id='sK0MW'></legend><bdo id='sK0MW'><pre id='sK0MW'><center id='sK0MW'></center></pre></bdo></b><th id='sK0MW'></th></span></q></dt></tr></i><div id='sK0MW'><tfoot id='sK0MW'></tfoot><dl id='sK0MW'><fieldset id='sK0MW'></fieldset></dl></div>

    <tfoot id='sK0MW'></tfoot>
          <bdo id='sK0MW'></bdo><ul id='sK0MW'></ul>

        在 T-SQL 中透视数据

        Pivot data in T-SQL(在 T-SQL 中透视数据)

          • <i id='ixuL9'><tr id='ixuL9'><dt id='ixuL9'><q id='ixuL9'><span id='ixuL9'><b id='ixuL9'><form id='ixuL9'><ins id='ixuL9'></ins><ul id='ixuL9'></ul><sub id='ixuL9'></sub></form><legend id='ixuL9'></legend><bdo id='ixuL9'><pre id='ixuL9'><center id='ixuL9'></center></pre></bdo></b><th id='ixuL9'></th></span></q></dt></tr></i><div id='ixuL9'><tfoot id='ixuL9'></tfoot><dl id='ixuL9'><fieldset id='ixuL9'></fieldset></dl></div>
          • <small id='ixuL9'></small><noframes id='ixuL9'>

          • <tfoot id='ixuL9'></tfoot>
            <legend id='ixuL9'><style id='ixuL9'><dir id='ixuL9'><q id='ixuL9'></q></dir></style></legend>
              <bdo id='ixuL9'></bdo><ul id='ixuL9'></ul>
                <tbody id='ixuL9'></tbody>
                1. 本文介绍了在 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 连接到单个表来构建摘要?

                  这篇关于在 T-SQL 中透视数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Building a comma separated list?(建立一个逗号分隔的列表?)
                  Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column(尽管每列都使用了 varchar(MAX),但在导入 CSV 文件时 SQL Server 中出现错误)
                  How can I import an Excel file into SQL Server?(如何将 Excel 文件导入 SQL Server?)
                  Export table to file with column headers (column names) using the bcp utility and SQL Server 2008(使用 bcp 实用程序和 SQL Server 2008 将表导出到带有列标题(列名称)的文件)
                  Concat field value to string in SQL Server(将字段值连接到 SQL Server 中的字符串)
                  SQL Server Bulk insert of CSV file with inconsistent quotes(SQL Server 批量插入带有不一致引号的 CSV 文件)
                    <tbody id='vYKGK'></tbody>

                2. <i id='vYKGK'><tr id='vYKGK'><dt id='vYKGK'><q id='vYKGK'><span id='vYKGK'><b id='vYKGK'><form id='vYKGK'><ins id='vYKGK'></ins><ul id='vYKGK'></ul><sub id='vYKGK'></sub></form><legend id='vYKGK'></legend><bdo id='vYKGK'><pre id='vYKGK'><center id='vYKGK'></center></pre></bdo></b><th id='vYKGK'></th></span></q></dt></tr></i><div id='vYKGK'><tfoot id='vYKGK'></tfoot><dl id='vYKGK'><fieldset id='vYKGK'></fieldset></dl></div>
                3. <legend id='vYKGK'><style id='vYKGK'><dir id='vYKGK'><q id='vYKGK'></q></dir></style></legend>
                    <tfoot id='vYKGK'></tfoot>
                      • <small id='vYKGK'></small><noframes id='vYKGK'>

                          <bdo id='vYKGK'></bdo><ul id='vYKGK'></ul>