Union, Join, Or GroupBy

Question

I have procedures pulling data from various sources to give me 3 VERY similar tables.

Metric          | Tickets   |Band
______________________________________
Acknowledgement | 45        | New
Acknowledgement | 23        | Within
Acknowledgement | 16        | Near
Acknowledgement | 2         | Very Near

And

Metric     | Tickets   |Band
___________________________________
Escalation | 10        | New
Escalation | 43        | Within
Escalation | 81        | Near
Escalation | 6         | Very Near

And

Metric| Tickets   |Band
___________________________________
Fixed | 34        | New
Fixed | 52        | Within
Fixed | 36        | Near
Fixed | 4         | Very Near

Now, I would like to combine them together in some way to have one table output like this

Metric          | New   | Within | Near | Very Near
_____________________________________________________
Acknowledgement | 45    | 23     | 16   | 2
Escalation      | 10    | 43     | 81   | 6
Fixed           | 34    | 52     | 36   | 4

How can I achieve this in MS SQLServer, please ?

Why don't you just have one table with all 12 of the entries in it? — Codeman, Aug 28 '12 at 23:40
This question and answers will give you lots of info - does is help? http://stackoverflow.com/questions/24470/sql-server-pivot-examples — Preet Sangha, Aug 28 '12 at 23:43
@Pheonixblade9 - these sorts of questions are generally pointless... Usually by the time a person is asking this sort of question, the schema is already defined and there is little that can be done to change it. — Stefan H, Aug 28 '12 at 23:44
@StefanH I should reiterate my question then: why doesn't OP just have a view with all 12 entries in it? It would greatly simplify the problem. — Codeman, Aug 28 '12 at 23:46
@Pheonixblade9 So, that view would need to contain a query that did what? Oh yeah, the query in that view would need to do what the OP is asking for in this question... — Stefan H, Aug 28 '12 at 23:47
@StefanH A view with all of the records together combined with the answer PreetSangha linked would solve his problem. — Codeman, Aug 28 '12 at 23:51

score 5 · Answer 1 · answered Aug 29 '12 at 00:00

This doesn't require repeating all of the aggregates and CASE expressions for each table - just a simple union will do, then you can pivot off of that.

SELECT Metric, [New], [Within], [Near], [Very Near] FROM 
(
            SELECT Metric, Tickets, Band FROM dbo.table_a
  UNION ALL SELECT Metric, Tickets, Band FROM dbo.table_b
  UNION ALL SELECT Metric, Tickets, Band FROM dbo.table_c
) 
AS x PIVOT 
(
  MAX(Tickets) FOR Band IN ([New],[Within],[Near],[Very Near])
) AS p;

Yes this definitely is a better question. – Taryn Aug 29 '12 at 00:13 — Taryn, Aug 29 '12 at 00:13

score 4 · Accepted Answer · answered Aug 28 '12 at 23:55

4

This is a fake pivot that should get what you want. You would then union this query for each table.

SELECT 
    Metric,
    MAX( CASE Band WHEN 'New' THEN Tickets ELSE '' END ) New, 
    MAX( CASE Band WHEN 'Within' THEN Tickets ELSE '' END ) Within, 
    MAX( CASE Band WHEN 'Near' THEN Tickets ELSE '' END ) Near, 
    MAX( CASE Band WHEN 'Very Near' THEN Tickets ELSE '' END ) [Very Near]
FROM 
    table
GROUP BY 
    Metric

UNION
...

answered Aug 28 '12 at 23:55

Stefan H

6,635
4
24
35

+1 but what's that `Tickets data` in the last aggregate? Would also work with `SUM()` aggregates as in `SUM(CASE Band WHEN 'New' THEN Tickets ELSE 0 END) New` – Michael Berkowski Aug 28 '12 at 23:57

Union, Join, Or GroupBy

2 Answers2