14,207,285 members
See more:
Hi, i am trying to do an SQL query for get a data for a line chart where i need to show the trends of the data, here is the sql query i came up with:
```SELECT
material
,   sum(CASE WHEN MONTH(date_in) = 1 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "JAN 2009"
,   sum(CASE WHEN MONTH(date_in) = 2 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "FEB 2009"
,   sum(CASE WHEN MONTH(date_in) = 3 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "MAR 2009"
,	sum(CASE WHEN MONTH(date_in) = 4 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "APR 2009"
FROM rec_stats
GROUP BY material
ORDER BY material```

the table is something like this:
`|  material |  amount | date_in | user |`

the user is irrelevant right now,

basically, i need to get the result to be like this:
```material | jan 2009 | feb 2009 | .......... | dec 2010 | jan 2011 | ..........
1        |   120    |    0     | .......... |   200    |   0      | .........
2        |   0      |   300    | ..........................................
.
.
.```

but as you can see, i need to define the grouping one by one, and this isn't really possible when i don't know when is the time range of the data...

i will be using C# for the implementation, and i wonder if there is anyway to generate the string based on the existing data?
Posted
Updated 22-Apr-11 6:02am
v2

## Solution 1

If you can ease up on the requirement of how exactly the data will look, the problem is quite simple. You'll need to rewrite your query to be something like:

```SELECT
material,
SUM([Amount]) AS [Amount],
RIGHT(CONVERT(varchar(50), [date_in], 106), 8) AS [Date]
FROM
[rec_stats]
GROUP BY
material,
YEAR([date_in]),
MONTH([date_in])
ORDER BY
material,
[date_in]```

You'll get data in the following format:

`material | amount | date`

From there, you'll just have to pivot the data in C# to a form you can use. Aside from the above, the other options you have would be:

-Dynamic SQL (i.e. SQL that writes SQL statements and executes it)
-SQL Pivot[^] - I don't consider this much different than Dynamic SQL, since you have to define what each column is, but I just wanted to state it.
v2
BlackJack99 22-Apr-11 11:23am

thanks for the interest.
do you mind explaining what "RIGHT(CONVERT(varchar(50), [date_in], 106), 8) AS [Date]" is for? i am not too familiar with this statement,
wouldnt it make some "holes" for months that dont have any entry in its result?

how does it differ from this statement?
SELECT material, YEAR(date_in) AS 'Year', MONTH(date_in) AS 'Month', SUM(amount) AS "Total"
FROM rec_stats
GROUP BY material, YEAR(date_in), MONTH(date_in)
ORDER BY material, YEAR(date_in), MONTH(date_in)
Andrew Rissing 22-Apr-11 21:27pm

Assuming I did it correctly without executing it, it should result in a date string of the format "JAN 2009". You can break it up as you have in what you provided. It just depends on what you want it to be. I tried to get it as close as to the original output you were looking for.
BlackJack99 23-Apr-11 2:38am

yes, it did result in what you mentioned, but it doesnt return the records for months that dont have any entry, for example, in case of there is no record with the record of MAY 2010, i need it to return 0 as the amount instead of not returning anything,

or are you suggesting to do the data in another format? if that is the case, i would really appreciate it if you would explain more about it.. thank you..
Andrew Rissing 23-Apr-11 16:42pm

The two options you have if you want that feature is to fill in the gaps in C# or create a SQL stored procedure that will provide a table of all the dates you need as a result to join up with. You would take the list of all the dates you need and LEFT JOIN it with data provided in the query above.
BlackJack99 23-Apr-11 23:06pm

thanks... i think i get the idea now... thank you very much!
Sandeep Mewara 22-Apr-11 12:16pm

My 5!

## Solution 2

Can you try something like this:

`GROUP BY dateadd(month, datediff(month, 0, date_in), 0)`

That will group the stats by each month I believe. I can't test if this works, I don't have month spanning data right now.
v2
BlackJack99 22-Apr-11 11:35am

Hi, mind explaining more? i dont really understand how does it work (or even where to put it),
it calculate the difference between the month of the date and 0, and then add it with 0? how does this would group each stats by the month and fill the entry-less months with 0?

Top Experts
Last 24hrsThis month
 Patrice T 50 W∴ Balboos 25 RickZeeland 20 OriginalGriff 20 Wonde Tadesse 15