sql - SQL Server 数据透视表中为空？

Question

我有这个查询

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 数据!)

Answer 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