pivot or transpose

drumcircle
Rising Star

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?

1 ACCEPTED SOLUTION

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

View solution in original post

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

View solution in original post

drumcircle
Rising Star

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