将列转换为一行

Question

我想将 SQL 数据从行转换为列

这是我的数据

-------------------------------
|ITEM |    DAY  |  No  | Qty  |
-------------------------------
|  A  |Monday   | No 1 | 10   |
|  A  |Tuesday  | No 1 | 10   |
|  A  |Tuesday  | No 2 | 5    |
|  A  |Wednesday| NULL | NULL |
-------------------------------

我只想转换为一行并获得此结果

---------------------------------------------------------------------------
|ITEM | Monday1|  Monday2 | Tuesday1 | Tuesday2 | Wednesday1 | Wednesday 2|
---------------------------------------------------------------------------
|  A  |   10   |  NULL    |  10      |     5    |   NULL     |   NULL     | 

我如何构建结果？ 我尝试使用数据透视表但仍然没有得到结果

谢谢

Answer 1

你可以旋转它们

SELECT *
FROM (
  SELECT 
    ITEM
  , CONCAT([DAY], RIGHT([No],1)) AS Col
  , Qty
  FROM YourTable
) Src
PIVOT (
  MAX(Qty) 
  FOR Col IN (
    [Monday1], [Monday2], 
    [Tuesday1], [Tuesday2],
    [Wednesday1], [Wednesday2]
  ) 
) Pvt
ORDER BY ITEM;