CODEWITHSUNDEEP
sqljoinpivotpervasive
Waxi
Waxi

Reputation: 1651

SQL pivot/join table?

This is my actual table...

ID        SEQ         HOURS      ROUTER

1         1000        13         Old
1         2000        23         Old
1         3000        17         Old

2         1111        43         New
2         2222        40         New

3         1111        16         New
3         2222        18         New

4         1000        19         Old
4         2000        31         Old
4         3000        10         Old

This is my desired table...

ID        ALPHA       BRAVO

1         36          17    
2         43          40
3         16          18
4         50          10

There needs to be some logic in here where I can say things like:

Old Router's 1000 + 2000 = Alpha
New Router's 1111 = Alpha

Old Router's 3000 = Bravo
New Router's 2222 = Bravo

I only have two types of routers, but their seq numbers are supposed to mean the same thing which is why I'm trying to combine the results. The only approach I know of is taking 2 separate queries that each target a router, but I still need to combine and pivot them in the end somehow.

I'm not sure if what I'm looking to do is best done outside the database with another language, but was hoping to do it within sql so by the time I got the data back I could just use it. Any help would be appreciated.

Upvotes: 1

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Are you looking for conditional aggregation?

select id,
       sum(case when router = 'old' and seq in (1000, 2000) then hours
                when router = 'new' and seq = 1111 then hours
                else 0
           end) as alpha,
       sum(case when router = 'old' and seq in (3000) then hours
                when router = 'new' and seq = 2222 then hours
                else 0
           end) as bravo
from t
group by id;

Upvotes: 3

Related Questions