September 2, 2009 at 11:15 am
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.
September 2, 2009 at 11:29 am
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.
September 2, 2009 at 12:10 pm
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.
September 2, 2009 at 12:31 pm
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/
September 2, 2009 at 2:08 pm
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
September 2, 2009 at 2:27 pm
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
September 2, 2009 at 2:39 pm
Thank you so very much !!!!...
This works perfectly !... Exactly as I needed.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply