列转行结果中将空值也显示出来

[Leshami]

CREATE TABLE tb
(
    ID CHAR(10) 
    ,Month1qty INT 
    ,Month2qty INT 
    ,Month3qty INT 
    ,Month4qty INT 
    ,Month5qty INT 
    ,Month6qty INT  
)               

INSERT INTO tb
SELECT 'F000000E24',55,30,45,NULL,NULL ,60

SELECT * FROM tb

SELECT ID
       ,timeperiod
       ,val AS performance
FROM tb
UNPIVOT (val FOR timeperiod IN (Month1qty,Month2qty,Month3qty,Month4qty,Month5qty,Month6qty))  AS x      
       
--结果       
     ID     timeperiod        performance
---------- ------------------ -----------
F000000E24 Month1qty             55
F000000E24 Month2qty             30
F000000E24 Month3qty             45
F000000E24 Month6qty             60

(4 row(s) affected)


如何将Month4qty,Month5qty在结果中也显示出来,且performance的值显示为NULL .

[百年树人]

UNPIVOT 不知道，如果字段不多，可以直接union all

[快乐_石头]

SELECT ID
       ,timeperiod
       ,val AS performance
FROM (select ID,
      isnull(Month1qty,0)Month1qty,
      isnull(Month2qty,0)Month2qty,
      isnull(Month3qty,0)Month3qty,
      isnull(Month4qty,0)Month4qty,
      isnull(Month5qty,0)Month5qty,
      isnull(Month6qty,0)Month6qty
      from tb)tb
UNPIVOT (val FOR timeperiod IN (Month1qty,Month2qty,Month3qty,Month4qty,Month5qty,Month6qty))  AS x

[快乐_石头]

/*
ID         timeperiod                                                                                                                       performance
---------- -------------------------------------------------------------------------------------------------------------------------------- -----------
F000000E24 Month1qty                                                                                                                        55
F000000E24 Month2qty                                                                                                                        30
F000000E24 Month3qty                                                                                                                        45
F000000E24 Month4qty                                                                                                                        0
F000000E24 Month5qty                                                                                                                        0
F000000E24 Month6qty                                                                                                                        60

(6 個資料列受到影響)
*/

[黄_瓜]

UNPIVOT 的输入中的 NULL 不会显示在输出中
要不你用isnull() 替换掉

[Leshami]

引用 2 楼 happy_stone 的回复:

SQL codeSELECT ID
,timeperiod
,valAS performanceFROM (select ID,isnull(Month1qty,0)Month1qty,isnull(Month2qty,0)Month2qty,isnull(Month3qty,0)Month3qty,isnull(Month4qty,0)Month4qty,isnull(Month5qty,0)Month5qty,isnull(Month6qty,0)Month6qtyfrom tb)tb
UNPIVOT (valFOR timeperiodIN (Month1qty,Month2qty,Month3qty,Month4qty,Month5qty,Month6qty))AS x

isnull(Month1qty,0)
能否给个 NULL值，而不是0

[黄_瓜]

引用 5 楼 robinson_0612 的回复:

引用 2 楼 happy_stone 的回复:SQL codeSELECT ID ,timeperiod ,valAS performanceFROM (select ID,isnull(Month1qty,0)Month1qty,isnull(Month2qty,0)Month2qty,isnull(Month3qty,0)Month3qty,isnull(Month4qty,0)Month4qty,isnull(Month5qty,0)Month5qty,isnull(Month6qty,0)Month6qtyfrom tb)tb UNPIVOT (valFOR timeperiodIN (Month1qty,Month2qty,Month3qty,Month4qty,Month5qty,Month6qty))AS x

isnull(Month1qty,0)
能否给个 NULL值，而不是0

--try
isnull(lie,'null')

UNPIVOT 的输入中的 NULL 不会显示在输出中
给个null 就肯定不会输出

[Leshami]

使得结果为
ID timeperiod performance
---------- ------------------ -----------
F000000E24 Month1qty 55
F000000E24 Month2qty 30
F000000E24 Month3qty 45
F000000E24 Month4qty NULL
F000000E24 Month5qty NULL
F000000E24 Month6qty 60

[快乐_石头]

引用 7 楼 robinson_0612 的回复:

使得结果为
    ID    timeperiod        performance
---------- ------------------ -----------
F000000E24 Month1qty            55
F000000E24 Month2qty            30
F000000E24 Month3qty            45
F000000E24 Month4qty            NULL
F000000E24 Month5qty            NULL
F000000E24 Month6qty            60

非要這樣嗎
再嵌套?

[快乐_石头]

drop table tb
go
CREATE TABLE tb
(
    ID CHAR(10) 
    ,Month1qty INT 
    ,Month2qty INT 
    ,Month3qty INT 
    ,Month4qty INT 
    ,Month5qty INT 
    ,Month6qty INT  
)               

INSERT INTO tb
SELECT 'F000000E24',55,30,45,NULL,NULL ,60
select ID,
       timeperiod,
       case when performance=0 then null else performance end performance
from (
SELECT ID
       ,timeperiod
       ,val AS performance
FROM (select ID,
      isnull(Month1qty,0)Month1qty,
      isnull(Month2qty,0)Month2qty,
      isnull(Month3qty,0)Month3qty,
      isnull(Month4qty,0)Month4qty,
      isnull(Month5qty,0)Month5qty,
      isnull(Month6qty,0)Month6qty
      from tb)tb
UNPIVOT (val FOR timeperiod IN (Month1qty,Month2qty,Month3qty,Month4qty,Month5qty,Month6qty))  AS x)
t
/*
ID         timeperiod                                                                                                                       performance
---------- -------------------------------------------------------------------------------------------------------------------------------- -----------
F000000E24 Month1qty                                                                                                                        55
F000000E24 Month2qty                                                                                                                        30
F000000E24 Month3qty                                                                                                                        45
F000000E24 Month4qty                                                                                                                        NULL
F000000E24 Month5qty                                                                                                                        NULL
F000000E24 Month6qty                                                                                                                        60

(6 個資料列受到影響)
*/

[Leshami]

哦，这样子的话是比较麻烦，有20列啊