Home:ALL Converter>pivot table with text identifier and number value

pivot table with text identifier and number value

Ask Time:2018-06-08T22:08:48         Author:Roberto Correia

Json Formatter

How to pivot a table with a text identifier and number value?

This is my table (services):

Street   | Service       | Total
---------|---------------|------
Street A | Service AA 01 | 20
Street A | Service AB 01 | 10
Street A | Service AB 01 | 15
Street A | Service AC 01 | 05
Street B | Service AA 01 | 10
Street B | Service AA 01 | 03
Street B | Service AB 01 | 05
Street B | Service AC 01 | 03

This is the result that I wanna:

Street   | Service AA 01 | Service AB 01 | Service AC 01
---------|---------------|---------------|--------------
Street A |            20 |            25 |            05
Street B |            13 |            05 |            03

What I tried so far:

SELECT Street, ['SERVICE AA 01'], ['SERVICE AB 01'], ['SERVICE AC 01']
FROM services PIVOT (
  SUM(Total) FOR Service IN (['SERVICE AA 01'], ['SERVICE AB 01'], ['SERVICE AC 01'])) AS D

Got the streets, all columns, but all values is always null;

Author:Roberto Correia,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/50762501/pivot-table-with-text-identifier-and-number-value
Tab Alleman :

In every PIVOT example I've ever seen, you have to start by selecting from a Derived table, not directly from the table.\n\nOh, and also according to this answer, you don't put the single-quotes in your column names.\n\nSo apparently you have to do something like this:\n\nSELECT Street, [SERVICE AA 01], [SERVICE AB 01], [SERVICE AC 01]\nFROM (SELECT Street, Service, Total FROM services) AS S\nPIVOT (\n SUM(Total) FOR Service IN ([SERVICE AA 01], [SERVICE AB 01], [SERVICE AC 01])) AS D\n",
2018-06-08T14:19:16
yy