SQL Server枢轴是否为空？

一尘不染

SQL Server枢轴是否为空？

sql

我有这个查询

DECLARE @Test TABLE
(
     RowID  INT IDENTITY(1,1) PRIMARY KEY
    ,[Name]VARCHAR(10) NOT NULL
    ,tool   VARCHAR(10) NOT NULL,
    stam NVARCHAR(MAX)

);  
INSERT  @Test   VALUES ('john', 'vocals','1');
INSERT  @Test   VALUES ('john', 'guitar','1');
INSERT  @Test   VALUES ('paul', 'vocals','1');
INSERT  @Test   VALUES ('paul', 'bass','1');
INSERT  @Test   VALUES ('george', 'vocals','1');
INSERT  @Test   VALUES ('george', 1,'1');
INSERT  @Test   VALUES ('ringo', 'vocals','1');
INSERT  @Test   VALUES ('ringo', 3,'1');
INSERT  @Test   VALUES ('ringo', 'drums','1');
INSERT  @Test   VALUES ('yoko', 'vocals','1');
INSERT  @Test   VALUES ('royi', 'vocals','1');
INSERT  @Test   VALUES ('royi', 'guitar','1');


;WITH PivotSource
AS
(
    SELECT   t.[Name], t.[tool]  
     FROM    @Test t
)

SELECT  * 
FROM    PivotSource  
 PIVOT   ( max(tool) FOR tool IN ([vocals], [guitar], [bass],[drums]) ) pvt;

有什么方法可以替换 null 为""（空字符串）吗？（无需修改CTE数据！）

阅读 157

2021-05-23

共1个答案

一尘不染

使用：

SELECT pvt.Name
    , isnull(pvt.[vocals], '') [vocals]
    , isnull(pvt.[guitar], '') [guitar]
    , isnull(pvt.[bass], '') [bass]
    , isnull(pvt.[drums], '') [drums]
FROM PivotSource  
PIVOT
(
    max(tool) 
    FOR tool 
    IN ([vocals], [guitar], [bass], [drums]) 
) pvt;

输出：

Name       vocals     guitar     bass       drums
---------- ---------- ---------- ---------- ----------
george     vocals                           
john       vocals     guitar                
paul       vocals                bass       
ringo      vocals                           drums
royi       vocals     guitar                
yoko       vocals

2021-05-23