Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Question Unanswered: Combining information from multiple records into one

    Hi,

    I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.

    My table looks like this:
    ID - DayNr - Transportation - TransOrder - Route
    25 - 1 - Car - 1 - Text A
    25 - 1 - Train - 1 - Text B
    25 - 1 - Train - 2 - Text C
    25 - 7 - Train - 1 - Text D
    25 - 7 - Train - 2 - Text E

    I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:

    Column 1 - Column 2
    25/1/Car - Text A
    25/1/Train - TextB;TextC
    25/7/Train - TextD;TextE

    I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?

    Thanks.
    RMG

    P.S. ID is not my primary key and doesn't have to be unique

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You can use a simple while loop to iterate over the record and insert the records into an output table. If the values for the combination of columns in your source set changes, then insert a new row into the table, and if the values are identical, then append the value from the other column to the corresponding column's value in the new table.

    Code:
    create a table to represent the output
    select source data from the database
    
    while (more records)
    {
    
    if the current column combination is different 
    to the previous one, insert a new row 
    into the output table.
    
    if the current column combination is equal 
    to the previous one, append the value of 
    columnX to the corresponding value of the
    current row in the output table.
    
    move to next record;
    
    }
    
    display the output to the user, for to not do so, 
    would defeat the purpose of writing the function.
    
    Send an email to your director of IT asking why 
    such a function was even requested ;)
    Regards,
    Last edited by r123456; 11-21-07 at 07:11.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2007
    Posts
    2
    This looks completely logically to me. But it would be very helpfull if you could translate some of this logic into SQL for me. Because that is where I have the problem, not so much the necessary steps I need to follow.

    And I am afraid I have to ask the last question to myself. As I am the one who wants to concatenate the information and put it in another table. And the answer is quite simple: This way I don't have to copy and paste information from thousands of records by hand.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Three options.

    Robert's way.
    UDF to which you pass the data that uniquely identifies your output row and that returns string of delimited values.
    If there are a finite (and manageable) number of values for Route then a CASE statement (note- this will give you n columns for Route where n is the number of unique values).

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I was going to suggest the third option and provide an example using the CASE statement, but I wasn't certain if the number of rows to be transformed, and thus the number of columns required to produce, would be of a small enough size to make this approach practical. I believe you would need to code one case statement per possible column. For this to work you will need to assign some kind of unique numbering to the rows, so that each case statement to represent a column, knows what row to extract. Also, you will need to collapse the rows after applying the concatenation of your new columns, populated by the CASE statement, in order to remove the gaps.

    Taking these factors into consideration, I felt that the iterative approach I described earlier would provide you with the simplest and quickest implementation.

    Though of course, they are only the considerations that I was able to ascertain from reading your post.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r123456
    Also, you will need to collapse the rows after applying the concatenation of your new columns, populated by the CASE statement, in order to remove the gaps.
    Nah - just use MAX() - I think the BoL 2000 CASE entry demonstrates exactly this. Thinking about it, I guess one might use PIVOT in 2005 in lieu of the CASE statements.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Wouldn't you still need to collapse the rows after having projected the MAX() values of each column, for example by using the GROUP BY.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well yes.

    Code:
    SELECT    col_1
            , col_2
            , something            = MAX(CASE WHEN [route] = 'something'        THEN [route] END)
            , anotherThing        = MAX(CASE WHEN [route] = 'anotherThing'    THEN [route] END)
            , andSoOn            = MAX(CASE WHEN [route] = 'andSoOn'            THEN [route] END)
    FROM    dbo.mytable    
    GROUP BY col_1
            , col_2

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I didn't intend to be pedantic, I just thought the poster should be aware that the result set would need to be "flattened".
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah beg your pardon - I wasn't sure whether or not you were asking questions on your behalf or the OPs. I thought your range extended well beyond this .

  11. #11
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Select Col1+col2 As Merged_col
    From ...


    ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ortho
    Select Col1+col2 As Merged_col
    From ...


    ?
    That handles Column 1 in the OPs desired results but look more carefully at column 2 - that's the tricky bit

  13. #13
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I think you will be able to get the result set you want doing something like this.

    Code:
    CREATE PROC sp_TrickySelect
    AS
    DECLARE @str varchar(8000), @tot int, @l int, @search varchar(1000), @select varchar(8000), @count int, @l2 int, @str_part varchar(1000)
    
    SELECT ([ID]+'/'[DayNr]+'/'+[Transportation]) AS [Column1], [TransOrder], [Route]
    INTO ##tmpTbl1
    FROM .....
    
    SELECT DISTINCT([Column1])
    INTO ##tmpTbl2
    FROM  ##tmpTbl1
    
    SET @tot = SELECT COUNT(*) FROM ##tmpTbl2
    SET @l = 1
    
    WHILE (@l<=@tot)
    BEGIN
      SET @search = SELECT TOP 1 [Column1] FROM ##tmpTbl2
      SET @count = SELECT COUNT([Route]) FROM ##tmpTbl1 WHERE [Column1]=@search
        SET @l2 = 1
        WHILE (@l2<=@count)
        BEGIN
          SELECT @str_part = SELECT TOP 1 [Route] FROM ##tmpTbl1 WHERE [Column1]=@search ORDER BY [TransOrder] ASC
          SELECT @str = @str+' '+@str_part
          DELETE ##tmpTbl1 WHERE [Column1]=@search AND [TransOrder]=(SELECT MIN [TransOrder] FROM ##tmpTbl1 WHERE [Column1]=@search)
          SET @l2 = @l2 +1
        END
        INSERT INTO tmpTbl ([Column1], [Column2]) VALUES (@search,@str)
        DELETE FROM ##tmpTbl2 WHERE [Column1]=@search
        SET @l = @l+1
    END 
    
    SELECT [Column1], [Column2] FROM tmpTbl
    NB: I wrote this directly on this website and I didn't test it.

    Hope it can helps you out.

    Regards

    Or Tho
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  14. #14
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    If you use the Code from my previous post you gonna have to create a table tmpTable at the begining of the proc and drop it at the end OR create it and TRUNCATE it at the begining of the proc...
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  15. #15
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I am compelled to vehemently advise against using a solution similar to the one described above. Of course, these views are intended to discuss the merits of the solution, and in no way are to be interpreted as being a reference to the author.

    Ortho,

    While your approach may indeed work, in comparing it with other solutions available, namely those developed in SQL, it is unnecessarily complex and bloated.

    Not only is the length of your solution a reason to outright dismiss it, but it also consumes a large number of resources and the use of programming constructs that are disproportionate to the complexity of the problem. These include temp tables, while loops, conditional constructs, string manipulation functions, and finally, individual SQL statements.

    Your approach is almost exclusively procedural in nature and as such, should be considered only, and only when ,a more elegant and often performance friendly set based solution is not available. Often an experienced SQL Developer can develop a solution using many times fewer resources and lines of code than what would otherwise b developed by a procedural coder. This is not to say that one skill set is more valuable than the other, but instead it serves to highlight the difference present in the mindsets of these two developers, and how their differing perspectives are suited for specific kinds of problems.

    Generally procedural coders find it immensely difficult to develop efficient code for the manipulation and retrieval of data, in other words, developing code that works with data. I know that myself, I find it often difficult to see the benefits in OO programming for anything related to data. It's just a different way of viewing a problem.

    As you can see by comparing your solution to that posted earlier in this thread, the number of explicitly programmed steps is much greater in your solution. This can increase the risk of errors being introduced during development and maintenance of the code, which is obviously, is a risk that developers and managers should strive to minimize.

    Finally, your solution ignores the intent of the family of languages (unfortunately I cannot remember the exact term at the time of writing), of which SQL is indeed widely known, in developing a level of abstraction between the intent of a function and the internal representation of how it will be performed. In other words, the goal of SQL and other similar languages, is to focus on expressing the problem in terms of what needs to be done and not so much on how to do it.

    To apply these principles to the problem posted by the original poster, we can see that an SQL solution can be developed in only a fraction of the lines of code and with no explicit declarations of variables or inclusion of procedural programming constructs.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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