如何实现表中数据的透视功能，请大神指教。

[猪扒界]

一、数据源
编码 统计日期 数量
A1 2017-08-01 2
A1 2017-08-02 1
A1 2017-08-03 3
A1 2017-08-04 1
A1 2017-08-06 5
A1 2017-08-07 1
A2 2017-08-01 2
A2 2017-08-02 1
A2 2017-08-05 1
A2 2017-08-06 2
A3 2017-08-01 1
A3 2017-08-04 2
A3 2017-08-07 3
A4 2017-08-01 2
A4 2017-08-02 3
A4 2017-08-06 3
A5 2017-08-01 4
A5 2017-08-02 3
A5 2017-08-03 5
A5 2017-08-04 2
A5 2017-08-05 5
A5 2017-08-06 4
A5 2017-08-07 4
A6 2017-08-01 3
A6 2017-08-02 3
A6 2017-08-04 1
A6 2017-08-05 1
A6 2017-08-06 5
A6 2017-08-07 2
… … …


二、实现结果，如下图所示，按日期降序排列，不同编码的按日期进行统计

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([编码] nvarchar(22),[统计日期] Date,[数量] int)
Insert #T
select N'A1','2017-08-01',2 union all
select N'A1','2017-08-02',1 union all
select N'A1','2017-08-03',3 union all
select N'A1','2017-08-04',1 union all
select N'A1','2017-08-06',5 union all
select N'A1','2017-08-07',1 union all
select N'A2','2017-08-01',2 union all
select N'A2','2017-08-02',1 union all
select N'A2','2017-08-05',1 union all
select N'A2','2017-08-06',2 union all
select N'A3','2017-08-01',1 union all
select N'A3','2017-08-04',2 union all
select N'A3','2017-08-07',3 union all
select N'A4','2017-08-01',2 union all
select N'A4','2017-08-02',3 union all
select N'A4','2017-08-06',3 union all
select N'A5','2017-08-01',4 union all
select N'A5','2017-08-02',3 union all
select N'A5','2017-08-03',5 union all
select N'A5','2017-08-04',2 union all
select N'A5','2017-08-05',5 union all
select N'A5','2017-08-06',4 union all
select N'A5','2017-08-07',4 union all
select N'A6','2017-08-01',3 union all
select N'A6','2017-08-02',3 union all
select N'A6','2017-08-04',1 union all
select N'A6','2017-08-05',1 union all
select N'A6','2017-08-06',5 union all
select N'A6','2017-08-07',2
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 编码'
select @sql=@sql+' , max(case 统计日期 when '''+ RTRIM([统计日期]) +''' then 数量 else 0 end) ['+ RTRIM([统计日期]) +']'
from (select distinct [统计日期] from #T) as a
set @sql=@sql+' from #T group by 编码'
exec(@sql) 

[繁花尽流年]

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([编码] nvarchar(22),[统计日期] Date,[数量] int)
Insert #T
select N'A1','2017-08-01',2 union all
select N'A1','2017-08-02',1 union all
select N'A1','2017-08-03',3 union all
select N'A1','2017-08-04',1 union all
select N'A1','2017-08-06',5 union all
select N'A1','2017-08-07',1 union all
select N'A2','2017-08-01',2 union all
select N'A2','2017-08-02',1 union all
select N'A2','2017-08-05',1 union all
select N'A2','2017-08-06',2 union all
select N'A3','2017-08-01',1 union all
select N'A3','2017-08-04',2 union all
select N'A3','2017-08-07',3 union all
select N'A4','2017-08-01',2 union all
select N'A4','2017-08-02',3 union all
select N'A4','2017-08-06',3 union all
select N'A5','2017-08-01',4 union all
select N'A5','2017-08-02',3 union all
select N'A5','2017-08-03',5 union all
select N'A5','2017-08-04',2 union all
select N'A5','2017-08-05',5 union all
select N'A5','2017-08-06',4 union all
select N'A5','2017-08-07',4 union all
select N'A6','2017-08-01',3 union all
select N'A6','2017-08-02',3 union all
select N'A6','2017-08-04',1 union all
select N'A6','2017-08-05',1 union all
select N'A6','2017-08-06',5 union all
select N'A6','2017-08-07',2
Go

DECLARE @sql VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql+'],['+CONVERT(CHAR(10), [统计日期], 120) FROM #T GROUP BY [统计日期]
SET @sql = STUFF(@sql, 1, 3, '')
SET @sql = 'select * from #T pivot (max([数量]) for [统计日期] in (['+@sql+']))a'
PRINT(@sql)
EXEC (@sql)

借用1#数据

[猪扒界]

不行呀，没有实现降序排序

[繁花尽流年]

引用 3 楼 bbaizz 的回复:

不行呀，没有实现降序排序

IF NOT OBJECT_ID(N'Tempdb..#T') IS NULL DROP TABLE #T
CREATE TABLE #T([编码] NVARCHAR(22), [统计日期] DATE, [数量] INT)
INSERT INTO #T
SELECT N'A1', '2017-08-01', 2 UNION ALL
SELECT N'A1', '2017-08-02', 1 UNION ALL
SELECT N'A1', '2017-08-03', 3 UNION ALL
SELECT N'A1', '2017-08-04', 1 UNION ALL
SELECT N'A1', '2017-08-06', 5 UNION ALL
SELECT N'A1', '2017-08-07', 1 UNION ALL
SELECT N'A2', '2017-08-01', 2 UNION ALL
SELECT N'A2', '2017-08-02', 1 UNION ALL
SELECT N'A2', '2017-08-05', 1 UNION ALL
SELECT N'A2', '2017-08-06', 2 UNION ALL
SELECT N'A3', '2017-08-01', 1 UNION ALL
SELECT N'A3', '2017-08-04', 2 UNION ALL
SELECT N'A3', '2017-08-07', 3 UNION ALL
SELECT N'A4', '2017-08-01', 2 UNION ALL
SELECT N'A4', '2017-08-02', 3 UNION ALL
SELECT N'A4', '2017-08-06', 3 UNION ALL
SELECT N'A5', '2017-08-01', 4 UNION ALL
SELECT N'A5', '2017-08-02', 3 UNION ALL
SELECT N'A5', '2017-08-03', 5 UNION ALL
SELECT N'A5', '2017-08-04', 2 UNION ALL
SELECT N'A5', '2017-08-05', 5 UNION ALL
SELECT N'A5', '2017-08-06', 4 UNION ALL
SELECT N'A5', '2017-08-07', 4 UNION ALL
SELECT N'A6', '2017-08-01', 3 UNION ALL
SELECT N'A6', '2017-08-02', 3 UNION ALL
SELECT N'A6', '2017-08-04', 1 UNION ALL
SELECT N'A6', '2017-08-05', 1 UNION ALL
SELECT N'A6', '2017-08-06', 5 UNION ALL
SELECT N'A6', '2017-08-07', 2

DECLARE @sql VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql+','+QUOTENAME(CONVERT(CHAR(10), [统计日期], 120),'') FROM #T GROUP BY [统计日期] ORDER BY [统计日期] DESC
SET @sql = STUFF(@sql, 1, 1, '')
SET @sql = 'select * from #T pivot (max([数量]) for [统计日期] in ('+@sql+'))a'
PRINT(@sql)
EXEC (@sql)

好了

[猪扒界]

不行，提示如下 select * from #T pivot (max([数量]) for [统计日期] in ([2017-08-07],[2017-08-06],[2017-08-05],[2017-08-04],[2017-08-03],[2017-08-02],[2017-08-01]))a 消息 102，级别 15，状态 1，第 1 行 '(' 附近有语法错误。

[猪扒界]

引用 5 楼 bbaizz 的回复:

不行，提示如下 select * from #T pivot (max([数量]) for [统计日期] in ([2017-08-07],[2017-08-06],[2017-08-05],[2017-08-04],[2017-08-03],[2017-08-02],[2017-08-01]))a 消息 102，级别 15，状态 1，第 1 行 '(' 附近有语法错误。

可以了，调整好了。

[繁花尽流年]

引用 6 楼 bbaizz 的回复:

[quote=引用 5 楼 bbaizz 的回复:] 不行，提示如下 select * from #T pivot (max([数量]) for [统计日期] in ([2017-08-07],[2017-08-06],[2017-08-05],[2017-08-04],[2017-08-03],[2017-08-02],[2017-08-01]))a 消息 102，级别 15，状态 1，第 1 行 '(' 附近有语法错误。

可以了，调整好了。[/quote]

DECLARE @sql VARCHAR(MAX),@sql_col VARCHAR(MAX),@sql_total VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql+','+QUOTENAME(CONVERT(CHAR(10), [统计日期], 120),'') FROM #T GROUP BY [统计日期] ORDER BY [统计日期] DESC
SET @sql = STUFF(@sql, 1, 1, '')
SET @sql_col = ''
SELECT @sql_col = @sql_col+',isnull('+QUOTENAME(CONVERT(CHAR(10), [统计日期], 120),'')+',0) as'+QUOTENAME(CONVERT(CHAR(10), [统计日期], 120),'') FROM #T GROUP BY [统计日期] ORDER BY [统计日期] DESC
SET @sql_col = STUFF(@sql_col, 1, 1, '')
SET @sql_total = ''
SELECT @sql_total = @sql_total+'+isnull('+QUOTENAME(CONVERT(CHAR(10), [统计日期], 120),'')+',0)' FROM #T GROUP BY [统计日期] ORDER BY [统计日期] DESC
SET @sql_total = STUFF(@sql_total, 1, 1, '')

SET @sql = 'select [编码],'+@sql_col+','+@sql_total+' as [合计] from #T pivot (max([数量]) for [统计日期] in ('+@sql+'))a'
PRINT(@sql)
EXEC (@sql)

帮你补个合计