locked
How to create single row from multiple row with same value? RRS feed

  • Question

  • User-353733005 posted

    I've a table with same value multiple time in a row. Now I want to show the same value only one time using sql query. 

    Here is my table:

    Sentence Languages
    In depth knowledge of and experience with C#
    In depth knowledge of and experience with ASP.NET
    Solid experience with HTML
    Solid experience with CSS

    Now I want this into this form:

    Sentence Languages
    In depth knowledge of and experience with

    C#, ASP.NET

    Solid experience with

    HTML, CSS

    Can anyone help please. 

    Wednesday, December 27, 2017 7:25 AM

Answers

  • User-707554951 posted

    Hi Shibly,

    Working code as below:

    create table mytable (id int identity(1,1), Sentence varchar(50),   Languages varchar(10))
    insert into mytable values ('In depth knowledge of and experience with','C#')
    insert into mytable  values ('In depth knowledge of and experience with','ASP.NET')
    insert into mytable  values ('Solid experience with','HTML')
    insert into mytable  values ('Solid experience with','CSS')
    
    
    --Replace space in your column with a special character and remove it in your select statement
    UPDATE  mytable
    SET Languages=REPLACE(Languages,' ','|')
    
    
    
    SELECT Sentence, REPLACE(Units,'|', ' ')  as Units
    
    
    FROM (SELECT t1.Sentence,
           Units =REPLACE( (SELECT Languages AS [data()]
               FROM mytable t2
              WHERE t2.Sentence = t1.Sentence
              ORDER BY Languages
                FOR XML PATH('')
                ), ' ', ',')
          FROM mytable t1
          GROUP BY Sentence)
          t0 ;
                

    Output:

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 27, 2017 9:37 AM

All replies

  • User-707554951 posted

    Hi Shibly,

    Working code as below:

    create table mytable (id int identity(1,1), Sentence varchar(50),   Languages varchar(10))
    insert into mytable values ('In depth knowledge of and experience with','C#')
    insert into mytable  values ('In depth knowledge of and experience with','ASP.NET')
    insert into mytable  values ('Solid experience with','HTML')
    insert into mytable  values ('Solid experience with','CSS')
    
    
    --Replace space in your column with a special character and remove it in your select statement
    UPDATE  mytable
    SET Languages=REPLACE(Languages,' ','|')
    
    
    
    SELECT Sentence, REPLACE(Units,'|', ' ')  as Units
    
    
    FROM (SELECT t1.Sentence,
           Units =REPLACE( (SELECT Languages AS [data()]
               FROM mytable t2
              WHERE t2.Sentence = t1.Sentence
              ORDER BY Languages
                FOR XML PATH('')
                ), ' ', ',')
          FROM mytable t1
          GROUP BY Sentence)
          t0 ;
                

    Output:

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 27, 2017 9:37 AM
  • User77042963 posted
     SELECT t1.Sentence,
           Units =  STUFF((SELECT ','+ Languages  
               FROM mytable t2
              WHERE t2.Sentence = t1.Sentence
              ORDER BY Languages
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
          FROM mytable t1
          GROUP BY Sentence 
    Wednesday, December 27, 2017 4:20 PM