Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Merging Rows SQL

    Hi guys

    I have what I hope is a simple problem to solve.

    If I have a table similar to below

    ID Field1 Field2 Field3
    01 Red
    01 Green
    01 Blue
    02 Blue
    02 Yellow
    02 Red

    How would I merge the records together to appear as below

    ID Field1 Field2 Field3
    01 Red Green Blue
    02 Blue Red Yellow


    Note there will never be 2 Field1's populated for the same ID and also there are many columns so ideally I dont want to have to reference field1 field 2 etc.

    Thanks in advance,

    Conor

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Effectively you are denormalising your data. Don't do this in the database.
    You do this in your presentation layer e.g. webpage, form, report, etc.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Thanks, but I don't have access to the presentation layer. I am working on the database side of a 3rd party application.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you have a finite number of columns to display then it can be done. If the number is not fixed you'll have to resort to ugly, ugly dynamic-sql.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2011
    Posts
    44
    It is a finite (approx 150) columns

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do this in your presentation layer, they are made to handle problems like this easily and cleanly. While it is possible to denormalize in the database layer, it is a bad idea based on almost every criteria you can use to evaluate it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Disclaimer:
    Re-read all the good reasons others have given you in previous posts to NOT proceed like this:

    Code:
    DROP TABLE DaSourceTable;
    CREATE TABLE DaSourceTable(
    	Id	int		NOT NULL,
    	Colour	VARCHAR(10)	NOT NULL
    )
    
    DROP TABLE DaHelpTable;
    CREATE TABLE DaHelpTable(
    	Id	int		NOT NULL,
    	Colour	VARCHAR(10)	NOT NULL,
    	RowNum	INT		NOT NULL
    )
    
    DROP TABLE DaTable;
    CREATE TABLE DaTable(
    	Id	int		NOT NULL,
    	Colour1	VARCHAR(10)	NULL,
    	Colour2	VARCHAR(10)	NULL,
    	Colour3	VARCHAR(10)	NULL,
    )
    
    INSERT INTO DaSourceTable(ID, Colour) VALUES
    ('1', 'Red'), 
    ('1', 'Green'), 
    ('1', 'Blue'), 
    ('2', 'Blue'), 
    ('2', 'Yellow'), 
    ('2', 'Red'),
    ('3', 'Ocre')
    
    
    SELECT * FROM DaSourceTable
    GO
    
    WITH CTE AS(
    SELECT	id, 
    	colour,
    	ROW_NUMBER () OVER (PARTITION BY Id ORDER BY Colour) as Rownum
    FROM DaSourceTable
    )
    INSERT INTO DaHelpTable (Id, Colour, RowNum)
    SELECT id, colour, RowNum
    FROM CTE
    
    SELECT * FROM DaHelpTable
    
    INSERT INTO DaTable (Id, Colour1)
    SELECT Id, Colour
    FROM DaHelpTable
    WHERE RowNum = 1
    ORDER BY Id
    
    UPDATE U
    SET Colour2 = H.Colour
    FROM DaTable as U
    	INNER JOIN DaHelpTable as H ON
    		U.Id = H.Id
    		AND H.Rownum = 2
    
    UPDATE U
    SET Colour3 = H.Colour
    FROM DaTable as U
    	INNER JOIN DaHelpTable as H ON
    		U.Id = H.Id
    		AND H.Rownum = 3
    
    SELECT * FROM DaTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by cocono1 View Post
    Hi guys

    I have what I hope is a simple problem to solve.

    If I have a table similar to below

    ID Field1 Field2 Field3
    01 Red
    01 Green
    01 Blue
    02 Blue
    02 Yellow
    02 Red

    How would I merge the records together to appear as below

    ID Field1 Field2 Field3
    01 Red Green Blue
    02 Blue Red Yellow
    You should use [CODE] tag to reduce misunderstandings for your requirements,
    like ...
    Code:
    ID     Field1 Field2 Field3
    01     Red
    01             Green
    01                      Blue
    02     Blue
    02                      Yellow
    02           Red
    and

    Code:
    ID     Field1 Field2 Field3
    01     Red   Green Blue
    02     Blue  Red    Yellow

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could also use my solution from http://www.dbforums.com/microsoft-sq...show-last.html but this is a presentation issue so it really ought to be handled in the presentation layer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is it too late to mention that this sort of thing is better done at the presentation layer?

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Note there will never be 2 Field1's populated for the same ID ...
    Considering the note, this might be an answer.
    Code:
    SELECT id
         , MAX(colour1) AS colour1
         , MAX(colour2) AS colour2
         , MAX(colour3) AS colour3
     FROM  source_table
     GROUP BY
           id
    ;

    By the way,
    I wonder how(e.g. from what source data? what tool/script/statement did you used? ...) did you populated the table, like the example...
    Code:
    ID     Field1 Field2 Field3
    01     Red
    01            Green
    01                   Blue
    02     Blue
    02                   Yellow
    02            Red
    with quoted note.

Tags for this Thread

Posting Permissions

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