Zero in SQL Server pivot?

Question

I have this request

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; 

result:

is there any way to replace null with "" (empty string)? ( without modifying CTE data!)

Answer 1

Using:

 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; 

Output:

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