pivot table with text identifier and number value

Question

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;

score 2 · Accepted Answer · answered Jun 08 '18 at 14:19

2

In every PIVOT example I've ever seen, you have to start by selecting from a Derived table, not directly from the table.

Oh, and also according to this answer, you don't put the single-quotes in your column names.

So apparently you have to do something like this:

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

answered Jun 08 '18 at 14:19

Tab Alleman

31,483
7
36
52

You are 100% correct regarding the single-quotes... but the Derived Table thing? I'll stipulate more often than not, but HAVE TO is just too strong. – John Cappelletti Jun 08 '18 at 14:31
@JohnCappelletti I felt a little unsure about putting it in such strong terms, but I couldn't find an example that didn't use a derived table. Any idea why, and under what circumstances you wouldn't have to use one? – Tab Alleman Jun 08 '18 at 14:34
1

@TabAlleman . . . You wouldn't use one when your table has exactly the columns you need, no more, no less. – Gordon Linoff Jun 08 '18 at 14:50
1

Provided all the columns in the table will be consumed by the pivot, otherwise you have to limit the columns "feeding" the pivot. Not all pivots are aggregations – John Cappelletti Jun 08 '18 at 14:50

score 1 · Answer 2 · answered Jun 08 '18 at 14:54

Your problem is that [SERVICE AA 01] and ['SERVICE AA 01'] do not refer to the same column names. The second has single quotes in the column name -- a really bad practice and not at issue.

I will make an editorial comment that if you had named the columns with underscores rather than spaces (SERVICE_AA_01), then you would not need to escape them. And you probably would not quote them either. And you would not have had this problem. Just saying. Good naming conventions can prevent problems and confusion.

I would also use conditional aggregation:

select street,
       sum(case when Service = 'SERVICE AA 01' then total end) as [SERVICE AA 01],  
       sum(case when Service = 'SERVICE AB 01' then total end) as [SERVICE AB 01], 
       sum(case when Service = 'SERVICE AC 01' then total end) as [SERVICE AC 01] from services
group by street;

I don't find the pivot syntax to be particularly powerful or succinct. As Tab points out, a subquery is usually used because extraneous columns do nefarious things in a pivot. That is not the issue in this case, but it frequently occurs.

tks. I prefer the conditional aggregation, but, in this example, the conditional aggregation has a "clear" syntax, but in my real problem, my table has more than 50 different services.. so, the syntax will be huge (this is for a printed report) — Roberto Correia, Jun 08 '18 at 14:57
@RobertoCorreia . . . Actually, I often construct the logic in a spreadsheet, so the number of columns doesn't matter. I would do the same using `pivot`. — Gordon Linoff, Jun 08 '18 at 15:01

pivot table with text identifier and number value

2 Answers2