SQL查询图表图表

本文介绍了SQL查询图表图表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下输出但不是我想要的...请帮助我获得欲望输出 With following查询

SELECT SUM（列名称） AS 总计，状态 DATEADD（DAY， 0 ，DATEDIFF（DAY， 0 ，DelivaryDate）） DeleiveryDate， FROM abc WHERE 列名称=字段名 AND （DelivaryDate BETWEEN @ FromDate AND @ ToDate ） GROUP BY DATEADD（DAY， 0 ，DATEDIFF（DAY， 0 ，DelivaryDate）），状态 ORDER BY DATEADD（DAY， 0 ，DATEDIFF（DAY， 0 ，DelivaryDate）），状态

什么我有 DelivaryDate状态总计 26-08-2014 DND 50 26-08-2014失败20 26-08-2014交付100 27-08-2014 DND 5 27-08-2014交付10美元

我想要什么 DelivaryDate DND交付失败总计 26-08-2014 50 20 100 170 27-08-2014 5 - 10 15

感谢Advanc e ...

解决方案

检查此查询希望这对您有所帮助。

DECLARE @cols AS NVARCHAR （MAX）， @ query AS NVARCHAR （MAX） 选择 @cols = STUFF（（ SELECT ' ，' + QUOTENAME（状态） FROM abc WHERE 列名称=字段名 AND （DelivaryDate BETWEEN @ FromDate AND @ ToDate ） GROUP BY DATEADD（DAY， 0 ，DATEDIFF（DAY， 0 ，DelivaryDate）），状态 ORDER BY DATEADD（DAY， 0 ，DATEDIFF（DAY， 0 ，DelivaryDate）），状态 FOR XML PATH（' '）， TYPE ）.value（' 。'，' NVARCHAR（MAX）'）， 1 ， 1 ，' '） set @ query = ' SELECT DeleiveryDate，' + @cols + ' 来自（选择SUM （列名）AS总计，状态 DATEADD（DAY，0，DATEDIFF（DAY，0，DelivaryDate）） DeleiveryDate FROM Abc WHERE Column Name = Fieldname AND（DelivaryDate BETWEEN @FromDate AND @ToDate） GROUP BY DATEADD（DAY，0，DATEDIFF（DAY，0，DelivaryDate）），状态）x pivot （ SUM（总计）状态为（' + @cols + ' ））p' 执行（ @查询）

I have a Following Output but not What i desire For... please help me to get desire output With Following Query

SELECT SUM(Column Name) AS Total ,Status DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) DeleiveryDate , Total FROM Abc WHERE Column Name = Fieldname AND (DelivaryDate BETWEEN @FromDate AND @ToDate) GROUP BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status ORDER BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status

What I have DelivaryDate Status Total 26-08-2014 DND 50 26-08-2014 Failed 20 26-08-2014 Delivered 100 27-08-2014 DND 5 27-08-2014 Delivered 10

What i Want DelivaryDate DND Failed Delivered Total 26-08-2014 50 20 100 170 27-08-2014 5 - 10 15

Thanks in Advance...

解决方案

Check this Query hope this will help you.

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(Status) FROM Abc WHERE Column Name = Fieldname AND (DelivaryDate BETWEEN @FromDate AND @ToDate) GROUP BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status ORDER BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT DeleiveryDate,' + @cols + ' from ( Select SUM(Column Name) AS Total ,Status DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) DeleiveryDate FROM Abc WHERE Column Name = Fieldname AND (DelivaryDate BETWEEN @FromDate AND @ToDate) GROUP BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status ) x pivot ( SUM(Total) for Status in (' + @cols + ') ) p ' execute(@query)