Skip to Content
0
Jun 22, 2015 at 05:40 PM

Translate SQL Pivot query into HANA

979 Views

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