Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Ask a query to make reports

Author  Topic 

lw1990
Yak Posting Veteran

85 Posts

Posted - 2017-08-07 : 11:34:00
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 Wal-Mart January2017 100
East Wal-Mart February2017 100
East Wal-Mart March2017 200
East Wal-Mart April2017 300
East Wal-Mart 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 Wal-Mart January2017 100
West Wal-Mart February2017 200
West Wal-Mart April2017 200
West Wal-Mart 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 March2017 April2017 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-Name January2017 February2017 March2017 April2017 May2017 ... 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

   

- Advertisement -