? How would one flatten three tables into one

Question

? How would one flatten three tables into one

Wesley Norton

SSCrazy

Points: 2827
More actions
February 1, 2008 at 9:49 am

#378325

Let me start by apologizing for not following the proper rules for posting, I'm not able to post the code.
That said, what I have are three tables, one for earnings, one for taxes, and the other for deductions used to create a report in which all three are listed on the same line, might look something like this:
Earnings: Taxes: Deductions:
100.50 82.39 2.50
But the data has to be taken from three seperate tables. What I am doing now is grabbing all the people for the table, and assigning them 20 rows (should never be more than 20 lines), basically the same row duplicated with a row number column of 1-20. The three previously mentioned tables are then assigned row numbers 1-n (never more than 20) and then combined in a join statement. Lastly all rows that contain nulls for all three columns are deleted. It's a rather convoluded query and I have it working, but I want to see if there is a better way to do this. (Hopefully faster too...)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Y, sorry dude... you said it best in your post that you can't post the code. That means you realize that there's really no way for us to help you w/o more specifics.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Wesley Norton SSCrazy Points: 2827 More actions · Answer 2

There is a way to help, but I realize it's not as easy as just fixing my code. I suppose I could post the 865 lines of code that are doing this, but you probably wouldn't take the time to read through it. On the other hand I suppose I could just make something that looked like what I am doing... One sec

-- employee data with seqno (table that contains one column with 20 rows 1-20)

insert into #temptable

select name, address, id, seqno from employeedata,sequenceno

isnert into #earnings

select id,earningcode,curamt, row_number() Row_number() over (PARTITION BY earnings.ID ORDER BY earnings.curamt DESC) AS seqno

from earnings where employeedata.id = earnings.id

insert into #taxes

select id,taxcode,curamt, row_number() Row_number() over (PARTITION BY taxes.ID ORDER BY taxes.curamt DESC) AS seqno

from taxes where employeedata.id = taxes.id

insert into #deductions

select id,deductioncode,curamt, row_number() Row_number() over (PARTITION BY deductions.ID ORDER BY deductions.curamt DESC) AS seqno

from deductions where employeedata.id = earnings.id

-- I realize that the code below is wrong, but you get the idea

insert into #finaltable

select * from #temptable inner join (earnings inner join taxes inner join deductions) on id = id

where earningcode is not null and taxcode is not null and deductioncode is not null

I was hoping that someone may have a theory to doing this, just trying to learn from some others that have more knowledge on this topic. Thank you for your reply.