Very Complex Cross Tab Query - 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)

Very Complex Cross Tab Query

Author

Topic

Alvin_SQL007
Starting Member

12 Posts

Posted - 2014-10-30 : 16:28:05

Hi all I have a table(#Temp1) with following structure as my source.

CREATE TABLE #TEMP1 ([BatchMonthYear] varchar(20),PymntMonthYear varchar(20),PaymentAmount int )
INSERt INTO #TEMP1 VALUES ('oct-12','oct-12',230)
INSERt INTO #TEMP1 VALUES ('sep-12','oct-12',150)
INSERt INTO #TEMP1 VALUES ('sep-12','sep-12',220)
INSERt INTO #TEMP1 VALUES ('aug-12','oct-12',1500)
INSERt INTO #TEMP1 VALUES ('aug-12','sep-12',2150)
INSERt INTO #TEMP1 VALUES ('aug-12','aug-12',768)
INSERt INTO #TEMP1 VALUES ('jul-12','aug-12',170)
INSERt INTO #TEMP1 VALUES ('jun-12','sep-12',150)
INSERt INTO #TEMP1 VALUES ('jun-12','oct-12',567)

select * from #TEMP1

How can i get a T-SQL queries output as following

CREATE TABLE #TEMP2 ([BatchMonthYear] varchar(20), Month1 int,Month2 int,Month3 int, Month4 int, Month5 int)
INSERT INTO #TEMP2 VALUES ('oct-12', 230,0,0,0,0)
INSERT INTO #TEMP2 VALUES ('sep-12', 220,150,0,0,0)
INSERT INTO #TEMP2 VALUES ('aug-12', 768,2150,1500,0,0)
INSERT INTO #TEMP2 VALUES ('jul-12', 0,170,0,0,0)
INSERT INTO #TEMP2 VALUES ('jun-12', 0,0,0,150,567)

select * from #TEMP2

------------------------------------------------------------

Also to add to the problem number of months across rows and columns is not fixed. user enters parameters (start date and number of months) to decide that. the fixed thing is if the user enters 13 months then both rows and columns will have the same months.

Any help is appreciated. i tried loads of things including Pivot/Unpivot but not been able to resolve this issue.

Thanks,
Alvin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-30 : 20:31:10

[code]
select BatchMonthYear
, isnull([oct-12],0) Month1
, isnull([sep-12],0) Month2
, isnull([aug-12],0) Month3
, isnull([jul-12],0) Month4
, isnull([jun-12],0) Month5

from #temp1 t
pivot (max(PaymentAmount) for pymntMonthYear in ([oct-12], [sep-12], [aug-12], [jul-12], [jun-12]) ) p
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources