CASE statement for column substitution

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » CASE statement for column substitution

CASE statement for column substitution

Rate Topic

Display Mode

Topic Options

Author

Message

Boris-460517

Posted Wednesday, September 02, 2009 11:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 26, 2010 6:00 PM
Points: 4, Visits: 36

Hello,

Thank you in advance for looking into this for me.

I have a select statement from which based on the value in a column, I need to populate a different column.

For example, I am trying to do this, populate the JAN, FEB... columns with the amount, based on the month value in the PERIOD column. In other words, pivot from column Period to Jan,Feb...etc

select lastname,
case period
when 'Jan' then amount as JAN
when 'Feb' then amount as FEB
and so on...

What would be the correct syntax for this ?...

Thank you for your help.

Boris.

Post #781650

Lamprey13

Posted Wednesday, September 02, 2009 11:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:22 PM
Points: 285, Visits: 1,575

As far as I know you'd have to return N number of separate columns or use dynamic sql. But, if you are trying to do a pivot you might be able to take advantage of the PIVOT operator.

Post #781660

Boris-460517

Posted Wednesday, September 02, 2009 12:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 26, 2010 6:00 PM
Points: 4, Visits: 36

Thank you very much for your reply.

I am still curious to know if there is a way to do this with a CASE statement and avoid the CURSOR route. This must be possible to do directly in T-SQL somehow ?? ...and I would love to get that syntax right... couldn't find anything on the web...

I will also investigate your suggestion....

Thanks again.

Post #781702

Lamprey13

Posted Wednesday, September 02, 2009 12:31 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:22 PM
Points: 285, Visits: 1,575

If you wanna post some sample data and expected output, I'm sure we can help you come to a reasonable solution:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #781720

Boris-460517

Posted Wednesday, September 02, 2009 2:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 26, 2010 6:00 PM
Points: 4, Visits: 36

Hi,

Thank you for sharing your valuable time with me on this, it is greatly appreciated.

Here is what I am trying to do ..

-- Build target table
CREATE TABLE [dbo].[_tbl_Target](
[ACCOUNT_NAME] [varchar](50) NOT NULL,
[JAN] [numeric](10, 2) NOT NULL,
[FEB] [numeric](10, 2) NULL,
[MAR] [numeric](10, 2) NULL,
[APR] [numeric](10, 2) NULL,
[MAY] [numeric](10, 2) NULL,
[JUN] [numeric](10, 2) NULL,
[JUL] [numeric](10, 2) NULL,
[AUG] [numeric](10, 2) NULL,
[SEP] [numeric](10, 2) NULL,
[OCT] [numeric](10, 2) NULL,
[NOV] [numeric](10, 2) NULL,
[DEC] [numeric](10, 2) NULL
)

-- Build source table
CREATE TABLE [dbo].[_tbl_Source](
[ACCOUNT_NAME] [varchar](50) NOT NULL,
[PERIOD_NAME] [varchar](50) NOT NULL,
[AMOUNT] [numeric](10, 2) NOT NULL,
)

-- Enter some data in the _tbl_source
insert into _tbl_Source select '1234','Jan',100
insert into _tbl_Source select '1234','Jan',50
insert into _tbl_Source select '1234','Feb',120
insert into _tbl_Source select '5678','Oct',100
insert into _tbl_Source select '5678','Nov',200

-- This statement works populating Jan column only to test ...

INSERT INTO _tbl_Target (account_name,Jan)
SELECT ACCOUNT_NAME,sum(Amount)
FROM _tbl_source
group by account_name

-- This statement is what I would like to get working... or something like that

INSERT INTO _tbl_Target (account_name,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
SELECT ACCOUNT_NAME,
CASE Period_name
when 'Jan' then sum(AMOUNT) as JAN else 0 AS JAN
when 'Feb' then sum(AMOUNT) as FEB else 0 AS FEB
when 'Mar' then sum(AMOUNT) as MAR else 0 AS MAR
when 'Apr' then sum(AMOUNT) as APR else 0 AS APR
when 'May' then sum(AMOUNT) as MAY else 0 AS MAY
when 'Jun' then sum(AMOUNT) as JUN else 0 AS JUN
when 'Jul' then sum(AMOUNT) as JUL else 0 AS JUL
when 'Aug' then sum(AMOUNT) as AUG else 0 AS AUG
when 'Sep' then sum(AMOUNT) as SEP else 0 AS SEP
when 'Oct' then sum(AMOUNT) as OCT else 0 AS OCT
when 'Nov' then sum(AMOUNT) as NOV else 0 AS NOV
when 'Dec' then sum(AMOUNT) as DEC else 0 AS DEC
END -- case
FROM _tbl_source
group by account_name

Post #781799

Lamprey13

Posted Wednesday, September 02, 2009 2:27 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:22 PM
Points: 285, Visits: 1,575

I see what you want to do now.. Try something like this:

INSERT INTO _tbl_Target (account_name,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
SELECT 
	ACCOUNT_NAME,
	SUM(CASE WHEN Period_name = 'Jan' then AMOUNT else 0 END) AS JAN,
	SUM(CASE WHEN Period_name = 'Feb' then AMOUNT else 0 END) AS FEB,
	...
	SUM(CASE WHEN Period_name = 'Dec' then AMOUNT else 0 END) AS [DEC]
FROM 
	_tbl_source 
group by 
	account_name

Post #781810

Boris-460517

Posted Wednesday, September 02, 2009 2:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 26, 2010 6:00 PM
Points: 4, Visits: 36

Thank you so very much !!!!...

This works perfectly !... Exactly as I needed.

Post #781815

« Prev Topic | Next Topic »

Permissions