数据库多行转换为单一列

  • Z时代
  • 2024-01-10
  • 分类：综合

database

数据库存储与实际显示需求不一样时，我们得写SQL来实现数据呈现：

如：

 

先来看看数据表：

 

CREATETABLE[dbo].[Expenses]
(
[Date] DATE,
[Description]NVARCHAR(40),
[Charge]DECIMAL(18,2)
)
GO

Source Code

 

然后，我们为表填充一些数据，比如春节购买开支：

 

INSERTINTO[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

Source Code

 

所有数据准备完毕，现在写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

Source Code

 

以上是 数据库多行转换为单一列 的全部内容， 来源链接： utcz.com/z/532308.html

回到顶部