How to Unpivot in SQL? (SAP HANA) (Columns to rows)

Question

I need to unpivot some data in SAP HANA. I set up an example table to try it out on, but I still can't get anywhere.

The actual table contains 1000's of ID's and ~50 columns, but I want to do this for many tables, so while I can specify the FieldNames(Original Columns), it would be nice to have an automated solution.

Here is the example table I setup:

I would like to get the results into this form:

Note: The '?' represents NULL.

To create the example table:

create local temporary table #example
(
ID NVARCHAR(255),
Name NVARCHAR(255),
Country NVARCHAR(255),
Balance Decimal(18,2)
);

insert into #example values('ID1','Bill','USA', 100);
insert into #example values('ID2','','', 45);
insert into #example values('ID3', NULL,NULL, 768);
insert into #example values('ID4',NULL,'France', 42);

Th union all works, but I still have to specify the column names. You can add this as an answer, if not then I will — ThomasRones, Feb 05 '20 at 10:50

score 0 · Accepted Answer · answered Feb 05 '20 at 10:55

0

Here is a solution that requires me to specify the column names and type conversions:

(Credit to jarlh for the union suggestion)

select 
"ID",
'Country' as "FieldName",
"COUNTRY" as "FieldValue"
from #example
union all
select 
"ID",
'Name' as "FieldName",
"NAME" as "FieldValue"
from #example
union all
select 
"ID",
'Balance' as "FieldName",
CAST("BALANCE" as NVARCHAR) as "FieldValue"
from #example

answered Feb 05 '20 at 10:55

ThomasRones

657
8
29

1

This is not at all automated - if you had asked for an option just to make it work, this is basically *the* standard approach to perform pivot/unpivot. – Lars Br. Mar 03 '20 at 12:55
2

That's true, so I created https://unpivot-sql.com/ to semi-automate it for me – ThomasRones Mar 03 '20 at 14:47

score 0 · Answer 2 · answered Feb 05 '20 at 21:56

Creating a key-value store like table is the wrong thing to do in most situations that involve SQL databases. That said, with HANA SDI/SDQ (Smart data Integration/smart data quality) you can create a “flow graph” (basically a data transformation process) that does the pivot/unpivot operation without having to code much.

How to Unpivot in SQL? (SAP HANA) (Columns to rows)

2 Answers2

Linked