In my application, I have used store product description values as follows:
ID BILLNO CUS_NAME DATE TOT_BAL S1 S2 S3 S4 D1 D2 D3 D4 Q1 Q2 Q3 Q4 U1 U2 U3 U4 T1 T2 T3 T4 TOTAL CUSCODE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
29 1 XXX Apr-03-2017 1932 1 2 3 NULL AAA BBB CCC NULL 6 30 6 NULL 80 35 67 NULL 480 1050 402 0 1932 DF
40 2 YYYY Apr-04-2017 6454 1 2 3 NULL AAA DDD FFF NULL 30 24 50 NULL 80 96 35 NULL 2400 2304 1750 0 6454 MQ
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
I am migrating my application to latest technologies, so i am re structuring mt tables to avoid complications. So i want the result would be to adapt my new table as follows:
BILLNO CUS_NAME DATE TOT_BAL SNO Pdt QTY Unit Tot CUSCODE
1 XXX Apr-03-2017 1932 1 AAA 6 80 480 DF
1 XXX Apr-03-2017 1932 2 BBB 30 35 1050 DF
1 XXX Apr-03-2017 1932 3 CCC 6 67 402 DF
2 YYY Apr-04-2017 6454 1 AAA 30 80 2400 MQ
2 YYY Apr-04-2017 6454 2 DDD 24 96 2304 MQ
2 YYY Apr-04-2017 6454 3 FFF 50 35 1750 MQ
I tried below query but its giving wrong result:
I don't want Null values. I just need to have valid data only. For Example S4, D4, Q4 are Null values , so Output should ignore that.
SELECT BILLNO, CUS_NAME,SNOA,SNO,PDTA,PDT
FROM [tmpFormat]
UNPIVOT
(
SNO
FOR SNOA IN (S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14,S15,S16,S17)
) UnPIV
UNPIVOT
(
PDT
FRO PDTA IN (D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17)
) UnPiv1;