Selecting one column into several based on condition

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 2000 Forums

SQL Server Development (2000)

Selecting one column into several based on condition

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-05 : 07:30:53

Emil writes "Dear SQL Team, I am hoping that can help me.
I am relatively new to the sql and this the task I need to perform. I need to move data to the different columns based on the values. This a payroll application where based on the earnings code I have to move data into certain columns. This is the "best" I came up with. Thank You for your help.
declare @temptable table (File# varchar(50), Code1 varchar(10), Amt1 money,
Code2 varchar(10), Amt2 money,
Code3 varchar(10), Amt3 money,
Code4 varchar(10), Amt4 money,
Code5 varchar(10), Amt5 money)

insert @temptable
select File#, max(case Earnings3Code when 'B' then Earnings3Code else Null end),
Sum(case Earnings3Code when 'B' then Earnings3Amount else Null end),
max(case Earnings3Code when 'i' then Earnings3Code else Null end),
Sum(case Earnings3Code when 'i' then Earnings3Amount else Null end),
max(case Earnings3Code when 'p' then Earnings3Code else Null end),
Sum(case Earnings3Code when 'p' then Earnings3Amount else Null end),
max(case Earnings3Code when 'v' then Earnings3Code else Null end),
Sum(case Earnings3Code when 'v' then Earnings3Amount else Null end),
max(case Earnings3Code when 'x' then Earnings3Code else Null end),
Sum(case Earnings3Code when 'x' then Earnings3Amount else Null end)
from EPIANG02

Group by File#
Order by File#
insert @temptable
select File#, max(case Earnings3Code when 'Z' then Earnings3Code else Null end),
Sum(case Earnings3Code when 'Z' then Earnings3Amount else Null end),
max(case Earnings3Code when '4' then Earnings3Code else Null end),
Sum(case Earnings3Code when '4' then Earnings3Amount else Null end),
max(case Earnings3Code when '5' then Earnings3Code else Null end),
Sum(case Earnings3Code when '5' then Earnings3Amount else Null end),
max(case Earnings3Code when '13' then Earnings3Code else Null end),
Sum(case Earnings3Code when '13' then Earnings3Amount else Null end),
max(case Earnings3Code when '19' then Earnings3Code else Null end),
Sum(case Earnings3Code when '19' then Earnings3Amount else Null end)

from EPIANG02

Group by File#
Order by File#

insert @temptable
select File#, max(case Earnings3Code when '22' then Earnings3Code else Null end),
Sum(case Earnings3Code when '22' then Earnings3Amount else Null end),
max(case Earnings3Code when '23' then Earnings3Code else Null end),
Sum(case Earnings3Code when '23' then Earnings3Amount else Null end),
max(case Earnings3Code when '26' then Earnings3Code else Null end),
Sum(case Earnings3Code when '26' then Earnings3Amount else Null end),
max(case Earnings3Code when '27' then Earnings3Code else Null end),
Sum(case Earnings3Code when '27' then Earnings3Amount else Null end),
max(case Earnings3Code when '29' then Earnings3Code else Null end),
Sum(case Earnings3Code when '29' then Earnings3Amount else Null end)

from EPIANG02

Group by File#
Order by File#

insert @temptable
select File#, max(case Earnings3Code when '30' then Earnings3Code else Null end),
Sum(case Earnings3Code when '30' then Earnings3Amount else Null end),
max(case Earnings3Code when '35' then Earnings3Code else Null end),
Sum(case Earnings3Code when '35' then Earnings3Amount else Null end),
max(case Earnings3Code when 'Y' then Earnings3Code else Null end),
Sum(case Earnings3Code when 'Y' then Earnings3Amount else Null end),
null ,null,null,null

from EPIANG02

Group by File#
Order by File#
delete from @TempTable where Code1 is Null and Amt1 is Null and
Code2

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-06 : 04:36:52

U can try this, hope it suits ur requirement

select
File#,
Earnings3Code,
Sum(Earnings3Amount)
from @EPIANG02
Group by File#,Earnings3Code

Ganesh.V
Net AssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-06 : 04:38:45

Sorry Im my reply i have used the table variable, so kindly try this one.

select
File#,
Earnings3Code,
Sum(Earnings3Amount)
from EPIANG02
Group by File#,Earnings3Code

Ganesh.V
Net AssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources