Post reply

? How would one flatten three tables into one

  • 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...)

  • February 1, 2008 at 9:55 am

    #774317

    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:

  • February 1, 2008 at 10:09 am

    #774325

    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.

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

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