Translate SQL Pivot query into HANA

[pm_witmond]

Hi,

Please assist me in translating the sql query below into a HANA version.

It's a SP to create a dynamic pivot table

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', ' + QUOTENAME(U_Lengte ) FROM (SELECT 0 as U_Lengte union all select distinct U_Lengte FROM Pakket_Voorraad ) AS x order by u_lengte;

SET @sql = N'with cte as(

SELECT  ItemCode,ItemName,U_Kenmerk_1,U_Kenmerk_2,U_Kenmerk_3, ' + STUFF(@columns, 1, 2, '') + '

FROM

(

  SELECT ItemCode,ItemName,U_Lengte, U_aantal,U_Kenmerk_1,U_Kenmerk_2,U_Kenmerk_3 FROM Pakket_Voorraad

  union all

  select ItemCode,ItemName,0 as U_Lengte, MTRS U_aantal,U_Kenmerk_1,U_Kenmerk_2,U_Kenmerk_3 FROM Pakket_Voorraad

) AS j

PIVOT

(

  SUM(U_aantal) FOR U_Lengte IN ('  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')  + ')

) AS p )

select * from cte order by Itemcode asc';

EXEC sp_executesql @sql

When we run this SP the @sql is filled with this example data

with cte as(

SELECT  ItemCode,ItemName,U_Kenmerk_1,U_Kenmerk_2,U_Kenmerk_3, [0], [127], [180], [210], [240], [250], [270], [300], [310], [330], [340], [360], [370], [390], [400], [420], [430], [450], [480], [510], [540], [570], [600]

FROM

(

  SELECT ItemCode,ItemName,U_Lengte, U_aantal,U_Kenmerk_1,U_Kenmerk_2,U_Kenmerk_3 FROM Pakket_Voorraad

  union all

  select ItemCode,ItemName,0 as U_Lengte, MTRS U_aantal,U_Kenmerk_1,U_Kenmerk_2,U_Kenmerk_3 FROM Pakket_Voorraad

) AS j

PIVOT

(

  SUM(U_aantal) FOR U_Lengte IN ( [0], [127], [180], [210], [240], [250], [270], [300], [310], [330], [340], [360], [370], [390], [400], [420], [430], [450], [480], [510], [540], [570], [600])

) AS p )

select * from cte order by Itemcode asc

Thanks,

Paul

[Former Member]

PIVOT and UNPIVOT are already approved by the SAP HANA SQL interface committee in terms of SQL syntax and semantics, but it is a matter of resources and priorities if and when it will be implemented. So I regret, at the moment you can't use these functions, but mid- to long-term there is a good chance that it will be implemented. Then your example would work without changes.

[Former Member]

Hi Paul,

what is SQL pivot and where does it exist in HANA? The only pivot table i'm aware of is in Excel and is utilizing MDX/ODBO interface between HANA and Excel.

thx,

greg

[pm_witmond]

Gregory,

ask Google for "TSQL PIVOT"

Regards,

Paul

[Former Member]

Paul,

i actually did google 'pivot table' and it seems to be a part of the sql standard. it's somewhat different than MDX query that runs in HANA and is subsequently sent to Excel 'pivot table'. again, i don't believe there's a graphical tool in HANA that has the same 'pivot' functionality as in Excel. also, i think HANA 'would like' us to try SQL script rather than 'plain' SQL and i'm not sure if things have improved since 2013:

thx,

greg

[lbreddemann]

This exact question had been asked and discussed a lot here on SCN.

You might have used a search engine yourself to find those entries before posting the question once again.

And : PIVOT is not part of the SQL standard but had been added by several DBMS vendors in different fashions.

For now, that is up to SPS 9, SAP HANA doesn't provide a PIVOT command on SQL level.

It's possible however, to incorporate this "turn columns to rows" operation in a data flow definition using the Enterprise Information Management (SAP HANA Enterprise Information Management – SAP Help Portal Page) features.

This is not a very dynamic process though as the input and output structure needs to be known upfront.

- Lars

[Former Member]

Lars,

thank you for pointing it out, so if i understand correctly both Oracle and SQL Server have PIVOT in their SQL syntax.

shouldn't it be in the works for HANA?

thx,

greg

[lbreddemann]

Well "should" is a very interesting word when it comes to platform product definitions

I'd give it my +1 if there was a poll for it.

But I'm pretty sure that my optinion is not the deciding factor here...

- Lars