将行转入列 Firebird 2.5 [英] Pivot rows into columns Firebird 2.5

问题描述

The 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 instruction must 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 description from table1 when id exists in table2 or the column "alternate-value" when table1Id is null.

Is it possible? Or do I need construct the query dynamically?

解决方案

Well, yes, it is possible (if done in two steps), but it is a bit complex so I'm not certain whether you should do it. First, you could execute the following select:

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 would have to run the result. Note that I used MyValue rather than Value and that the columns may not appear in the order you desire (although that could also be possible).

Pivottables are not something that easily converts to SQL in Firebird and I generally prefer to create Pivot tables in Excel rather than Firebird, but as you can see it is possible.

这篇关于将行转入列 Firebird 2.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！