Replace the value of a column using pivot

Question

I have a query where I use the pivot function to display the information almost as I wish. The structure of the tables is like this:

When executing the query the result is shown as follows:

What I would like to do is replace the state of the "TableC" with an "X", the value is always 1. And the NULL with a blank space.

The tables

create database dbdemo;
use dbdemo;
create table TableA(
IdTableA int primary key identity(1,1),
Number int
);
create table TableB(
IdTableB int  primary key identity(1,1),
Description varchar(50)
);
create table TableC(
IdTableC int primary key identity(1,1),
IdTableA int,
IdTableB int,
Status int,
constraint FK_TableA_TableC foreign key(IdTableA) references TableA(IdTableA),
constraint FK_TableB_TableC foreign key(IdTableB) references TableB(IdTableB)
);
insert into TableA values(5000),(10000),(15000),(20000),(25000);
insert into TableB values('Descrip 1'),('Descrip 2'),('Descrip 3'),('Descrip 4');
insert into TableC values(1,1,1),(1,2,1),(1,4,1),(2,1,1),(2,3,1),(2,4,1),(3,1,1),
(3,2,1),(3,3,1),(3,4,1),(4,2,1),(4,3,1);

The query

DECLARE @Pivot_Column [nvarchar](max);  
DECLARE @Query [nvarchar](max);  
   
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(Number) FROM  
(SELECT DISTINCT a.[Number] FROM TableC as c
inner join TableA as a on a.IdTableA=c.IdTableA) as Tab  
   
SELECT @Query='SELECT Description,'+@Pivot_Column+'FROM   
(SELECT b.Description, a.[Number],c.Status FROM TableC as c
inner join TableA as a on a.IdTableA=c.IdTableA 
inner join TableB as b on b.IdTableB=c.IdTableB) as Tab1  
PIVOT  
(SUM(Status) FOR [Number] IN ('+@Pivot_Column+')) as Tab2'  
EXEC  sp_executesql  @Query

Maybe some way to include something like

isnull(case when Status=1 then 'X' end ,'')

user_15332650 · Answer

I think all you need is

select... isnull(case when status=1 then 'X' end ,'')

so to merge into your existing query you need to construct the pivot column separately from the select list.

Try the following:

declare @Pivot_Column [nvarchar](max);  
declare @Pivot_Column2 [nvarchar](max);
declare @Query [nvarchar](max);  
   
select @Pivot_Column= Coalesce(@Pivot_Column+',','')+ QuoteName(Number),
    @Pivot_Column2= Coalesce(@Pivot_Column2+',','') + 'isnull(case when ' + QuoteName(Number) + '=1 then ''X'' end ,'''')' + QuoteName(Number)
from  
(select distinct a.[Number] from TableC as c
inner join TableA as a on a.IdTableA=c.IdTableA) as Tab  

print @Pivot_Column

select @Query='SELECT Description,' + @Pivot_Column2 + 'FROM   
(SELECT b.Description, a.[Number],c.Status FROM TableC as c
inner join TableA as a on a.IdTableA=c.IdTableA 
inner join TableB as b on b.IdTableB=c.IdTableB) as Tab1  
PIVOT (SUM(Status) FOR [Number] IN ('+@Pivot_Column+')) as Tab2'  
exec sp_executesql @Query

Replace the value of a column using pivot

1 Answers