Pivot multiple columns to rows in load script

[carlcimino]

Hello I am working on bringing data into QlikSense but need to pivot multiple columns to row values.  I can do so using CrossTable for one field but it doesn't work when I add more fields.  My script below works and pivots SER_NUM_1 - SER_NUM_4 into rows.  However I have fields MODEL_YEAR_1 - MODEL_YEAR_4 (and many other fields) that should pivot as well.  How does one pivot more than one attribute field?  In the screen print you can see that VIN_NUMBER contains stacked VIN and MODEL YEar but I want a column that says Model Year.

crosstable(UNIT_ID, VIN_NUMBER, 16)
load *
;
Select
cast(replicate('0',12-len(ltrim(rtrim(a.POLICY_NO))))+ltrim(rtrim(a.POLICY_NO)) as varchar(99)) as POLICY_NUMBER
, replicate('0',7-len(a.AGENT_NO))+a.AGENT_NO as PROD_N
, a.SUB_AGENT as SUB_PROD_N
, a.COMPANY_CODE
, DATEADD(YEAR, -1,a.EXPRN_DATE) as EFFECTIVE_DATE
, a.EXPRN_DATE as EXPIRATION_DATE
, left(a.AS_OF_YYYYMMDD,6) as AS_OF_YYYYMM
, a.NO_OF_CARS as NUMBER_OF_VEHICLES
, a.NO_OF_DRVRS as NUMBER_OF_DRIVERS
, a.SCR_SCORE as INSURANCE_SCORE
, a.NAME_INSD1 as INSURED_NAME
, a.INSD_STREET as MAILING_ADDRESS
, a.INSD_CITY as MAILING_CITY
, a.INSD_STATE as MAILING_STATE
, a.INSD_ZIP as MAILING_ZIPCODE
, a.TOT_TRM_PRM
, a.SER_NUM_1
, a.SER_NUM_2
, a.SER_NUM_3
, a.SER_NUM_4
From stg_prs_qpmis.vw_caasmstr a (nolock)
where ltrim(rtrim(a.POLICY_NO))='15966244'
;
exit script;

[Gysbert_Wassenaar · Accepted solution]

You can first create new fields that are combinations of the model year and serial: MODEL_YR1 & '|' & SER_NUM_1 as FIELD_1 ...etc. These new fields you can pivot using the crosstable function. Then from the table with the pivotted values you need to create a new table where you split the combined values again into separate fields using the subfield function: SubField(VIN_NUMBER, '|' , 1 ) as MODEL_YEAR, SubField(VIN_NUMBER, '|' , 2) as SER_NUM 

[carlcimino]

That is fantastic.  Thank you!