SQL Server查询:行使列(枢轴?) [英] SQL Server query : rows make columns (Pivot?)

问题描述

我很想查询.

我创建的此查询

WITH TATH(Priority, EntryDate) AS 
(
    SELECT TH.Priority as Priority, DATEADD(dd, 0, DATEDIFF(dd, 0, entryDate)) as EntryDate  
      FROM TicketAssignment TA, TicketHeader TH 
     WHERE TA.TicketID = TH.TicketID   
       AND TA.Company = 'IT'
       AND TA.CurrentRole IN ('SA1B','SA1C','SDA')
) 
SELECT Priority, convert(varchar(10), EntryDate,103) as EntryDate, COUNT(*) AS Count  
FROM TATH 
GROUP BY Priority, EntryDate 

到目前为止，结果是:

Priority    EntryDate   Count
0   25/11/2011  1
1   25/11/2011  2
2   25/11/2011  36
3   25/11/2011  8
0   28/11/2011  3
1   28/11/2011  3
2   28/11/2011  37
3   28/11/2011  37

我想使结果看起来像这样

EntryDate   Priorty0    Priority1   Priority2   Priority3
25/11/2011  1   2   36  8
28/11/2011  3   3   37  37

我认为这超出了我的能力，但我无法实现.

如果枢轴不是一个好的解决方案，那么我必须搜索(或研究)什么?

你能帮我个问题吗?

解决方案

旋转很像分组.您可以将其视为具有特殊效果"的有限分组.限制在于，只能有一个聚合列. (在正常的GROUP BY查询中，自然可以有多个.)当然，通过特殊效果"，我的意思是将其他一列(也只有一列)转换为多列. /p>

让我们以您的GROUP BY查询为例.您在输出中有三列.其中之一Count是包含聚合信息的列.那将是分散在PIVOT查询中的多个列中的那个.另一列Priority是结果按其分组的其他两个列之一，也是需要透视的列.最后，EntryDate是另一个GROUP BY列.它应该只是保持原样，因为它不直接参与旋转.

现在让我们逐步了解您的主SELECT如何从常规的GROUP BY查询转换为PIVOT查询:

1. 由于在PIVOT查询中暗含了分组，因此删除了GROUP BY子句.而是引入了PIVOT子句.

2. Count列的表达式从SELECT子句移到PIVOT子句.

3. Priority列的拆分在PIVOT子句中定义.

4. 将SELECT子句中的Priority和Count列替换为PIVOT子句中定义的列的列表.

5. EntryDate列在SELECT子句中保持不变.

这是结果查询，其中的注释标记了上述转换的每个部分:

WITH TATH(Priority, EntryDate) AS 
(
    SELECT TH.Priority as Priority, DATEADD(dd, 0, DATEDIFF(dd, 0, entryDate)) as EntryDate
      FROM TicketAssignment TA, TicketHeader TH 
     WHERE TA.TicketID = TH.TicketID   
       AND TA.Company = 'IT'
       AND TA.CurrentRole IN ('SA1B','SA1C','SDA')
) 
SELECT
  convert(varchar(10), EntryDate,103) as EntryDate,                       -- #5
  [0] AS Priority0, [1] AS Priority1, [2] AS Priority2, [3] AS Priority3  -- #4
FROM TATH
PIVOT (                                                                   -- #1
  COUNT(*)                                                                -- #2
  FOR Priority IN ([0], [1], [2], [3])                                    -- #3
) p

/*  -- your original main query, for comparison
SELECT
  Priority,                                                               -- #4
  convert(varchar(10),                                                    -- #5
  EntryDate,103) as EntryDate, COUNT(*) AS Count                          -- ##2&4
FROM TATH 
GROUP BY Priority, EntryDate                                              -- #1
*/

PIVOT子句中的列列表上还有一条注释.首先，您必须了解，根据列数及其名称，SQL查询的结果集应该是固定的 ^* .这意味着您必须显式枚举要在输出中看到的所有已转换的列.名称是从要透视的列的值派生的，但应将其指定为 names ，而不是值.这就是为什么您可以在列出的数字周围看到方括号.由于数字本身不满足常规标识符的规则 ，必须将它们定界.

您还可以看到，您可以像其他任何列或表达式一样在SELECT子句中对透视列进行别名化.因此，最后，您不必以毫无意义的0，1等标识符结尾，而可以为这些列分配任何您喜欢的名称.

---

_{^* 如果希望动态化透视列的数目和/或名称，则必须动态构建查询，即首先收集名称，然后将它们合并到包含其余查询的字符串中，并使用EXEC ()或EXEC sp_executesql调用最终查询.您可以搜索此站点以获取有关动态数据透视的更多信息.}

I hava a problme to make a query.

This query which I created

WITH TATH(Priority, EntryDate) AS 
(
    SELECT TH.Priority as Priority, DATEADD(dd, 0, DATEDIFF(dd, 0, entryDate)) as EntryDate  
      FROM TicketAssignment TA, TicketHeader TH 
     WHERE TA.TicketID = TH.TicketID   
       AND TA.Company = 'IT'
       AND TA.CurrentRole IN ('SA1B','SA1C','SDA')
) 
SELECT Priority, convert(varchar(10), EntryDate,103) as EntryDate, COUNT(*) AS Count  
FROM TATH 
GROUP BY Priority, EntryDate 

and the result so far is:

Priority    EntryDate   Count
0   25/11/2011  1
1   25/11/2011  2
2   25/11/2011  36
3   25/11/2011  8
0   28/11/2011  3
1   28/11/2011  3
2   28/11/2011  37
3   28/11/2011  37

I want to make the result look like this

EntryDate   Priorty0    Priority1   Priority2   Priority3
25/11/2011  1   2   36  8
28/11/2011  3   3   37  37

I think it is out of my ability I look up pivot but I cannot achieve it.

If a pivot is not a good solution, what I have to search (or study)?

Could you help me Q.Q?

解决方案

Pivoting is much like grouping. You could view it as limited grouping with a ‘special effect’. The limitation consists in the fact that there can only be just one aggregated column. (In normal GROUP BY query you can have more than one, naturally.) And by the ‘special effect’ I mean, of course, that one of the other columns (and, again, only one) gets transformed into multiple columns.

Let's take your GROUP BY query as an example. You've got three columns in the output. One of them, Count, is the very column that contains aggregated information. That is the one that would be scattered among multiple columns in a PIVOT query. Another column, Priority, is one of the two other columns the results are grouped by and also the one that needs to be pivoted. Finally, EntryDate is the other GROUP BY column. It should simply stay as it is, because it does not directly take part in pivoting.

Let's see now how your main SELECT gets transformed from a usual GROUP BY query into a PIVOT query, step by step:

1. Since grouping is implied in a PIVOT query, the GROUP BY clause is removed. Instead, a PIVOT clause is introduced.

2. The Count column's expression is moved from the SELECT clause to the PIVOT clause.

3. The splitting of the Priority column is defined in the PIVOT clause.

4. The Priority and Count columns in the SELECT clause are replaced by the list of the columns defined in the PIVOT clause.

5. The EntryDate column stays unchanged in the SELECT clause.

And here's the resulting query, with comments marking every part of the transformation described above:

WITH TATH(Priority, EntryDate) AS 
(
    SELECT TH.Priority as Priority, DATEADD(dd, 0, DATEDIFF(dd, 0, entryDate)) as EntryDate
      FROM TicketAssignment TA, TicketHeader TH 
     WHERE TA.TicketID = TH.TicketID   
       AND TA.Company = 'IT'
       AND TA.CurrentRole IN ('SA1B','SA1C','SDA')
) 
SELECT
  convert(varchar(10), EntryDate,103) as EntryDate,                       -- #5
  [0] AS Priority0, [1] AS Priority1, [2] AS Priority2, [3] AS Priority3  -- #4
FROM TATH
PIVOT (                                                                   -- #1
  COUNT(*)                                                                -- #2
  FOR Priority IN ([0], [1], [2], [3])                                    -- #3
) p

/*  -- your original main query, for comparison
SELECT
  Priority,                                                               -- #4
  convert(varchar(10),                                                    -- #5
  EntryDate,103) as EntryDate, COUNT(*) AS Count                          -- ##2&4
FROM TATH 
GROUP BY Priority, EntryDate                                              -- #1
*/

There's one additional note on the column list in the PIVOT clause. First of all, you must understand that the resulting set of a SQL query is supposed to be fixed^* in terms of the number of columns and their names. That means you must explicitly enumerate all the transformed columns you want to see in the output. The names are derived from the values of the column being pivoted but they should be specified as names, not as values. That is why you can see square brackets around the listed numbers. Since numbers themselves do not satisfy the rules for regular identifiers, they must be delimited.

You can also see that you can alias pivoted columns in the SELECT clause just like any other column or expression. So, in the end, you don't have to end up with the meaningless 0, 1 etc. identifiers and instead you can assign those columns any names you like.

---

_{^*If you want the number and/or names of the pivoted columns to be dynamic, you'll have to build the query dynamically, i.e. collect the names first, then incorporate them into a string containing the rest of the query and invoke the final query with EXEC () or EXEC sp_executesql. You can search this site for more information on dynamic pivoting.}

这篇关于SQL Server查询:行使列(枢轴?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！