欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库多行转换为单一列

程序员文章站 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