Merge Duplicate values

• bicky1980

  SSCrazy

  Points: 2079

  More actions

  October 4, 2012 at 7:19 am

  #282687

  Hello All

  Can someone help me with the following:

  I need to take the duplicate rows and merge the telephone data onto the original record as additional columns.

  drop table temp

  create table temp(

  nameid nvarchar(10),

  telephone nvarchar(12))

  insert into temp

  values ('1','01234567890'),('1','01234567899'),('2','02345678998'),('2','02345678999'),('2','02345679000')

  The final table needs to look like

  1,01234567890,01234567899

  2,02345678998,02345678999,02345679000

  Thanks

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  October 4, 2012 at 7:43 am

  #1545532

  bicky1980 (10/4/2012)

  ---

  Hello All

  Can someone help me with the following:

  I need to take the duplicate rows and merge the telephone data onto the original record as additional columns.

  drop table temp

  create table temp(

  nameid nvarchar(10),

  telephone nvarchar(12))

  insert into temp

  values ('1','01234567890'),('1','01234567899'),('2','02345678998'),('2','02345678999'),('2','02345679000')

  The final table needs to look like

  1,01234567890,01234567899

  2,02345678998,02345678999,02345679000

  Thanks

  Strictly speaking it's impossible in RDBMS.

  Number of columns is the same for all rows in a table.

  So, you cannot have one row with 3 columns and another one with 4;-)

  What you could do instead, you may have all rows to have the same number of columns, but contain empty/null values in some of them. In a case, final table could look something like:

  1,01234567890,01234567899

  2,02345678998,02345678999,02345679000

  You can do it with Dynamic Cross-Tab query (search for example on this forum and you will find very good one :-)).

  Saying that you have not specified the "final" table DDL. So, from what you have posted someone may thing that you want just one column with comma separated values in it. If so, you can do this:

  select cast(nameid as varchar)+ (select ',' + telephone from #temp t2

  where t2.nameid = t1.nameid

  for xml path(''))

  from #temp t1

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• bicky1980

  SSCrazy

  Points: 2079

  More actions

  October 4, 2012 at 8:03 am

  #1545554

  Sorry, I wasn't very clear.

  The final table would have empty columns where there was less telephone numbers.

  I want each telephone number in a sperate column on the record e.g. telno1, telno2, telno3

  Thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 4, 2012 at 8:08 am

  #1545558

  Do you have another column in your existing table to identify what type of phone number it is? Mobile/work/home etc?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• bicky1980

  SSCrazy

  Points: 2079

  More actions

  October 4, 2012 at 8:31 am

  #1545582

  No they are all labelled telno

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 4, 2012 at 8:36 am

  #1545587

  SELECT

  nameid,

  telephone1 = MAX(CASE WHEN rn = 1 THEN telephone END),

  telephone2 = MAX(CASE WHEN rn = 2 THEN telephone END),

  telephone3 = MAX(CASE WHEN rn = 3 THEN telephone END),

  telephone4 = MAX(CASE WHEN rn = 4 THEN telephone END)

  FROM (SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY nameid ORDER BY telephone) FROM temp) d

  GROUP BY nameid

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• bicky1980

  SSCrazy

  Points: 2079

  More actions

  October 4, 2012 at 10:24 am

  #1545666

  Fantastic! Works Great!