数据库多行转换为单一列，

数据库多行转换为单一列，

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