CODEWITHSUNDEEP
sqlsql-serverxmlselectgraph
Sid
Sid

Reputation: 765

SIMPLE SQL SELECT INTO GRAPH

I am having trouble outputting an SQL Select Statement to my XML to be used for my Graph

I have these results

December 2011 470 FRESENIUS

January 2012 434 FRESENIUS

February 2012 278 FRESENIUS

February 2012 2 STORESID

I need to output them like this so I can loop my Code and generate the XML

Month Year FRESNIUS STORESID

December 2011 470 0

January 2012 434 0

February 2012 278 2

take note i did not include the headers, these are all column results. and also, STORESID and FRESNIUS are not STATIC Values. Multiple Storers Exists so I needed to Expand my number of Columns also , Dynamically.

I kinda need to output the second one to generate the XML properly thru PHP which I already have. Or are there any other proper way.

Thanks.

query below for the output i was talking about

SELECT  
        DATENAME(month, orderdate)  + ' ' + CAST(Year(orderdate) AS VARCHAR(4)) AS 'Month Year' ,
        count(*) 'Number of Orders', 
        storerkey
FROM    orders
GROUP BY
        storerkey,
        DATENAME(month, orderdate) + ' ' +  CAST(Year(orderdate) AS VARCHAR(4)),
        CAST(Year(orderdate) AS VARCHAR(4)) + RIGHT('0' + CAST(Month(orderdate) AS VARCHAR(2)),2)
ORDER BY    
        storerkey,
        CAST(Year(orderdate) AS VARCHAR(4)) + RIGHT('0' + CAST(Month(orderdate) AS VARCHAR(2)),2)

Upvotes: 0

Views: 930

Answers (3)

GarethD
GarethD

Reputation: 69769

I am not 100% certain of the XML output you need but you can take advantage of SQL-Servers FOR XML clause and PIVOT function. This should get you started:

CREATE TABLE #Test (GraphDate DATETIME, Value INT, Type VARCHAR(50))
INSERT #Test VALUES 
    ('01/12/2011', 470, 'FRESENIUS'),
    ('01/01/2012', 434, 'FRESENIUS'),
    ('01/12/2012', 278, 'FRESENIUS'),
    ('01/02/2012', 2, 'STORESID')

SELECT  DATEPART(YEAR, GraphDate) [Year], 
        DATENAME(MONTH, GraphDate) [Month], 
        ISNULL(FRESENIUS, 0) [FRESENIUS],
        ISNULL(STORESID, 0) [STORESID]
FROM    #Test
        PIVOT
        (   SUM(Value)
            FOR Type IN ([FRESENIUS], [STORESID])
        ) PivotTable
FOR XML PATH('row'), ROOT

DROP TABLE #Test

EDIT

The below is the basic query you have asked for based on the query you have given. 

SELECT  DATENAME(MONTH, OrderDate)  + ' ' + DATENAME(YEAR, OrderDate) [MonthYear], 
        ISNULL(FRESENIUS, 0) [FRESENIUS],
        ISNULL(STORESID, 0) [STORESID]
FROM    Orders
        PIVOT
        (   COUNT(StorerKey)
            FOR StorerKey IN ([FRESENIUS], [STORESID])
        ) PivotTable
ORDER BY  DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate)

EDIT 2

I don't think PIVOT is supported by SQL-Server 2000. You will need to use aggregate functions:

SELECT  DATENAME(MONTH, OrderDate)  + ' ' + DATENAME(YEAR, OrderDate) [MonthYear], 
        COUNT(CASE WHEN StorerKey = 'FRESENIUS' THEN 1 END) [FRESENIUS],
        COUNT(CASE WHEN StorerKey = 'STORESID' THEN 1 END) [STORESID]
FROM    Orders
GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate), DATENAME(YEAR, OrderDate), DATENAME(MONTH, OrderDate)
ORDER BY  DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate)

I think some FOR XML functionality is still available though so you may still be able to output your XML directly from SQL.

EDIT 3

DECLARE @SQL NVARCHAR(2000)
SELECT  @SQL = ISNULL(@SQL, '') + ', COUNT(CASE WHEN StorerKey = ''' + StorerKey + ''' THEN 1 END) [' + StorerKey + ']'
FROM    (   SELECT  DISTINCT StorerKey
            FROM    Orders
        ) Keys


SET @SQL = 'SELECT  DATENAME(MONTH, OrderDate) + '' '' + DATENAME(YEAR, OrderDate) [MonthYear]' + @SQL + 
            ' FROM    Orders
            GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate), DATENAME(YEAR, OrderDate), DATENAME(MONTH, OrderDate)
            ORDER BY  DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate)'

EXECUTE SP_EXECUTESQL @SQL

Upvotes: 1

Markus Jarderot
Markus Jarderot

Reputation: 89171

Based on your query:

SELECT
    DATENAME(MONTH, orderdate) + ' ' + CAST(YEAR(orderdate) AS VARCHAR(4)) AS [Month Year]
    COUNT(CASE storerkey WHEN 'FRESNIUS' THEN 1 END) AS FRESNIUS,
    COUNT(CASE storerkey WHEN 'STORESID' THEN 1 END) AS STORESID
FROM orders
GROUP BY YEAR(orderdate), MONTH(orderdate), DATENAME(MONTH, orderdate)
ORDER BY YEAR(orderdate), MONTH(orderdate)

Output:

Month Year      FRESNIUS   STORESID
December 2011        470          0
January 2012         434          0
February 2012        278          2

Upvotes: 1

Rory Hunter
Rory Hunter

Reputation: 3460

You could join the table to itself. If I make assumptions about the column names, assume that either the FRESNIUS or STORESID could be absent, and use common table expressions (untested!):

WITH date_fresnius AS (
    SELECT [Month Year], FRESNIUS
    FROM original_table
)
, date_storesid AS (
    SELECT [Month Year], STORESID
    FROM original_table
)
SELECT
      ISNULL( f.[Month Year], s.[Month Year]) AS [Month Year]
    , ISNULL( f.FRESNIUS, 0 ) AS FRESNIUS
    , ISNULL( s.STORESID, 0 ) AS STORESID
FROM
    date_fresnius f
    FULL OUTER JOIN date_storesid s ON (f.[Month Year] = s.[Month Year])

If you always have a FRESNIUS record you can use a LEFT JOIN instead.

Upvotes: 1

Related Questions