使用sql在每行中展开具有多个键和值的表
问题描述:
如何将每行具有多个键和值的表展开"到每行具有一个键和值的表?
How can I "unroll" a table with multiple keys and values in each row to a table with one key and value in each row?
例如,我有一张桌子
key1 value1 key2 value2
a 1 b 2
c 3 d 4
e 5 f 6
,我想将其更改为
key value
a 1
b 2
c 3
d 4
e 5
f 6
上面每行只有两个键和值,但是我需要一般情况下的解决方案,在这种情况下,每行最多可能有7对.
There are only two keys and values in each row above but I need the solution for general case, where I may have up to 7 pairs in each row.
答
只使用union/union all吗?
just use union / union all ?
假设key1和key2与value1和value2具有相同的类型
Assuming key1 and key2 are of same type, as value1 and value2
select key1, value1
from table
union all -- or just union to merge duplicates
select key2, value2
from table