时间戳

首页 > 解决方案 > 使用数据透视替换列的值

sql - 使用数据透视替换列的值

问题描述

我有一个查询，我使用数据透视函数几乎如我所愿地显示信息。表的结构是这样的：

表

执行查询时，结果如下所示：

询问

我想做的是用“X”替换“TableC”的状态，该值始终为 1。NULL 为空格。

桌子

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);

查询

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

也许某种方式可以包括类似的东西

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

标签： sqlsql-serverreplacepivotisnull

解决方案

我想你所需要的只是

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

所以要合并到您现有的查询中，您需要与选择列表分开构建数据透视列。

尝试以下操作：

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

推荐阅读