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

问题描述

顺序:

   
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 

select指令必须返回

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

当id在table2中存在时，这些列是对table1的描述；当table1Id为空时，这些列是替代值"列.

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

然后您必须运行结果.请注意，我使用MyValue而不是Value，并且列可能不会按照您想要的顺序显示(尽管也可以).

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).

数据透视表在Firebird中不是很容易转换为SQL的东西，我通常更喜欢在Excel中而不是Firebird中创建数据透视表，但是如您所见，这是可能的.

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屋！