数据库多行转换为单一列
程序员文章站
2022-04-16 09:18:21
数据库存储与实际显示需求不一样时,我们得写SQL来实现数据呈现: 如: 先来看看数据表: CREATE TABLE [dbo].[Expenses] ( [Date] DATE, [Description] NVARCHAR(40), [Charge] DECIMAL(18,2) ) GO Sour ......
数据库存储与实际显示需求不一样时,我们得写sql来实现数据呈现:
如:
先来看看数据表:
create table [dbo].[expenses] ( [date] date, [description] nvarchar(40), [charge] decimal(18,2) ) go
然后,我们为表填充一些数据,比如春节购买开支:
insert into [dbo].[expenses] ([date],[description],[charge]) values ('2020-01-22',n'鱿鱼',305.40), ('2020-01-22',n'猪肉',110.60), ('2020-01-22',n'青菜',36.90), ('2020-01-22',n'酒',30.00), ('2020-01-22',n'米',75.00), ('2020-01-23',n'鱿鱼',200.40), ('2020-01-23',n'猪肉',50.00), ('2020-01-23',n'青菜',14.30), ('2020-01-23',n'酒',30.00), ('2020-01-23',n'米',20.00), ('2020-01-24',n'鱿鱼',460.00), ('2020-01-24',n'猪肉',200.00), ('2020-01-24',n'青菜',90.00), ('2020-01-24',n'酒',50.00), ('2020-01-24',n'米',300.00) go
所有数据准备完毕,现在写sql来实现此功能:
select e1.[date],e1.[charge] as n'鱿鱼',e2.[charge] as n'猪肉',e3.[charge] as n'青菜',e4.[charge] as n'酒', e5.[charge] as n'米' from [dbo].[expenses] as e1,[dbo].[expenses] as e2,[dbo].[expenses] as e3,[dbo].[expenses] as e4,[dbo].[expenses] as e5 where e1.[date] = e2.[date] and e2.[date] = e3.[date] and e3.[date] = e4.[date] and e4.[date] = e5.[date] and e1.[description] = n'鱿鱼' and e2.[description] = n'猪肉' and e3.[description] = n'青菜' and e4.[description] = n'酒' and e5.[description] = n'米' go