如何取消对多列SQLServer的透视 [英] How to UnPivot for multiple columns SQLServer

问题描述

在我的应用程序中，我使用了如下的商店产品描述值:

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    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

我正在将应用程序迁移到最新技术，所以我正在重组mt表，以避免出现复杂情况.所以我希望结果是适应我的新表，如下所示:

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:

我不希望使用Null值.我只需要有有效的数据.例如，示例S4，D4，Q4为Null值，因此Output应该忽略它.

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;

推荐答案

尝试交叉应用

SELECT BILLNO, CUS_NAME, UnP.SNOA, UnP.PDTA --,..
FROM [tmpFormat]
CROSS APPLY
( SELECT S1 AS SNOA, D1 as PDTA --,..
  UNION ALL
  SELECT S2 AS SNOA, D2 as PDTA --,.. 
 -- ..
) UnP

这篇关于如何取消对多列SQLServer的透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！