Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2006
    Posts
    54

    Unanswered: Create Single Line Table from Multiple Line Tables

    I have a two tables

    One contains 1 record per Customer and has Fields to Contain the last 15 Sales.

    The Other is a multiple line table containing Customer ID and Sales Details for a single sale.

    I want to populate the 15 Fields in the single like table with data from the 15 rows (if they exist).

    I have an SSIS package that does it through multiple Merge Joins - Wondering if there was a better way to do it within a SQL Statement?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    By a long way the better way would be not to do it.

    How come you are doing this? What is the end goal? Not "to get 15 rows in to one row and fifteen columns" but something like "We run a weekly report of 15 most recent sales...." or "my boss needs to know....".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2006
    Posts
    54
    Possibly, but unforunately it needs to be in single table, single line format for submission to local government.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - yes I sympathise. Thank **** I no longer have to supply QUANGO & government data sets.

    What version SQL Server? Is this only 15 columns, or is it 15 most recent sales in (say) 30, or 45, or 60 columns?

    In SQL 2005+ there is a more efficient method than lots of joins.

    BTW - I don't use SSIS. What is a Merge Join in SSIS? I'm just curious as there is no logical merge join in T-SQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is in 2008:
    MERGE (Transact-SQL)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If this is for a file that is going to be sent somewhere, I would write up a script to flatten the records out as they are written to the file.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman View Post
    There is in 2008:
    MERGE (Transact-SQL)
    MERGE ain't a join. Tsk. Tsk. & Thrice Tsk.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I have to agree with MCrowley, and pootleflump (before he folded his hand).

    The basic data table structure should, from its original design, make a fundamental attempt at good database design.

    You can always transform your data and output-it any which way you want, but the original data design should be reasonably "correct."

    The 15-column design is not a reasonably correct design.

    I am sure the governmental agency is not mandating how you design your database. They are simply mandating that, when you hand the data to us, it should be in 'this' format.

    If my understanding is correct, then you should design the database properly and transform the data to whatever the government agency wants.

    My two cents.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump View Post
    Ah - yes I sympathise. Thank **** I no longer have to supply QUANGO & government data sets.

    What version SQL Server? Is this only 15 columns, or is it 15 most recent sales in (say) 30, or 45, or 60 columns?

    In SQL 2005+ there is a more efficient method than lots of joins.

    BTW - I don't use SSIS. What is a Merge Join in SSIS? I'm just curious as there is no logical merge join in T-SQL.
    Quote Originally Posted by PracticalProgram View Post
    I have to agree with MCrowley, and pootleflump (before he folded his hand).

    The basic data table structure should, from its original design, make a fundamental attempt at good database design.

    You can always transform your data and output-it any which way you want, but the original data design should be reasonably "correct."

    The 15-column design is not a reasonably correct design.

    I am sure the governmental agency is not mandating how you design your database. They are simply mandating that, when you hand the data to us, it should be in 'this' format.

    If my understanding is correct, then you should design the database properly and transform the data to whatever the government agency wants.

    My two cents.
    SQL 2008

    We have our own DB Design, just need to develop an extract table for submission.

    15 Columns in the Submission Table, 1 Column Many rows in the source 'Sales' table

    Basically I'm taking a relational database and trying to create a flat file output

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah, I don't consider this unreasonable. It is not an OLTP table. Whether or not this is a persisted table or a view (which is a table too) is something for the OP to decide. I think the straight-to-file requirement is caught between best principles. If this was to be presented in a report I would certainly recommend the UI handle this, but it seems overkill to create a UI to produce the pivot.

    Something like:
    Code:
    SELECT  some_cols
          , sales_one   =   MAX(
                                CASE 
                                    WHEN last_fifteen = 1 THEN
                                        sales_date
                                END)
          , sales_two   =   MAX(
                                CASE 
                                    WHEN last_fifteen = 2 THEN
                                        sales_date
                                END)
    FROM    --enumerated sales
            (
                SELECT  some_cols
                      , sales_date
                      , last_fifteen    = ROW_NUMBER()  OVER    (PARTITION BY   pk_column
                                                                 ORDER BY       sales_date DESC)
                FROM    dbo.theTable
            ) AS sales
    WHERE   last_fifteen    BETWEEN 1 AND 15
    GROUP BY some_cols
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    No one said anything about a UI. Just a simple VB script will do. In pseudocode:
    Code:
    rs = select regular vertical recordset here ordered by some key
    set currentkey = rs.key
    while !rs.EOF
     while currentkey = rs.key
      write record to file + ","
      rs.movenext
     loop
     write newline character
     set currentkey = rs.key
    loop

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No that's true and I nearly went back and changed it...but I couldn't be fecked.

    What do you see as the advantage of that over T-SQL WD24601?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It used to be that formats of these files were significantly more complex requiring header and footer rows. Those are much easier to create with an extract script.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh gosh yes - I remember adding footer rows for government submissions.

    In that case yes, I would defo agree. T-SQL sucks!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •