SQL查询图表图表 [英] SQL Query For Chart Diagram

问题描述

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





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美元

< pre lang =HTML>我想要什么

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，< span class =code-digit> 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)

这篇关于SQL查询图表图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！