CODEWITHSUNDEEP
sqlsqlitepivotgrouping
user3246693
user3246693

Reputation: 783

Sqlite - group_concat into columns?

Is it possible to do something similar to group_concat, only instead of combining the values into 1 comma separated string, extend them out into multiple columns in a table? Initially I planned to group_concat the values, and then do a split on the string to move the values into the columns I need, however there is apparently no split option in SQLite, and I am limited to doing this directly in SQLite(no other DB's or scripting options are available).

To generate the report I need to make, I have the following table.

Item | Owner | System 
----------------------
A    | Bob   | Sys1
B    | Bob   | Sys1
B    | Sue   | Sys1
B    | Bob   | Sys2
C    | Bob   | Sys3
C    | Sue   | Sys3
C    | Jane  | Sys3

I need to concat/pivot this out into this format dynamically.

Item | Owner 1 | Owner 2 | Owner 3 | Owner 4 | Owner 5 | System
----------------------------------------------------------------
A    | Bob     |         |         |         |         | Sys1
B    | Bob     | Sue     |         |         |         | Sys1
B    | Bob     |         |         |         |         | Sys2
C    | Bob     | Sue     | Jane    |         |         | Sys3

As far as the Owner columns are concerned, there technically shouldn't ever be more than 5 owners max per item, and even if there are more than 5, I don't care about displaying the additional owners as long as at least 5 show up(also wouldn't matter which 5 in this scenerio).

There can be an unlimited number of "Items" in the table and there are about 20 systems(though the system count will change)

Is this possible using only SQlite?

Upvotes: 1

Views: 904

Answers (1)

radar
radar

Reputation: 13425

you need to have auto increment id in your table to achieve this

SQLLite doesn't have row_number analytic function, it doesn't have pivot keyword

Here we are generating row_number using a correlated sub query and based on this selection 5 owners for each Item, System.

select Item, System,
       max(case when rn = 1 then Owner end) as Owner1,
       max(case when rn = 2 then Owner end) as Owner2,
       max(case when rn = 3 then Owner end) as Owner3,
       max(case when rn = 4 then Owner end) as Owner4,
       max(case when rn = 5 then Owner end) as Owner5
from (
    select Item, System, Owner, (
        select count(*) 
        from Table1 b  
        where a.Item = b.Item and a.System = b.System
        and a.id >= b.id) as rn
    from Table1 a) T
group by Item, System

Upvotes: 1

Related Questions