T-SQL数据透视3列

Question

我正在尝试将这些数据转换为：

CREATE TABLE mytable(
   _Received   INTEGER  NOT NULL PRIMARY KEY 
  ,_Remaining  VARCHAR(16) NOT NULL
  ,MonthName_s VARCHAR(9) NOT NULL
);
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (226,'2260.71','April');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'2800','August');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'685.71','December');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'685.76','February');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'685.71','January');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'2800','July');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (150,'34','June');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'685.71','March');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (300,'3010.71','May');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'400','November');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'2800','October');
INSERT INTO mytable(_Received,_Remaining,MonthName_s) VALUES (0,'2800','September');

进入这个..。

+-------------+-------------+--------+-------------+-------------+-------------+------+-------------+-------------+-------------+----------+---------+-----------+
| MonthName_s |    April    | August |  December   |  February   |   January   | July |    June     |    March    |     May     | November | October | September |
+-------------+-------------+--------+-------------+-------------+-------------+------+-------------+-------------+-------------+----------+---------+-----------+
| _Received   |         226 |      0 |           0 |           0 |           0 |    0 |         150 |           0 |         300 |        0 |       0 |         0 |
| _Remaining  | 2260.714286 |   2800 | 685.7142857 | 685.7142857 | 685.7142857 | 2800 | 3485.714286 | 685.7142857 | 3010.714286 |      400 |    2800 |      2800 |
+-------------+-------------+--------+-------------+-------------+-------------+------+-------------+-------------+-------------+----------+---------+-----------+

有一些麻烦，请原谅这个月的订单，我会解决的！

Answer 1

您的主键不正确，因为我看到您正在尝试插入重复的键，并且剩余的列应该是数字，因为您希望对其求和。

它可以像这样修复：

CREATE TABLE myTable(
    Id              INTEGER         NOT NULL    PRIMARY KEY IDENTITY(1,1),
    Received        INTEGER         NOT NULL,
    Remaining       DECIMAL(6,2)    NOT NULL,
    MonthName_s     VARCHAR(9)      NOT NULL
);

然后，对于你的问题，你可以这样透视结果：

SELECT 'Received' AS ActionName, [January], [February], [March], [April], [May], [June], [July] --...
FROM
(
  SELECT MonthName_s, Received FROM myTable
) T
PIVOT
(
  SUM(Received)
  FOR MonthName_s IN ([January], [February], [March], [April], [May], [June], [July]) --...
) p

UNION ALL

SELECT 'Remaining' AS ActionName, [January], [February], [March], [April], [May], [June], [July] --...
FROM
(
  SELECT MonthName_s, Remaining FROM myTable
) T
PIVOT
(
  SUM(Remaining)
  FOR MonthName_s IN ([January], [February], [March], [April], [May], [June], [July]) --...
) p

如果您想要更复杂、更优雅的解决方案，请使用以下代码：

SELECT ActionName, [January], [February], [March], [April], [May], [June], [July] --...
FROM
(
    SELECT      ActionName,MonthName_s,
                SUM(CASE ActionName WHEN 'Received' THEN T.Received ELSE T.Remaining END) AS Amount
    FROM        myTable                             AS  T
    CROSS JOIN  (VALUES ('Received'), ('Remaining'))    AS  Actions(ActionName)
    GROUP BY    MonthName_s, ActionName
) T
PIVOT
(
  SUM(Amount)
  FOR MonthName_s IN ([January], [February], [March], [April], [May], [June], [July]) --...
) p