CODEWITHSUNDEEP
pythonsqlsqlite
codeKiller
codeKiller

Reputation: 5739

SQL modify order of columns and add new columns from query

I would like to know how to do the following operation, if possible:

Sample of the original table:

Type      Status    I1    I2    I3    I4    I5
 A          ON      3     4     2     10    2
 A         OFF      1     1     0     null  null
 B          ON      5     6     2     10    2
 B         OFF      2     1     2     null  null
 C          ON      8     7     5     10    2
 C         OFF      1     1     1     null  null
 A          ON      4     4     4     10    2
 A         OFF      2     2     1     null  null
 B          ON      5     4     5     10    2
 B         OFF      1     1     1     null  null

So, basically what I would like is to merge the I values from ON and OFF status, in the following way:

Taking the table above, the values I1,I2, I3 from OFF status will become I6, I7,I8 output would be

Type      I1    I2    I3    I4    I5    I6    I7   I8
 A        3     4     2     10    2     1     1    0
 B        5     6     2     10    2     2     1    2
 C        8     7     5     10    2     1     1    1
 A        4     4     4     10    2     2     2    1
 B        5     4     5     10    2     1     1    1

To be clear, what It would do is just to take the values I1, I2 and I3 from the OFF status of each type and put them together with the results from the ON status as I6, I7, I8

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

If you id column is auto-incremented and it has no gaps, then you might be in luck

select o1.*, o2.i1 as i6, o2.i2 as i7, o2.i3 as i8
from original o1 join
     original o2
     on o2.id = o1.id + 1 and
        o2.type = o1.type and
        o1.status = 'ON' and o2.status = 'OFF';

Otherwise, this query is much harder than it seems. You need to group the similar ids. One method is to identify them by the number of similar rows with the same type and ON. You can get this with a correlated subquery:

with o as (
      select o.*,
             (select count(*)
              from original o2
              where o2.type = o.type and o2.status = 'ON' and o2.id <= o.id
             ) as grp
      from original o
     )
select o.type,
       max(case when type = 'ON' then i1 end) as i1,
       max(case when type = 'ON' then i2 end) as i2,
       max(case when type = 'ON' then i3 end) as i3,
       max(case when type = 'ON' then i4 end) as i4,
       max(case when type = 'ON' then i5 end) as i5,
       max(case when type = 'OFF' then i1 end) as i6,
       max(case when type = 'OFF' then i2 end) as i7,
       max(case when type = 'OFF' then i3 end) as i8
from o
group by grp, type ;

Upvotes: 2

shA.t
shA.t

Reputation: 16958

Try this:

SELECT Type, SUM(I1) AS I1, SUM(I2) AS I2, SUM(I3) AS I3, SUM(I4) AS I4, SUM(I5) AS I5, SUM(I6) AS I6, SUM(I7) AS I7, SUM(I8) AS I8
FROM (
    SELECT Type, I1, I2, I3, I4, I5, 0 As I6, 0 As I7, 0 As I8
    FROM YourTable
    WHERE Status = 'ON'
    UNION ALL
    SELECT Type, 0 , 0, 0, 0, 0, I1 As I6, I2 As I7, I3 As I8
    FROM YourTable
    WHERE Status = 'OFF') DT
GROUP BY Type

Upvotes: 0

Related Questions