T-SQL PIVOT 3 Columns

Question

I am trying to PIVOT this data:

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');

INTO this...

+-------------+-------------+--------+-------------+-------------+-------------+------+-------------+-------------+-------------+----------+---------+-----------+
| 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 |
+-------------+-------------+--------+-------------+-------------+-------------+------+-------------+-------------+-------------+----------+---------+-----------+

Having some trouble and please excuse the month ordering I will sort that out!

So what have you tried? Why isn't it working? You know you need to use `PIVOT`, so what's wrong with it? — Thom A, Jun 11 '21 at 08:11
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Thom A, Jun 11 '21 at 08:13
Does this answer your question? [SQL Server - Pivot Convert rows to columns (with additional row data)](https://stackoverflow.com/questions/62366368/sql-server-pivot-convert-rows-to-columns-with-additional-row-data) — Thom A, Jun 11 '21 at 08:16
@Larnu I can PIVOT either the "Remaining" OR "Received to the months as columns, but I can't figure out how to change both remaining and received to rows. — bellmont, Jun 11 '21 at 08:26
Did you have a look at the linked duplicates (for example the second). — Thom A, Jun 11 '21 at 08:31
@Larnu, so I managed to do it by the MAX(CASE WHEN, I did a SELECT MAX.. for all the Remaining values and then using UNION ALL did it again for the received! A bit of a long winded way but it got the results! Thanks — bellmont, Jun 11 '21 at 08:58
No need for a `UNION ALL`, just use a `GROUP BY` as shown in the other [answer](https://stackoverflow.com/a/62366420/2029983) — Thom A, Jun 11 '21 at 09:01

Yair Maron · Answer 1 · 2021-06-11T11:02:19.677

Your Primary Key is incorrect, as I see you are trying to insert duplicate keys, and also the Remaining Column should be numeric, as you want to sum it.

It can be fixed like this:

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
);

And then, as for your question, you can Pivot the result this way:

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

If you want a bit more sophisticated and elegant solution, use this instead:

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

T-SQL PIVOT 3 Columns

1 Answers1