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

      <bdo id='2Ecvi'></bdo><ul id='2Ecvi'></ul>

      <legend id='2Ecvi'><style id='2Ecvi'><dir id='2Ecvi'><q id='2Ecvi'></q></dir></style></legend>

      1. SQL Server:5 列的动态数据透视

        SQL Server : dynamic pivot over 5 columns(SQL Server:5 列的动态数据透视)

          <tbody id='PAbhx'></tbody>
      2. <small id='PAbhx'></small><noframes id='PAbhx'>

            <tfoot id='PAbhx'></tfoot>

              <legend id='PAbhx'><style id='PAbhx'><dir id='PAbhx'><q id='PAbhx'></q></dir></style></legend>

                • <bdo id='PAbhx'></bdo><ul id='PAbhx'></ul>

                  <i id='PAbhx'><tr id='PAbhx'><dt id='PAbhx'><q id='PAbhx'><span id='PAbhx'><b id='PAbhx'><form id='PAbhx'><ins id='PAbhx'></ins><ul id='PAbhx'></ul><sub id='PAbhx'></sub></form><legend id='PAbhx'></legend><bdo id='PAbhx'><pre id='PAbhx'><center id='PAbhx'></center></pre></bdo></b><th id='PAbhx'></th></span></q></dt></tr></i><div id='PAbhx'><tfoot id='PAbhx'></tfoot><dl id='PAbhx'><fieldset id='PAbhx'></fieldset></dl></div>
                  本文介绍了SQL Server:5 列的动态数据透视的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我很难弄清楚如何在具有多列的 SQL Server 2008 中进行动态数据透视.

                  I'm having a very tough time trying to figure out how to do a dynamic pivot in SQL Server 2008 with multiple columns.

                  我的示例表如下:

                  ID  YEAR  TYPE  TOTAL   VOLUME
                  DD1 2008    A   1000    10
                  DD1 2008    B   2000    20
                  DD1 2008    C   3000    30
                  DD1 2009    A   4000    40
                  DD1 2009    B   5000    50
                  DD1 2009    C   6000    60
                  DD2 2008    A   7000    70
                  DD2 2008    B   8000    80
                  DD2 2008    C   9000    90
                  DD2 2009    A   10000   100
                  DD2 2009    B   11000   110
                  DD2 2009    C   12000   120
                  

                  我正在尝试按如下方式调整它:

                  and I'm trying the pivot it as follows:

                  ID  2008_A_TOTAL    2008_A_VOLUME   2008_B_TOTAL    2008_B_VOLUME   2008_C_TOTAL    2008_C_VOLUME   2009_A_TOTAL    2009_A_VOLUME   2009_B_TOTAL    2009_B_VOLUME   2009_C_TOTAL    2009_C_VOLUME
                  DD1 1000            10              2000            20              3000            30              4000            40              5000            50              6000            60
                  DD2 7000            70              8000            80              9000            90              10000           100             11000           110             12000           120
                  

                  我的SQL Server 2008查询如下创建表:

                  My SQL Server 2008 query is as follows to create the table:

                  CREATE TABLE ATM_TRANSACTIONS 
                  (
                   ID varchar(5),
                   T_YEAR varchar(4),
                   T_TYPE varchar(3), 
                   TOTAL int,
                   VOLUME int
                  );
                  
                  INSERT INTO ATM_TRANSACTIONS
                  (ID,T_YEAR,T_TYPE,TOTAL,VOLUME)
                  
                  VALUES
                  ('DD1','2008','A',1000,10),
                  ('DD1','2008','B',2000,20),
                  ('DD1','2008','C',3000,30),
                  ('DD1','2009','A',4000,40),
                  ('DD1','2009','B',5000,50),
                  ('DD1','2009','C',6000,60),
                  ('DD2','2008','A',7000,70),
                  ('DD2','2008','B',8000,80),
                  ('DD2','2008','C',9000,90),
                  ('DD2','2009','A',10000,100),
                  ('DD2','2009','B',11000,110),
                  ('DD2','2009','C',1200,120);
                  

                  T_Year 列将来可能会改变,但 T_TYPE 列一般都知道,所以我不确定我是否可以使用 PIVOT 函数的组合带有动态代码的 SQL Server?

                  The T_Year column may change in the future but the T_TYPE column is generally know, so I'm not sure if I can use a combination of the PIVOT function in SQL Server with dynamic code?

                  我尝试按照此处的示例进行操作:

                  I tried following the example here:

                  http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

                  但我最终得到了奇怪的结果.

                  but I ended up with with weird results.

                  推荐答案

                  为了得到结果,您需要查看 TotalVolume 列.我的建议是首先编写查询的硬编码版本,然后将其转换为动态 SQL.

                  In order to get the result, you will need to look at unpivoting the data in the Total and Volume columns first before applying the PIVOT function to get the final result. My suggestion would be to first write a hard-coded version of the query then convert it to dynamic SQL.

                  UNPIVOT 过程将这些多列转换为行.UNPIVOT有几种方法,可以使用UNPIVOT功能,也可以使用CROSS APPLY.取消数据透视的代码类似于:

                  The UNPIVOT process converts these multiple columns into rows. There are a few ways to UNPIVOT, you can use the UNPIVOT function or you can use CROSS APPLY. The code to unpivot the data will be similar to:

                  select id, 
                      col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
                      value
                  from ATM_TRANSACTIONS t
                  cross apply
                  (
                      select 'total', total union all
                      select 'volume', volume
                  ) c (col, value);
                  

                  这为您提供以下格式的数据:

                  This gives you data in the format:

                  +-----+---------------+-------+
                  | id  |      col      | value |
                  +-----+---------------+-------+
                  | DD1 | 2008_A_total  |  1000 |
                  | DD1 | 2008_A_volume |    10 |
                  | DD1 | 2008_B_total  |  2000 |
                  | DD1 | 2008_B_volume |    20 |
                  | DD1 | 2008_C_total  |  3000 |
                  | DD1 | 2008_C_volume |    30 |
                  +-----+---------------+-------+
                  

                  然后就可以应用PIVOT函数了:

                  Then you can apply the PIVOT function:

                  select ID, 
                      [2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
                      [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume]
                  from
                  (
                      select id, 
                          col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
                          value
                      from ATM_TRANSACTIONS t
                      cross apply
                      (
                          select 'total', total union all
                          select 'volume', volume
                      ) c (col, value)
                  ) d
                  pivot
                  (
                      max(value)
                      for col in ([2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
                                  [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume])
                  ) piv;
                  

                  既然你有正确的逻辑,你可以把它转换成动态 SQL:

                  Now that you have the correct logic, you can convert this to dynamic SQL:

                  DECLARE @cols AS NVARCHAR(MAX),
                      @query  AS NVARCHAR(MAX)
                  
                  select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+t_type+'_'+col) 
                                      from ATM_TRANSACTIONS t
                                      cross apply
                                      (
                                          select 'total', 1 union all
                                          select 'volume', 2
                                      ) c (col, so)
                                      group by col, so, T_TYPE, T_YEAR
                                      order by T_YEAR, T_TYPE, so
                              FOR XML PATH(''), TYPE
                              ).value('.', 'NVARCHAR(MAX)') 
                          ,1,1,'')
                  
                  set @query = 'SELECT id,' + @cols + ' 
                              from 
                              (
                                  select id, 
                                      col = cast(t_year as varchar(4))+''_''+t_type+''_''+col, 
                                      value
                                  from ATM_TRANSACTIONS t
                                  cross apply
                                  (
                                      select ''total'', total union all
                                      select ''volume'', volume
                                  ) c (col, value)
                              ) x
                              pivot 
                              (
                                  max(value)
                                  for col in (' + @cols + ')
                              ) p '
                  
                  execute sp_executesql @query;
                  

                  这会给你一个结果:

                  +-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
                  | id  | 2008_A_total | 2008_A_volume | 2008_B_total | 2008_B_volume | 2008_C_total | 2008_C_volume | 2009_A_total | 2009_A_volume | 2009_B_total | 2009_B_volume | 2009_C_total | 2009_C_volume |
                  +-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
                  | DD1 |         1000 |            10 |         2000 |            20 |         3000 |            30 |         4000 |            40 |         5000 |            50 |         6000 |            60 |
                  | DD2 |         7000 |            70 |         8000 |            80 |         9000 |            90 |        10000 |           100 |        11000 |           110 |         1200 |           120 |
                  +-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
                  

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

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

                  相关文档推荐

                  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 文件)

                  <small id='jPUWE'></small><noframes id='jPUWE'>

                  • <bdo id='jPUWE'></bdo><ul id='jPUWE'></ul>
                      <tbody id='jPUWE'></tbody>

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

                          • <tfoot id='jPUWE'></tfoot>