CODEWITHSUNDEEP
sql-serverreporting-services
akd
akd

Reputation: 6740

GROUP BY to flatten each group member in a separate column

I am trying to generate a report in SSRS.

I have 2 tables as below:

Address table:

AddressId | AddressLine

AddressCountEachMonth

ID | AddressId | Date | Count

For each date(Year-Month) there is an entry in AddressCountEachMonth table with the count value.

What I would like to do is to be able to query AddressCountEachMonth to output the result as below

For example If I provide a start date: 2014-01-01 and and date: 2014-05-01

Query result should be:

Address | 2016-01 | 2016-02 | 2016-03 | 2016-04 | 2016-05| 

x            5          1         0       2          4
y            2          3         4       0          2
...
...

is there any function in SQL Server that would help? I looked into STUFF but could not generate the result.

Upvotes: 0

Views: 489

Answers (1)

Joe C
Joe C

Reputation: 3993

Luckily SSRS provides the ability to pivot dynamically so you will not have to hard code a query or build dynamic sql. Check out this article that shows step by step how to do this.

https://msdn.microsoft.com/en-us/library/ms157334%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

Another good one:

https://www.simple-talk.com/sql/reporting-services/advanced-matrix-reporting-techniques/

Upvotes: 1

Related Questions