Case/decode in Loop - 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

General SQL Server Forums

New to SQL Server Programming

Case/decode in Loop

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2008-12-01 : 07:45:23

This is my Query. It decodes/Case a table turning it into a type of pivot. It is very long. Is there a way to create a loop with this. Looping the year from 2002 to 2020. Regards

SELECT T$ITEM,
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$1 ELSE 0 END) AS "2005 JANUARY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$2 ELSE 0 END) AS "2005 FEBUARY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$3 ELSE 0 END) AS "2005 MARCH",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$4 ELSE 0 END) AS "2005 APRIL",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$5 ELSE 0 END) AS "2005 MAY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$6 ELSE 0 END) AS "2005 JUNE",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$7 ELSE 0 END) AS "2005 JULY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$8 ELSE 0 END) AS "2005 AUGUST",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$9 ELSE 0 END) AS "2005 SEPT",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$10 ELSE 0 END) AS "2005 OCTOBER",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$11 ELSE 0 END) AS "2005 NOVEMBER",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$12 ELSE 0 END) AS "2005 DECEMBER",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$1 ELSE 0 END) AS "2006 JANUARY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$2 ELSE 0 END) AS "2006 FEBUARY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$3 ELSE 0 END) AS "2006 MARCH",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$4 ELSE 0 END) AS "2006 APRIL",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$5 ELSE 0 END) AS "2006 MAY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$6 ELSE 0 END) AS "2006 JUNE",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$7 ELSE 0 END) AS "2006 JULY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$8 ELSE 0 END) AS "2006 AUGUST",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$9 ELSE 0 END) AS "2006 SEPT",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$10 ELSE 0 END) AS "2006 OCTOBER",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$11 ELSE 0 END) AS "2006 NOVEMBER",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$12 ELSE 0 END) AS "2006 DECEMBER",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$1 ELSE 0 END) AS "2007 JANUARY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$2 ELSE 0 END) AS "2007 FEBUARY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$3 ELSE 0 END) AS "2007 MARCH",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$4 ELSE 0 END) AS "2007 APRIL",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$5 ELSE 0 END) AS "2007 MAY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$6 ELSE 0 END) AS "2007 JUNE",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$7 ELSE 0 END) AS "2007 JULY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$8 ELSE 0 END) AS "2007 AUGUST",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$9 ELSE 0 END) AS "2007 SEPT",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$10 ELSE 0 END) AS "2007 OCTOBER",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$11 ELSE 0 END) AS "2007 NOVEMBER",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$12 ELSE 0 END) AS "2007 DECEMBER",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$1 ELSE 0 END) AS "2008 JANUARY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$2 ELSE 0 END) AS "2008 FEBUARY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$3 ELSE 0 END) AS "2008 MARCH",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$4 ELSE 0 END) AS "2008 APRIL",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$5 ELSE 0 END) AS "2008 MAY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$6 ELSE 0 END) AS "2008 JUNE",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$7 ELSE 0 END) AS "2008 JULY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$8 ELSE 0 END) AS "2008 AUGUST",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$9 ELSE 0 END) AS "2008 SEPT",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$10 ELSE 0 END) AS "2008 OCTOBER",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$11 ELSE 0 END) AS "2008 NOVEMBER",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$12 ELSE 0 END) AS "2008 DECEMBER",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$1 ELSE 0 END) AS "2009 JANUARY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$2 ELSE 0 END) AS "2009 FEBUARY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$3 ELSE 0 END) AS "2009 MARCH",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$4 ELSE 0 END) AS "2009 APRIL",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$5 ELSE 0 END) AS "2009 MAY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$6 ELSE 0 END) AS "2009 JUNE",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$7 ELSE 0 END) AS "2009 JULY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$8 ELSE 0 END) AS "2009 AUGUST",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$9 ELSE 0 END) AS "2009 SEPT",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$10 ELSE 0 END) AS "2009 OCTOBER",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$11 ELSE 0 END) AS "2009 NOVEMBER",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$12 ELSE 0 END) AS "2009 DECEMBER"
FROM baandb.ttdinv750700
GROUP BY T$ITEM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 07:47:47

First question.
Why would you want all years and months as column?
Ins't it better to have al years as rows and all months as column?

E 12°55'05.63"
N 56°04'39.26"

Deon Smit
Starting Member

47 Posts

Posted - 2008-12-01 : 07:49:46

That is how it was. But I am creating a report. I need is as columns.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 07:55:41

A report with 228 columns?
Who will ever read it?

E 12°55'05.63"
N 56°04'39.26"

Deon Smit
Starting Member

47 Posts

Posted - 2008-12-01 : 07:57:29

Our Sales Dep. The Cut out the rest they don't want. And when I can loop it I will then look at getting only the last 12 Months.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 08:27:32

Use this and let the front-end application rename the columns since you know which year you passed to the stored procedure.

CREATE PROCEDURE dbo.usp_GetDataForYear
(
	@theYear SMALLINT
)
AS

SET NOCOUNT ON

SELECT		T$ITEM,
		SUM(T$AUPP$1) AS [JANUARY],
		SUM(T$AUPP$2) AS [FEBUARY],
		SUM(T$AUPP$3) AS [MARCH],
		SUM(T$AUPP$4) AS [APRIL],
		SUM(T$AUPP$5) AS [MAY],
		SUM(T$AUPP$6) AS [JUNE],
		SUM(T$AUPP$7) AS [JULY],
		SUM(T$AUPP$8) AS [AUGUST],
		SUM(T$AUPP$9) AS [SEPT],
		SUM(T$AUPP$10) AS [OCTOBER],
		SUM(T$AUPP$11) AS [NOVEMBER],
		SUM(T$AUPP$12) AS [DECEMBER]
FROM		baandb.ttdinv750700
WHERE		T$YEAR = @theYear
GROUP BY	T$ITEM

E 12°55'05.63"
N 56°04'39.26"

Deon Smit
Starting Member

47 Posts

Posted - 2008-12-01 : 08:55:45

I am sorry to ask you this. But how do I pass the year to the stored procedure. This is my First time working with this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:19:20

quote:
Originally posted by Deon Smit

I am sorry to ask you this. But how do I pass the year to the stored procedure. This is my First time working with this.

create stored procedure as Peso suggested and call it like this

EXEC dbo.usp_GetDataForYear @theYear=<your year value here>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources