VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > SQL教程 >
  • sql教程之数据库多行转换为单一列

本站最新发布    SQL Server 2016数据库视频教程

试听地址   https://www.xin3721.com/eschool/SQLxin3721/

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

 

相关教程