How to transpose/pivot rows as columns in teiid ?

onkar.dhuri Jul 15, 2016 5:47 AM

I have a jdbc source connection in my vdb and I want to pivot it on a column as shown here.

The underlying source doesn't have pivot capabilities. Any idea can we do it in teiid ?

Thanks,
Onkar

1. Re: How to transpose/pivot rows as columns in teiid ?

onkar.dhuri Jul 17, 2016 7:42 AM (in response to onkar.dhuri)

rareddy, shawkins Could you please shed some lights on this ?
Actions
2. Re: How to transpose/pivot rows as columns in teiid ?

shawkins Jul 17, 2016 11:53 AM (in response to onkar.dhuri)

There is not a built in PIVOT operation in Teiid. You would use aggregation/grouping as needed to define the pivot - such as Creating Cross Tab Queries and Pivot Tables in SQL.
Actions
3. Re: How to transpose/pivot rows as columns in teiid ?

virtualdatabase Sep 27, 2017 5:06 PM (in response to shawkins)

A pivot FEATURE WOULD BE GREAT BUT in the meantime DDL for the CrossTab Function that is referenced in your link would be as well.
Actions
4. Re: How to transpose/pivot rows as columns in teiid ?

shawkins Oct 4, 2017 10:33 AM (in response to virtualdatabase)

Yes, it is possible to implement something like the crosstab function - but we lack a record type, so it would be a table valued function instead where the names/types would be part of the function:

select ... from crosstab(sql as col1 type, ...) as x

Can you log an enhancement for that?
Actions