列名显示日期 ? DECLARE @Sql NVARCHAR(max)='' SELECT @Sql=@Sql+','+QUOTENAME(CONVERT(VARCHAR(10),日期,120))+'=sum(case when CONVERT(VARCHAR(10),日期,120)='+QUOTENAME(CONVERT(VARCHAR(10),日期,120),'''')+' then 结算金额 else 0 end)' FROM 表 GROUP BY CONVERT(VARCHAR(10),日期,120) EXEC('SELECT 商品名'+@Sql+' FROM 表 GROUP BY 商品名
-- 测试数据 WITH table1(日期,商品名,结算金额) AS ( SELECT '2016-05-23','A',100 UNION ALL SELECT '2016-05-23','B',90 UNION ALL SELECT '2016-05-23','C',110 UNION ALL SELECT '2016-05-24','A',200 UNION ALL SELECT '2016-05-24','C',80 UNION ALL SELECT '2016-05-26','B',120 UNION ALL SELECT '2016-05-26','D',50 ) SELECT Convert(varchar(10),日期) 日期, 商品名, 结算金额 INTO #temp FROM table1-- 动态查询 DECLARE @sql varchar(max) DECLARE @columns varchar(max)SET @columns = '' SELECT @columns = @columns+',['+日期+']' FROM (SELECT DISTINCT 日期 FROM #temp ) t ORDER BY 日期SET @columns = STUFF(@columns,1,1,'')SET @sql = ' SELECT * FROM #temp PIVOT ( SUM(结算金额) FOR 日期 IN ('+@columns+') ) p'PRINT @sqlEXEC(@sql) PRINT 出来的动态 SQL SELECT * FROM #temp PIVOT ( SUM(结算金额) FOR 日期 IN ([2016-05-23],[2016-05-24],[2016-05-26]) ) p 查询结果 商品名 2016-05-23 2016-05-24 2016-05-26 ------ ----------- ----------- ----------- A 100 200 NULL B 90 NULL 120 C 110 80 NULL D NULL NULL 50
sql一般是操作二维表.
你要的这个功能,估计难了.
不过,你如果只是展现数据,是可以把数据放到excel里透视的.
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql=@Sql+','+QUOTENAME(CONVERT(VARCHAR(10),日期,120))+'=sum(case when CONVERT(VARCHAR(10),日期,120)='+QUOTENAME(CONVERT(VARCHAR(10),日期,120),'''')+' then 结算金额 else 0 end)' FROM 表 GROUP BY CONVERT(VARCHAR(10),日期,120)
EXEC('SELECT 商品名'+@Sql+' FROM 表 GROUP BY 商品名
WITH table1(日期,商品名,结算金额) AS (
SELECT '2016-05-23','A',100 UNION ALL
SELECT '2016-05-23','B',90 UNION ALL
SELECT '2016-05-23','C',110 UNION ALL
SELECT '2016-05-24','A',200 UNION ALL
SELECT '2016-05-24','C',80 UNION ALL
SELECT '2016-05-26','B',120 UNION ALL
SELECT '2016-05-26','D',50
)
SELECT Convert(varchar(10),日期) 日期,
商品名,
结算金额
INTO #temp
FROM table1-- 动态查询
DECLARE @sql varchar(max)
DECLARE @columns varchar(max)SET @columns = '' SELECT @columns = @columns+',['+日期+']'
FROM (SELECT DISTINCT 日期
FROM #temp
) t
ORDER BY 日期SET @columns = STUFF(@columns,1,1,'')SET @sql = '
SELECT *
FROM #temp
PIVOT (
SUM(结算金额)
FOR 日期 IN ('+@columns+')
) p'PRINT @sqlEXEC(@sql)
PRINT 出来的动态 SQL
SELECT *
FROM #temp
PIVOT (
SUM(结算金额)
FOR 日期 IN ([2016-05-23],[2016-05-24],[2016-05-26])
) p
查询结果
商品名 2016-05-23 2016-05-24 2016-05-26
------ ----------- ----------- -----------
A 100 200 NULL
B 90 NULL 120
C 110 80 NULL
D NULL NULL 50