eliminate duplicate rows

• mikejohn-460140

  Grasshopper

  Points: 21

  More actions

  July 3, 2007 at 2:21 pm

  #177326

  i have 2 rows with same data but in that last column have different data like first one is      xxxx

                                                                                                     second one is      yyyy

  what is my question is i need to display the data like

  one row and last column with comma  like xxxx,yyyy

  give me Suggestions 

  thanks

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  July 17, 2007 at 10:15 pm

  #720256

  Give me Info on how the two rows relate to each other

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Ravikumar Pasumarthi

  SSC Rookie

  Points: 34

  More actions

  July 18, 2007 at 8:06 am

  #720373

  Hi..... I can suggest an approach for this. 

  Let your table be TABLE_A with data as below:

  Column_1    Column_2    Column_3

  1               A               XXX

  1               A               YYY

  2               B               XXX

  2               B               YYY

  Now you want the result as......

  Column_1    Column_2    Column_3

  1                A              XXX, YYY

  2                B              XXX, YYY

  The following steps can be performed to get the desired result.

  Declare @Result table (SNo int identity(1, 1), column_1 int, Column_2 varchar(100), Column_3 varchar(500))

  Declare @Loop int                        Set @Loop = 1

  Declare @ColResult varchar(500)    Set @ColResult = ''

  Declare @Col1Value int

  Declare @Col2Value varchar(100)

  Insert into @Result (Column_1, Column_2)

  Select distinct Column_1, Column_2 from TABLE_A

  While (@Loop < (Select Max(SNo) from @Result))

  Begin

            Select @Col1Value = Column_1, @Col2Value = Column_2 from TABLE_A where SNo = @Loop

            Select @ColResult =  @ColResult + ', ' + (Select Column_3 from TABLE_A where Column_1 = @Col1Value and Column_2 = @Col2Value)

            Update @Result set Column_3 = @ColResult where SNo = @Loop

  End

  The @Result will be as desired.

  I use this approach frequently to get my results.

  If there is any other approach with better performance, please do let me know.