need to the data by making month names as columns

Question

Hi All,

    I am using SQLServer 2014.  Below is my sample data. And i want to populate the data in SSRS Report. 

CREATE TABLE #tmpdata(LocationName VARCHAR(100),Total Decimal(15,2),Paidon VARCHAR(3),mnth TINYINT(2))
insert into #tmpdata values('loc1',7434.50,'Jun',6)
go
insert into #tmpdata values('loc2',2736.00,'Apr',4)
go
insert into #tmpdata values('loc3',13760.00,'Apr',4)
go
insert into #tmpdata values('loc3',2720.00,'Jun',6)
go
insert into #tmpdata values('loc4',800.00,'Mar',3)
go
insert into #tmpdata values('loc4',27623.00,'Apr',4)

Location  Jan   Feb   Mar       Apr       May    Jun     Jul  Aug  Sep  Oct  Nov  Dec
loc1                                                         7434.50
loc2                                   2736.00
loc3                                   13760.00        2720.00
loc4                        800.00 27623.00

Please guide me on this.

Answer 1

i want to populate the data in SSRS Report. 

In a SSRS Report it's pretty easy, you can use a Matrix Report to get such a Pivot view, see Tutorial: Creating a Matrix Report (Report Builder)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Like this

SELECT location,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM
(
SELECT LocationName AS location,Total,Paidon
FROM #tmpdata
)t
PIVOT (SUM(Total) FOR Paidon IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]))p

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

I have done with matrix before request. Here the problem is in all months data may not exist. By based on the result i wont get all the months. Can you suggest the approach.

Answer 4

Hi Visakh,

  As usual worked like charm. I know that it can be achieved with Pivot query. you guided me in that way. I am learning the things from you experts. Thank you all for your support. 

Answer 5

If that was the answer, please mark it as the answer.

Answer 6

You can prepare a full month name list and left join your main query to make a full list. You can use this modified query to use matrix if you don't want to hard code your pivot table.