Help with query matrix format - SQL Server Forums

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)

Help with query matrix format

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-05-29 : 06:35:28

Hi ,

I have a table Temp and the data as follows.

Create table Temp
(
PAN nvarchar(100),
Month int,
Year int,
inputDate datetime,
Program nvarchar(10)
)

Insert into Temp Values('123456',1,2011,'2011-01-01','P1')
Insert into Temp Values('456798',1,2011,'2011-01-02','P1')
Insert into Temp Values('789456',1,2011,'2011-01-03','P1')
Insert into Temp Values('456123',1,2011,'2011-01-04','P1')
Insert into Temp Values('147258',1,2011,'2011-01-05','P1')
Insert into Temp Values('258147',1,2011,'2011-01-06','P1')
Insert into Temp Values('369258',1,2011,'2011-01-07','P1')

Insert into Temp Values('123456',2,2011,'2011-02-01','P1')
Insert into Temp Values('456798',2,2011,'2011-02-02','P1')
Insert into Temp Values('789456',2,2011,'2011-02-03','P1')
Insert into Temp Values('159265',2,2011,'2011-02-04','P1')
Insert into Temp Values('487159',2,2011,'2011-02-05','P1')

Insert into Temp Values('123456',3,2011,'2011-03-01','P1')
Insert into Temp Values('456798',3,2011,'2011-03-02','P1')
Insert into Temp Values('159265',3,2011,'2011-03-03','P1')
Insert into Temp Values('487159',3,2011,'2011-03-04','P1')
Insert into Temp Values('745874',3,2011,'2011-03-05','P1')

Insert into Temp Values('123456',4,2011,'2011-04-06','P1')
Insert into Temp Values('456798',4,2011,'2011-04-07','P1')
Insert into Temp Values('487159',4,2011,'2011-04-08','P1')
Insert into Temp Values('745874',4,2011,'2011-04-09','P1')
Insert into Temp Values('841526',4,2011,'2011-04-10','P1')
Insert into Temp Values('365847',4,2011,'2011-04-11','P1')

I would like output in matrix format(Month wise in rows and columns of matrix)

The repeated PAN's in each month
Newly added pans of each month ( That does not exists in previous months)

JAN-2011 FEB-2011 MAR-2011 APR-2011
JAN-2011 7 3 2 2
FEB-2011 2 2 1
MAR-2011 1 1
APR-2011 1

In the above scenario,

In Jan-2011 ---- 7 pans exists out of which 3 pans repeated in Feb-2011 and 2 Pans repeated in March and 2 pans repeated in Apr 2011.

In Feb 2011 --- 2 newly added pans(That does not exists in Jan) out of which 2 Pans repeated in March and 1 pan repeated in Apr 2011.

In Mar 2011 --- 1 newly added pan(That does not exists in Jan and Feb) out of which 1 pan repeated in Apr 2011.

In Apr 2011 -- 1 Newly added Pan(That does not exists in Jan, Feb and Mar).

Kindly let me know the procedure to achieve this.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:15:28

looks like a reporting requirement to me
Much easier to build this format in reporting tools like SSRS using matrix containers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources