Dynamic SQL - 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 2005 Forums

Transact-SQL (2005)

Dynamic SQL

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-21 : 12:46:05

I'm back

and got the cross tabs to work thanks again!

Could you help me generate my year columns dynamically?

Thanks!


SELECT Sort,Reg,
 SUM(CASE WHEN MONTH(DowrDt)=9 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Sep 08],
 SUM(CASE WHEN MONTH(DowrDt)=10 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Oct 08],
 SUM(CASE WHEN MONTH(DowrDt)=11 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Nov 08],
 SUM(CASE WHEN MONTH(DowrDt)=12 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Dec 08],
 SUM(CASE WHEN MONTH(DowrDt)=1 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Jan 09],
 SUM(CASE WHEN MONTH(DowrDt)=2 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Feb 09],
 SUM(CASE WHEN MONTH(DowrDt)=3 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Mar 09],
 SUM(CASE WHEN MONTH(DowrDt)=4 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Apr 09],
 SUM(CASE WHEN MONTH(DowrDt)=5 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [May 09],
 SUM(CASE WHEN MONTH(DowrDt)=6 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Jun 09],
 SUM(CASE WHEN MONTH(DowrDt)=7 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Jul 09],
 SUM(CASE WHEN MONTH(DowrDt)=8 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Aug 09],
 SUM(CASE WHEN MONTH(DowrDt)=9 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Sep 09],
 SUM(CASE WHEN MONTH(DowrDt)=10 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Oct 09],
 SUM(CASE WHEN MONTH(DowrDt)=11 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Nov 09],
 SUM(CASE WHEN MONTH(DowrDt)=12 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Dec 09]
FROM #tempintnc
GROUP BY sort,Reg
Order by sort, reg

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 13:39:35

If you have a Date table you could do something like:

DECLARE @Date TABLE (FullDate SMALLDATETIME)

INSERT @Date
SELECT '20080101' UNION ALL
SELECT '20080201' UNION ALL
SELECT '20080301' UNION ALL
SELECT '20080401' UNION ALL
SELECT '20080501' UNION ALL
SELECT '20080601' UNION ALL
SELECT '20080701' UNION ALL
SELECT '20080801' UNION ALL
SELECT '20080901' UNION ALL
SELECT '20081001' UNION ALL
SELECT '20081101' UNION ALL
SELECT '20081201' UNION ALL
SELECT '20090101' UNION ALL
SELECT '20090201' UNION ALL
SELECT '20090301' UNION ALL
SELECT '20090401' UNION ALL
SELECT '20090501' UNION ALL
SELECT '20090601' UNION ALL
SELECT '20090701' UNION ALL
SELECT '20090801' UNION ALL
SELECT '20090901' UNION ALL
SELECT '20091001' UNION ALL
SELECT '20091101' UNION ALL
SELECT '20091201' 


DECLARE @String VARCHAR(8000)

SET @String = 'SELECT Sort,Reg, ' + CHAR(13) + CHAR(10)

SELECT @String = @String + STUFF(
		(SELECT 
			',SUM(CASE WHEN MONTH(DowrDt)= ' 
			+ CAST(MONTH(FullDate) AS VARCHAR(2)) 
			+ ' AND YEAR(DowrDt)= ' 
			+ CAST(YEAR(FullDate) AS VARCHAR(4)) 
			+ ' THEN PctInt Else 0 END) AS [' 
			+ CONVERT(VARCHAR(3), FullDate, 9) + ' '
			+ RIGHT(CAST(YEAR(FullDate) AS VARCHAR(4)), 2) + '] '
			+ CHAR(10) 
		FROM @Date 
		WHERE FulLDate BETWEEN '20080901' AND '20091201'
		ORDER BY FullDate FOR XML PATH('')), 1, 1, '') 

SET @String = @String + 'FROM #tempintnc ' + CHAR(13) + CHAR(10) 
		+ 'GROUP BY sort,Reg ' + CHAR(13) + CHAR(10) 
		+ 'Order by sort, reg' + CHAR(13) + CHAR(10) 

SELECT @String

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-21 : 13:52:54

PERFECT!!!!! Thanks so much this is the best Forum to come to because you all are experts! I've learned so much.

Would you mind writing some comments next to the select statement so I can understand what you had done.

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-21 : 14:11:09

One thing I am running a 13 month trend so I would want 9/08 - 10/08 to appear on the report then next month I would want 10/08 - 11/08, does that make sense?

So instead of hardcoding those dates and having to update each year is there a way to use parameters?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 01:29:31

then i think you need to do a dynamic pivot. something like below


DECLARE @PeriodList varchar(max)

SELECT @PeriodList=LEFT(pl.PeriodList,LEN(pl.PeriodList)-1)
FROM (SELECT DISTINCT DATENAME(yyyy,DATEADD(mm,-1 * number,GETDATE()))+DATENAME(mm,DATEADD(mm,-1 * number,GETDATE()))+','
FROM master..spt_values
WHERE type='p'
AND number<=12
FOR XML PATH('')
)pl(PeriodList)


SELECT @Sql='
SELECT *
FROM
(
SELECT Sort,Reg,PctInt,DATENAME(yyyy,DowrDt)+ DATENAME(mm,DowrDt) AS Period
FROM YourTable
)m
PIVOT (SUM(PctInt) FOR Period IN ([' + REPLACE(@PeriodList,',','],[') + ']))p'

EXEC (@Sql)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-24 : 13:23:14

Thanks! Sorry for just getting to this. I tried it but I'm getting the error:

Must declare the scalar variable "@Sql".

This is my stored procedure with the pivot info added to it:


USE [iClaims]
GO
/****** Object:  StoredProcedure [dbo].[iClaimsNat]    Script Date: 11/24/2008 13:19:30 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[iClaimsNat]
@Rpt  char(1),
@Period  char(1) 
As
SET NOCOUNT ON
select ZipCd = Case when RSDNC_ZIP5 is Null then MAILG_ADDR_ZIP5
else RSDNC_ZIP5 end, convert(char,MonthDt,1) as DowrDt, INET_IND, COSSN
into #tempzip
from iClaims 
If @Rpt = '1' and @Period = 'M'
begin
Select Sort='1',Reg='NAT', a.DowrDt, iCnt, tCnt 
into  #tempint
From
 (select DowrDt, Count(COSSN) as iCnt
from  #tempzip
where INET_IND <> 'N'
Group by DowrDt
)a 
inner join
 (select DowrDt, Count(COSSN) as tCnt
from  #tempzip
Group by DowrDt
)b
on a.DowrDt = b.DowrDt
  
insert  #tempint
Select Sort =c.Region,Reg=c.Reg, c.DowrDt, iCnt, tCnt
From
(select Region, Reg, DowrDt, Count(COSSN) as iCnt 
from  #tempzip inner join ZipCodes on zipcd = zip
where INET_IND <> 'N'
Group by Region, Reg, DowrDt
 )c 
inner join
(select Region, Reg, DowrDt, Count(COSSN) as tCnt 
from  #tempzip inner join ZipCodes on zipcd = zip
Group by Region, Reg, DowrDt 
 )d 
on  c.DowrDt = d.DowrDt and c.Region = d.Region
end
   
DECLARE @PeriodList varchar(max)

SELECT @PeriodList=LEFT(pl.PeriodList,LEN(pl.PeriodList)-1)
FROM (SELECT DISTINCT DATENAME(yyyy,DATEADD(mm,-1 * number,GETDATE()))+DATENAME(mm,DATEADD(mm,-1 * number,GETDATE()))+','
FROM master..spt_values
WHERE type='p'
AND number<=12
FOR XML PATH('')
)pl(PeriodList)


SELECT @Sql='
SELECT *
FROM
(
SELECT Sort,Reg,PctInt,DATENAME(yyyy,DowrDt)+ DATENAME(mm,DowrDt) AS Period
FROM #tempzip
)m
PIVOT (SUM(PctInt) FOR Period IN ([' + REPLACE(@PeriodList,',','],[') + ']))p'

EXEC (@Sql)





drop table #tempzip
drop table #tempint

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-25 : 01:10:58

Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-26 : 07:23:39

Thanks but having a hard time setting this up...

How can I get my table to look like this with the dynamic pivot table with the months being generated across the top? Sorry don't understand the info you sent.

The fields that I need are Sort, Reg and DowrDt which is the month field.


Sort Reg Sept08 Oct08 Nov08
1    NAT 16.22 17.24 18.22
A    BOS 14.60 14.50 15.14

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources