将行值作为列返回的 SQL 查询 - SQL Server 2008

Question

我在弄清楚如何返回查询结果时遇到问题，其中我有要转换为列的行值。

简而言之，这是我当前在 SQL Server 2008 中的架构示例:

下面是我希望查询结果的示例:

这是可以玩的 SQLFiddle.com - http://sqlfiddle.com/#!6/6b394/1/0

关于架构的一些有用说明:

• 表格每天总是包含两行
• 一排苹果，一排橙子
• 我正在尝试将每对两行合并为一行
• 为此，我需要将行值转换为它们自己的列，但只有 [NumOffered]、[NumTaken]、[NumAbandoned]、[NumSpoiled] 的值 - 不需要这两行中每一行的每一列被复制，正如您从示例中看到的那样

如您所见，从示例所需的最终结果图像中，两行合并在一起，您可以看到每个值都有自己的列和相关名称。

我见过几个例子说明这是如何实现的，但并不完全适用于我的目的。我看过使用 Grouping 和 Case 方法的示例。我见过 PIVOT 的许多用途，甚至在 SQL 中创建过一些自定义函数。我不确定哪种方法最适合我。我可以对此有所了解吗？

Answer 1

您可以通过多种不同的方式获得结果。多个 JOIN、unpivot/pivot 或带聚合的 CASE。

它们各有利弊，因此您需要决定哪种方式最适合您的情况。

多重连接 - 现在您已经声明每天总是有 2 行 - 一行用于苹果和橙子。多次加入表格时，您需要某种列来加入。看起来该列是 timestamp 但如果您有一天只得到一行，会发生什么情况。然后 INNER JOIN @Becuzz won't work提供的解决方案因为它每天只会返回包含两个条目的行。 LeYou 可以通过 FULL JOIN 使用多个 JOIN，即使每天只有一个条目，它也会返回数据:

select 
  [Timestamp] = Coalesce(a.Timestamp, o.Timestamp),
  ApplesNumOffered = a.[NumOffered],
  ApplesNumTaken = a.[NumTaken],
  ApplesNumAbandoned = a.[NumAbandoned],
  ApplesNumSpoiled = a.[NumSpoiled],
  OrangesNumOffered = o.[NumOffered],
  OrangesNumTaken = o.[NumTaken],
  OrangesNumAbandoned = o.[NumAbandoned],
  OrangesNumSpoiled = o.[NumSpoiled]
from
(
  select timestamp, numoffered, NumTaken, numabandoned, numspoiled
  from myTable
  where FruitType = 'Apple'
) a
full join
(
  select timestamp, numoffered, NumTaken, numabandoned, numspoiled
  from myTable
  where FruitType = 'Orange'
) o 
  on a.Timestamp = o.Timestamp
order by [timestamp];

参见 SQL Fiddle with Demo .多个连接的另一个问题是，如果您有 2 个以上的值，您将需要为每个值进行额外的连接。

如果您的值数量有限，那么我建议使用聚合函数和 CASE 表达式来获取结果:

SELECT 
  [timestamp],
  sum(case when FruitType = 'Apple' then NumOffered else 0 end) AppleNumOffered,
  sum(case when FruitType = 'Apple' then NumTaken else 0 end) AppleNumTaken,
  sum(case when FruitType = 'Apple' then NumAbandoned else 0 end) AppleNumAbandoned,
  sum(case when FruitType = 'Apple' then NumSpoiled else 0 end) AppleNumSpoiled,
  sum(case when FruitType = 'Orange' then NumOffered else 0 end) OrangeNumOffered,
  sum(case when FruitType = 'Orange' then NumTaken else 0 end) OrangeNumTaken,
  sum(case when FruitType = 'Orange' then NumAbandoned else 0 end) OrangeNumAbandoned,
  sum(case when FruitType = 'Orange' then NumSpoiled else 0 end) OrangeNumSpoiled
FROM myTable
group by [timestamp];

参见 SQL Fiddle with Demo .甚至使用 PIVOT/UNPIVOT就像@M.Ali 一样。这些问题是如果您有未知值怎么办 - 意味着不仅仅是 Apple 和 Orange。您只能使用动态 SQL 来获取结果。动态SQL会创建一串需要引擎执行的sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(FruitType + col) 
                    from
                    (
                      select FruitType
                      from myTable
                    ) d
                    cross apply
                    (
                      select 'NumOffered', 0 union all
                      select 'NumTaken', 1 union all
                      select 'NumAbandoned', 2 union all
                      select 'NumSpoiled', 3
                    ) c (col, so)
                    group by FruitType, Col, so
                    order by FruitType, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT TimeStamp,' + @cols + ' 
            from 
            (
               select TimeStamp,
                new_col = FruitType+col, value
               from myTable
               cross apply
               (
                 select ''NumOffered'', NumOffered union all
                 select ''NumTaken'', NumOffered union all
                 select ''NumAbandoned'', NumOffered union all
                 select ''NumSpoiled'', NumOffered
               ) c (col, value)
            ) x
            pivot 
            (
                sum(value)
                for new_col in (' + @cols + ')
            ) p '

exec sp_executesql @query;

参见 SQL Fiddle with Demo

所有版本给出结果:

|                 timestamp | AppleNumOffered | AppleNumTaken | AppleNumAbandoned | AppleNumSpoiled | OrangeNumOffered | OrangeNumTaken | OrangeNumAbandoned | OrangeNumSpoiled |
|---------------------------|-----------------|---------------|-------------------|-----------------|------------------|----------------|--------------------|------------------|
| January, 01 2015 00:00:00 |              55 |            12 |                 0 |               0 |               12 |              5 |                  0 |                1 |
| January, 02 2015 00:00:00 |              21 |             6 |                 2 |               1 |               60 |             43 |                  0 |                0 |
| January, 03 2015 00:00:00 |              49 |            17 |                 2 |               1 |              109 |             87 |                 12 |                1 |
| January, 04 2015 00:00:00 |               6 |             4 |                 0 |               0 |               53 |             40 |                  0 |                1 |
| January, 05 2015 00:00:00 |              32 |            14 |                 1 |               0 |               41 |             21 |                  5 |                0 |
| January, 06 2015 00:00:00 |              26 |            24 |                 0 |               1 |               97 |             30 |                 10 |                1 |
| January, 07 2015 00:00:00 |              17 |             9 |                 2 |               0 |               37 |             27 |                  0 |                4 |
| January, 08 2015 00:00:00 |              83 |            80 |                 3 |               0 |              117 |            100 |                  5 |                1 |