0 Replies Latest reply: May 18, 2017 10:27 AM by William Christensen RSS

    Convert TSQL Pivot Query to Qlik Syntax

    William Christensen

      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