June 12, 2009 at 1:00 pm
Greetings once again.
I am working on some sql to be used in a report. I have a transaction table called prtrxp. This table has about 100 fields. The fields I am currently interested in have names like de_code##1, de_code##2 all the way to de_code##18. Correspondlingly there are de_amt##1, de_amt##2 all the way to de_amt##18. I need to check each de_code## field to compre it to the string '401k'. I am trying to avoid 18 comparisions. What I was wondering, was if sql provided a way to do something like:
for i = 1 to 18 do
if de_code##i = '401k' then sum(de_amt##i)
anyway, thats the pseudo code. hopefully I am not going to be relegated to using a WHEN statement.
Thanks for any ideas.
June 12, 2009 at 1:04 pm
No. SQL doesn't do that. The reason is that tables really aren't supposed to be designed that way. The values should be in rows, not columns.
Since you're probably stuck with that design, you're going to have to do a Case When.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2009 at 1:33 pm
I would look into using pivot and unpibvot functions. You could unpivot the data so you only need to compare 1 column to the string.
Once you compare you can filter the data and the pivot it back out.
ps. this is a little easier to pick up on in ssis components than it is t-sql, but both work well and are cleaner then case statements
June 12, 2009 at 9:31 pm
darryl (6/12/2009)
Greetings once again.I am working on some sql to be used in a report. I have a transaction table called prtrxp. This table has about 100 fields. The fields I am currently interested in have names like de_code##1, de_code##2 all the way to de_code##18. Correspondlingly there are de_amt##1, de_amt##2 all the way to de_amt##18. I need to check each de_code## field to compre it to the string '401k'. I am trying to avoid 18 comparisions. What I was wondering, was if sql provided a way to do something like:
for i = 1 to 18 do
if de_code##i = '401k' then sum(de_amt##i)
anyway, thats the pseudo code. hopefully I am not going to be relegated to using a WHEN statement.
Thanks for any ideas.
If you were to generate the table creation script and attach it to a post, I believe I might be able to show you a simple way that would even handle missing columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2009 at 10:25 am
Unpivot doesn't work, because you have two series of columns, which should be unpivoted.
The following isn't nice, but it should do.
create table #tmp(
ID int
, DeCode nvarchar(10)
, SumDeAmt int
)
declare @SQLTemplate nvarchar(max)
set @SQLTemplate='
insert into #tmp (id,DeCode,SumDeAmt)
select ID, ''de_code{##}'' as de_code, SUM(de_amt{##}) as SumDeAmt
from prtrxp
where de_code{##}=''401k''
group by ID
'
declare @SQLCommand nvarchar(max)
declare @loopVar int
set @loopVar=1
while @loopVar<=18
begin
select @SQLCommand=REPLACE(@SQLTemplate,'{##}',CONVERT(nvarchar(10),@loopVar))
exec @SQLCommand
set @loopVar=@loopVar+1
end
select * from #tmp
drop table #tmp
Age2uN
devloping robust and performant databaseapplications with Microsoft SQL-Server
June 16, 2009 at 10:53 am
if the 2 series of columns have the same number of occurerences then that would be perfect for an SSIS unpivot task
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply