CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Royi Namir
Royi Namir

Reputation: 148524

Null in SQL server pivot?

I have this query 

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;

the result is :

enter image description here

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

Upvotes: 4

Views: 7530

Answers (1)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56162

Use:

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

Upvotes: 7

Related Questions