Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

wdchristensen
Contributor III

Convert TSQL Pivot Query to Qlik Syntax

Below is a simple example of pivot query in tsql and I am curious how this can be done efficiently in Qlik (I am using Sense but I don't think that is relevant). In the example below the column only contains 4 values but I would like a solution that could easily scale to values > 10. I searched the help and came up with a bunch of articles related to pivot tables. Are some solutions just better implemented in SQL or is that just the perception of a newbie?

IF OBJECT_ID('tempdb..#t') IS NOT NULL

    DROP TABLE #t

create table #t (v varchar(50), i int)

insert into #t (v, i) values ('A,B,C,D',1)

,('E,F,G,H',2), ('I,J,K,L', 3), ('M,N,O', 4)

--Inorder to get into same row -pivoting the data

select * from (

select * from #t t cross apply (select RowN=Row_Number() over (Order by (SELECT NULL)), value from string_split(t.v, ',') ) d) src

pivot (max(value) for src.RowN in([1],[2],[3],[4])) p