Ask a query to make reports

Transact-SQL
1

Hello,
Please help me to query the records from a table and display the records in horizontally for a report.

I have a table with records in 4 fields:

Region Store Month_Year Number

East WalMart January2017 100
East WalMart February2017 100
East WalMart March2017 200
East WalMart April2017 300
East WalMart May2017 100
East Kmart February2017 200
East Kmart March2017 100
East Kmart May2017 200
East Target January2017 100
East Target February2017 200
East Target April2017 100
East Target May2017 300
West WalMart January2017 100
West WalMart February2017 200
West WalMart April2017 200
West WalMart May2017 400
West KMart January2017 300
West KMart March2017 200
West KMart April2017 100
West KMart May2017 200
West Target January2017 300
West Target February2017 200
West Target March2017 500
West Target May2017 200
North …
South …
… …

What I’m trying to display in report is group by region, Store and list Number by each Month_Year. Also, list the YTD Amount of the Store at the right column and Month total in the bottom of each Month like this:

East summary
Store-Name January2017 February2017 March2017April2017 May2017 ... YTD Amount

Wal-Mart	100	100	200	300	100	...	800
Kmart		    200	    100	200	...	500
Target	    100	200	100	    300	...	700
Month Total	200	500	300	400	600	...	2000

West Summary
Store-NameJanuary2017 February2017March2017April2017May2017...YTD Amount
~~
Wal-Mart	 100	200		       200	    400	...	900
Kmart	     300	 	  200	   100	    200	...	800
Target	     300	200	  500	 	        200	...	1200
Month Total	 700	400	  700	   300	    800	...	2900

North summary	(Same way... )
	
South summary	(Same way... )
...
Sorry, I can't display the format of database table in the poster here. But, 
Thank you in advance,

lw
2

It could be done like this:

declare @my1 nvarchar(255)=stuff((select ',sum(['
                                         +datename(month,dateadd(month,n,cast(dateadd(year,datediff(year,0,current_timestamp),0) as date)))
                                         +datename(year,current_timestamp)
                                         +']) as '
                                         +datename(month,dateadd(month,n,cast(dateadd(year,datediff(year,0,current_timestamp),0) as date)))
                                         +datename(year,current_timestamp)
                                    from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) as cte(n)
                                   where n<datepart(month,current_timestamp)
                                     for xml path('')
                                            ,type
                                 ).value('.','nvarchar(max)')
                                ,1,1,''
                                )
;

declare @my2 nvarchar(255)=stuff((select '+sum(isnull(['
                                        +datename(month,dateadd(month,n,cast(dateadd(year,datediff(year,0,current_timestamp),0) as date)))
                                        +datename(year,current_timestamp)
                                        +'],0))'
                                    from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) as cte(n)
                                   where n<datepart(month,current_timestamp)
                                     for xml path('')
                                            ,type
                                 ).value('.','nvarchar(max)')
                                ,1,1,''
                                )
;

declare @my3 nvarchar(255)=stuff((select ',['
                                         +datename(month,dateadd(month,n,cast(dateadd(year,datediff(year,0,current_timestamp),0) as date)))
                                         +datename(year,current_timestamp)
                                         +']'
                                    from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) as cte(n)
                                   where n<datepart(month,current_timestamp)
                                     for xml path('')
                                            ,type
                                 ).value('.','nvarchar(max)')
                                ,1,1,''
                                )
;

declare @sql nvarchar(max)='select case when grouping(store)=1 then ''Total'' else store end as [Store-Name]'
                          +      ','+@my1
                          +      ','+@my2+' as [YTD Amount]'
                          + ' from (select store,month_year,number from yourtable where region=''East'') as a'
                          +' pivot (sum(number) for month_year in ('+@my3+')) as p'
                          +' group by rollup(store)'
;

execute (@sql);
3

Thank you bitsmed for the query,
I've got the Store, month up to current month(August) and the YTD_Amount listed on the first row. This is the right format that I need to display in my report.
But, I couldn't get a value in each month for the "East' region. As I do have some values for store WallMart and Kmart in January and March. What I got is all 'Null' for 8 months in one store and in the total row.

NULL NULL NULL NULL NULL NULL NULL NULL 0.00000
NULL NULL NULL NULL NULL NULL NULL NULL 0.00000

Also, If I want to display West, North and South, where can I extend your query in @sql?

Your help is really appreciated.
lw

4

Actually I'd forgotten to evaluate on the year, so the last part should be:

declare @sql nvarchar(max)='select case when grouping(store)=1 then ''Total'' else store end as [Store-Name]'
                          +      ','+@my1
                          +      ','+@my2+' as [YTD Amount]'
                          + ' from (select store,month_year,number from yourtable'
                          +         ' where region=''East''' /* This is where you change region */
                          +           ' and month_year like ''%''+datename(year,current_timestamp)'
                          +       ') as a'
                          +' pivot (sum(number) for month_year in ('+@my3+')) as p'
                          +' group by rollup(store)'
;

When I run for East on the sample data you provided, I get:

Store-Name  January2017  February2017  March2017  April2017  May2017  June2017  July2017  August2017  YTD Amount
Kmart       NULL         200           100        NULL       200      NULL      NULL      NULL        500
Target      100          200           NULL       100        300      NULL      NULL      NULL        700
WalMart     100          100           200        300        100      NULL      NULL      NULL        800
Total       200          500           300        400        600      NULL      NULL      NULL        2000

For West, I get:

Store-Name  January2017  February2017  March2017  April2017  May2017  June2017  July2017  August2017  YTD Amount
KMart       300          NULL          200        100        200      NULL      NULL      NULL        800
Target      300          200           500        NULL       200      NULL      NULL      NULL        1200
WalMart     100          200           NULL       200        400      NULL      NULL      NULL        900
Total       700          400           700        300        800      NULL      NULL      NULL        2900
5

Thank you bitsmed for the quick reply and query,
Maybe the data type of the fields were wrong in my table. The Month_Year is varchar(16) and the Number is numeric(22, 5). My result is still Null and 0:
NULL NULL NULL NULL NULL NULL NULL NULL 0.00000
NULL NULL NULL NULL NULL NULL NULL NULL 0.00000
NULL NULL NULL NULL NULL NULL NULL NULL 0.00000
NULL NULL NULL NULL NULL NULL NULL NULL 0.00000

It looks like the Month_Year in your query is datatime. I don't know if it matters. Also, my table is actually a view which I queried from other tables and view. I don't know if the data from a view can be used in your query.

Thanks again,

lw

6

Only thing I can think of, is that you might have set up another language than english. The query generates the month_year with the month names in the language you have set up.
This can easily be tested by doing this before the "declare @my1 ....":

set language us_English;
7

Thank you bitsmed for all your helps, I've found the reason why the data was not queried out. As the month_year field is a varchar field, our real data has a space between month and year in that field. I've used function Replace in the view to fix the field and the query is working now.

Your helps are truly appreciated.

lw

8

I'm glad you got it to work :slight_smile:

9

Thank you bitsmed,
The query works great to display my monthly records and YTD values in my reports.

lw