34,576
社区成员
发帖
与我相关
我的任务
分享
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Flhz]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Flhz]
GO
CREATE TABLE [dbo].[Flhz] (
[编码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[类型] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[金额] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
insert into flhz values( '01','口服',12.5)
insert into flhz values( '01','注射',13.5)
insert into flhz values( '01','其他',14.5)
insert into flhz values( '02','口服',12.5)
insert into flhz values( '02','注射',2.5)
insert into flhz values( '02','其他',6.5)
insert into flhz values( '03','口服',7.5)
insert into flhz values( '03','注射',11.5)
insert into flhz values( '03','其他',23.5)
select [编码] as [编码] ,
max(case [类型] when '口服' then [金额] else 0 end) 口服,
max(case [类型] when '注射' then [金额] else 0 end) 注射,
max(case [类型] when '其他' then [金额] else 0 end) 其他
from dbo.Flhz
group by [编码]--这个比较好理解
declare @sql varchar(8000)
set @sql = 'select [编码] '
select @sql = @sql + ' , max(case [类型] when ''' + 类型 + ''' then [金额] else 0 end) [' + 类型 + ']'
from (select distinct 类型 from Flhz) as a
set @sql = @sql + ' from Flhz group by [编码]'
exec(@sql)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Flhz]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Flhz]
GO
CREATE TABLE [dbo].[Flhz] (
[编码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[类型] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[金额] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
insert into flhz values( '01','口服',12.5)
insert into flhz values( '01','注射',13.5)
insert into flhz values( '01','其他',14.5)
insert into flhz values( '02','口服',12.5)
insert into flhz values( '02','注射',2.5)
insert into flhz values( '02','其他',6.5)
insert into flhz values( '03','口服',7.5)
insert into flhz values( '03','注射',11.5)
insert into flhz values( '03','其他',23.5)
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + [类型] from Flhz group by [类型]
set @sql = '[' + @sql + ']'
exec ('select * from Flhz a pivot (max([金额]) for [类型] in (' + @sql + ')) b')
/*
编码 口服 其他 注射
-------------------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
01 12.50 14.50 13.50
02 12.50 6.50 2.50
03 7.50 23.50 11.50
(3 行受影响)
select [编码] as [编码] ,
max(case [类型] when '口服' then [金额] else 0 end) 口服,
max(case [类型] when '注射' then [金额] else 0 end) 注射,
max(case [类型] when '其他' then [金额] else 0 end) 其他
from dbo.Flhz
group by [编码]