pivot or transpose

drumcircle
Single Node Master

What is the suggested approach for pivoting key-value pair rows to data to named columns in Exasol?

T_UNPIVOTED_PROPERTIES

id VARCHAR(20)

key VARCHAR(20)

val VARCHAR(100)

example data:

name, anthony

hobby, audio

name, tom

hobby, soccer

 


T_PIVOTED_TABLE

name_col (VARCHAR 100)

hobby_col (VARCHAR 100)

example data:

anthony, audio

tom, soccer

Write a UDF?

4 REPLIES 4

exa-Aleksandr
Team Exasol
Team Exasol

Hi @drumcircle , hi @mwellbro ,

If we aren't restricted with a fully dynamic (in terms of columns) solution, then may be just ANSI variant:

with
base(rid,key,val) as (
values(1,'name','anthony')
,(1,'hobby','audio')
,(2,'name','tom')
,(2,'hobby','soccer')
)
SELECT
b.rid
, max(case when b.key = 'hobby' then b.val end) as hobby_col
, max(case when b.key = 'name' then b.val end) as name_col
FROM
base b
group by
b.rid
;

?

mwellbro
Xpert

Hi @drumcircle ,

maybe more on the "esoteric" side, but maybe you could ( I don´t say you should 😉  ) do it like this:

with base(rid,key,val) as (
values(1,'name','anthony'),(1,'hobby','audio'),(2,'name','tom'),(2,'hobby','soccer')
)
,j_layer as (
select '{' || group_concat('"'||key||'"'||' : '||'"'||val||'"' order by val) ||'}' as json from base group by rid
)
select
JSON_EXTRACT (json,
'$.name',
'$.hobby')
EMITS(
name VARCHAR(100),
hobby VARCHAR(100))
from j_layer
;

 Although this has some "side-requirements" like exa-version >= 6.2.7 + command-line parameter '-enableJsonFunctions' has to be set AND probably more important in your
case I smuggled a row-id into your key/value pairs which you might not have.

Also there is the "natural limit" of a single group_concat-value to be taken into consideration - but maybe it´ll come in handy at some point.

Cheers,
Malte

drumcircle
Single Node Master

Amazing! Yep... JSON does support this xform in straight SQL, forgot about that!

mwellbro
Xpert

Hi drumcircle,

I think "Write a UDF" about sums it up , although I was surprised that "exasol github pivot" didn´t turn up anything 😄

But maybe there´s someone here who could pitch in ?

Cheers,
Malte