1. <legend id='JqFaA'><style id='JqFaA'><dir id='JqFaA'><q id='JqFaA'></q></dir></style></legend>
      <tfoot id='JqFaA'></tfoot>

          <bdo id='JqFaA'></bdo><ul id='JqFaA'></ul>
      1. <small id='JqFaA'></small><noframes id='JqFaA'>

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

        在 SQL Server 中使用“Pivot"将行转换为列

        时间:2023-07-18
        <i id='58uy9'><tr id='58uy9'><dt id='58uy9'><q id='58uy9'><span id='58uy9'><b id='58uy9'><form id='58uy9'><ins id='58uy9'></ins><ul id='58uy9'></ul><sub id='58uy9'></sub></form><legend id='58uy9'></legend><bdo id='58uy9'><pre id='58uy9'><center id='58uy9'></center></pre></bdo></b><th id='58uy9'></th></span></q></dt></tr></i><div id='58uy9'><tfoot id='58uy9'></tfoot><dl id='58uy9'><fieldset id='58uy9'></fieldset></dl></div>
        1. <small id='58uy9'></small><noframes id='58uy9'>

          <legend id='58uy9'><style id='58uy9'><dir id='58uy9'><q id='58uy9'></q></dir></style></legend>

              <tfoot id='58uy9'></tfoot>
                • <bdo id='58uy9'></bdo><ul id='58uy9'></ul>

                    <tbody id='58uy9'></tbody>

                  本文介绍了在 SQL Server 中使用“Pivot"将行转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我已经阅读了 MS 数据透视表上的内容,但我仍然无法正确理解这一点.

                  I have read the stuff on MS pivot tables and I am still having problems getting this correct.

                  我有一个正在创建的临时表,我们会说第 1 列是商店编号,第 2 列是周数,最后第 3 列是某种类型的总数.此外,周数是动态的,商店数是静态的.

                  I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic, the store numbers are static.

                  Store      Week     xCount
                  -------    ----     ------
                  102        1        96
                  101        1        138
                  105        1        37
                  109        1        59
                  101        2        282
                  102        2        212
                  105        2        78
                  109        2        97
                  105        3        60
                  102        3        123
                  101        3        220
                  109        3        87
                  

                  我希望它以数据透视表的形式出现,如下所示:

                  I would like it to come out as a pivot table, like this:

                  Store        1          2          3        4        5        6....
                  ----- 
                  101        138        282        220
                  102         96        212        123
                  105         37        
                  109
                  

                  在侧面存储数字,在顶部存储周数.

                  Store numbers down the side and weeks across the top.

                  推荐答案

                  如果您使用的是 SQL Server 2005+,那么您可以使用 PIVOT 函数将数据从行转换为列.

                  If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.

                  如果周数未知,听起来您将需要使用动态 sql,但最初使用硬编码版本更容易查看正确的代码.

                  It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.

                  首先,这里有一些快速的表定义和可供使用的数据:

                  First up, here are some quick table definitions and data for use:

                  CREATE TABLE yt 
                  (
                    [Store] int, 
                    [Week] int, 
                    [xCount] int
                  );
                      
                  INSERT INTO yt
                  (
                    [Store], 
                    [Week], [xCount]
                  )
                  VALUES
                      (102, 1, 96),
                      (101, 1, 138),
                      (105, 1, 37),
                      (109, 1, 59),
                      (101, 2, 282),
                      (102, 2, 212),
                      (105, 2, 78),
                      (109, 2, 97),
                      (105, 3, 60),
                      (102, 3, 123),
                      (101, 3, 220),
                      (109, 3, 87);
                  

                  如果您的值已知,那么您将对查询进行硬编码:

                  If your values are known, then you will hard-code the query:

                  select *
                  from 
                  (
                    select store, week, xCount
                    from yt 
                  ) src
                  pivot
                  (
                    sum(xcount)
                    for week in ([1], [2], [3])
                  ) piv;
                  

                  参见 SQL 演示

                  那么如果您需要动态生成周数,您的代码将是:

                  Then if you need to generate the week number dynamically, your code will be:

                  DECLARE @cols AS NVARCHAR(MAX),
                      @query  AS NVARCHAR(MAX)
                  
                  select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                                      from yt
                                      group by Week
                                      order by Week
                              FOR XML PATH(''), TYPE
                              ).value('.', 'NVARCHAR(MAX)') 
                          ,1,1,'')
                  
                  set @query = 'SELECT store,' + @cols + ' from 
                               (
                                  select store, week, xCount
                                  from yt
                              ) x
                              pivot 
                              (
                                  sum(xCount)
                                  for week in (' + @cols + ')
                              ) p '
                  
                  execute(@query);
                  

                  请参阅SQL 演示.

                  动态版本,生成应转换为列的week 数字列表.两者都给出相同的结果:

                  The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:

                  | STORE |   1 |   2 |   3 |
                  ---------------------------
                  |   101 | 138 | 282 | 220 |
                  |   102 |  96 | 212 | 123 |
                  |   105 |  37 |  78 |  60 |
                  |   109 |  59 |  97 |  87 |
                  

                  这篇关于在 SQL Server 中使用“Pivot"将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:获取每组的前 1 行 下一篇:为什么我们总是喜欢在 SQL 语句中使用参数?

                  相关文章

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

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

                  <tfoot id='Ky7kE'></tfoot>