数据库多行转换为单一列

数据库存储与实际显示需求不一样时，我们得写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