Rotating rows to columns Firebird 2.5
Sequence:
table1 ===== id - Description ---------------- | 1 | Proj-x | 2 | Settlers | 3 | Bank | 4 | Newiest table2 ===== id table1Id value alternate-value --------------------------------- | 1 | 1 | 12 | null | 1 | 4 | 6 | null | 1 | null | 22 | Desktop | 2 | 2 | 7 | null | 2 | 3 | 11 | null | 2 | null | 2 | Camby Jere | 3 | 1 | 8 | null | 3 | 4 | 6 | null | 3 | null | 7 | Camby Jere
The select command should return
| table1.id | Proj-x | Settlers | Bank | Newiest | Desktop | Camby Jere -------------------------------------------------- -------- | 1 | 12 | null | null | null | null | null | 1 | null | null | 6 | null | null | null | 1 | null | null | null | null | 22 | null | 2 | null | 7 | null | null | null | null | 2 | null | null | 11 | null | null | null | 2 | null | null | null | null | null | 2 | 3 | 8 | null | null | null | null | null | 3 | null | null | null | 6 | null | null | 3 | null | null | null | null | null | 7
The columns are descriptions from table1
when id exists in, table2
or in the "alternate-value" column when table1Id
null.
Is it possible? Or do I need to build the query dynamically?
source to share
Okay, yes, it is possible (if done in two steps), but it's a bit tricky, so I'm not sure if you should be doing this. First, you can make the following selection:
with tmp1(MyFieldName) as
(select distinct coalesce(t2.alternate_value, t1.Description)
from table2 t2
left join table1 t1 on t2.Table1ID = t1.id),
tmp2(MyPivotSource) as
(select 'iif(coalesce(t2.alternate_value, t1.Description) = '''||MyFieldName||''', t2.MyValue, 0) as "'||MyFieldName||'"'
from tmp1)
select 'select t2.id as "table1.id", '||list(MyPivotSource)||'from table2 t2
left join table1 t1 on t2.Table1ID = t1.id'
from rdb$database
cross join tmp2
And then you will need to run the result. Note that I used MyValue, not Value, and that the columns may not appear in the order I want (although this is also possible).
Pivottables is not something that easily converts to SQL in Firebird, and I usually prefer to create pivot tables in Excel over Firebird, but as you can see it is possible.
source to share