|
Как собрать таблицу из названий колонок и их значений?
#39898252
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Москва
Сообщения: 18 719
|
|
Стимуляции воображения ради:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
with t(Column1,Column2,Column3) as (
select 'Value 1_1', 'Value 2_1', 'Value3_1'
from dual union all select 'Value 1_2', 'Value 2_2', 'Value3_2'
from dual union all select 'Value 1_3', 'Value 2_3', 'Value3_3'
from dual )
select decode(d.r, 1, 'Column1', 2, 'Column2', 3, 'Column3') Column1NTable
, decode(d.r, 1, Column1 , 2, Column2 , 3, Column3 ) Column2NTable
from t, (select rownum r from dual connect by level <=3) d
;
with t as (select 'Value 1_1' Column1, 'Value 2_1' Column2, 'Value3_1' Column3
from dual union all select 'Value 1_2', 'Value 2_2', 'Value3_2'
from dual union all select 'Value 1_3', 'Value 2_3', 'Value3_3'
from dual )
select decode(grouping_id(column1, column2,column3)
, 3, 'Column1'
, 5, 'Column2'
, 6, 'Column3'
) Column1NTable
, coalesce(column1, column2,column3) Column2NTable
from t
group by grouping sets(Column1, Column2, Column3)
;
with t as (select 'Value 1_1' Column1, 'Value 2_1' Column2, 'Value3_1' Column3
from dual union all select 'Value 1_2', 'Value 2_2', 'Value3_2'
from dual union all select 'Value 1_3', 'Value 2_3', 'Value3_3'
from dual )
select decode(grouping_id(column1, column2,column3)
, 3, 'Column1'
, 5, 'Column2'
, 6, 'Column3'
) Column1NTable
, coalesce(column1, column2,column3) Column2NTable
from t
group by cube(Column1, Column2, Column3)
having grouping_id(column1, column2,column3) in (3,5,6)
;
with t as (select 'Value 1_1' Column1, 'Value 2_1' Column2, 'Value3_1' Column3
from dual union all select 'Value 1_2', 'Value 2_2', 'Value3_2'
from dual union all select 'Value 1_3', 'Value 2_3', 'Value3_3'
from dual )
select column1 Column1NTable, column2 Column2NTable
from t
model
partition by (column1 c1, column2 c2, column3 c3)
dimension by (1 n)
measures(column1, column2, column3)
rules( column2[3] = column3[1], column1[3] = 'Column3'
, column2[2] = column2[1], column1[2] = 'Column2'
, column2[1] = column1[1], column1[1] = 'Column1'
)
;
|
|
|
