数据库多行转换为单一列

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

如:

数据库多行转换为单一列

 

先来看看数据表:

数据库多行转换为单一列

 

数据库多行转换为单一列
CREATE TABLE [dbo].[Expenses]
(
    [Date] DATE,
    [Description] NVARCHAR(40),
    [Charge] DECIMAL(18,2)
)
GO
Source Code

 

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

数据库多行转换为单一列

 

数据库多行转换为单一列
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
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

 

数据库多行转换为单一列

上一篇:【Java面试题】MySQL索引底层为什么用到B+树


下一篇:mysql 5.7.28 中GROUP BY报错问题 SELECT list is not in GROUP BY clause and contains no