SQL: Concatenate the rows based on Category and group by

• bonk6399

  SSC Enthusiast

  Points: 120

  More actions

  January 10, 2019 at 7:17 am

  #409426

  Hi Experts,

  I would like to concatenate the desc column group by Row_Desc with in the group by ID.   I am using SQL Server 2014

  ID                             Row_Desc                           Row_No                                   Desc
1                                  A                                           2                                              are you
1                                  A                                           1                                             Hi How
1                                  B                                           1                                           I am Good, Thank you
1                                  B                                           2                                           How are you doing?
2                                  A                                           2                                              Joe Tomorrow
2                                  A                                           1                                            We, Will meet
2                                  B                                           2                                          Will do
2                                  B                                           1                                           Sure

  I want the output like

  ID                          Row_Desc                 Desc
1                                  A                         Hi How are you
1                                  B                        I am good, Thank you, How are you doing?
2                                  A                         We will meet Joe Tomorrow
2                                  B                          Sure, Will do

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  January 10, 2019 at 8:38 am

  #2018549

  This will work.
  You can add extra CROSS APPLYs if needed...

  USE [tempdb]
GO

  CREATE TABLE [Source]
(
ID Int,
Row_Desc Char(1),
Row_No Int,
Descr Varchar(200)
)

  INSERT INTO [Source]
(ID, Row_Desc, Row_No, Descr)
VALUES
(1, 'A', 2, 'are you'),
(1, 'A', 1, 'Hi How'),
(1, 'B', 1, 'I am Good, Thank you'),
(1, 'B', 2, 'How are you doing?'),
(2, 'A', 2, 'Joe Tomorrow'),
(2, 'A', 1, 'We, Will meet'),
(2, 'B', 2, 'Will do'),
(2, 'B', 1, 'Sure')

  SELECT S.ID, S.Row_Desc, Descr = MAX(d1.Descr) + ' ' + MAX(d2.Descr)
FROM [Source] S
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_Desc AND Row_No = 1) d1 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_Desc AND Row_No = 2) d2 (Descr)
GROUP BY ID, Row_Desc
ORDER BY ID, Row_Desc

• bonk6399

  SSC Enthusiast

  Points: 120

  More actions

  January 10, 2019 at 9:06 am

  #2018555

  laurie-789651 - Thursday, January 10, 2019 8:38 AM

  This will work.
  You can add extra CROSS APPLYs if needed...

  USE [tempdb]
  GO

  CREATE TABLE [Source]
  (
  ID Int,
  Row_Desc Char(1),
  Row_No Int,
  Descr Varchar(200)
  )

  INSERT INTO [Source]
  (ID, Row_Desc, Row_No, Descr)
  VALUES
  (1, 'A', 2, 'are you'),
  (1, 'A', 1, 'Hi How'),
  (1, 'B', 1, 'I am Good, Thank you'),
  (1, 'B', 2, 'How are you doing?'),
  (2, 'A', 2, 'Joe Tomorrow'),
  (2, 'A', 1, 'We, Will meet'),
  (2, 'B', 2, 'Will do'),
  (2, 'B', 1, 'Sure')

  SELECT S.ID, S.Row_Desc, Descr = MAX(d1.Descr) + ' ' + MAX(d2.Descr)
  FROM [Source] S
  CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_Desc AND Row_No = 1) d1 (Descr)
  CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_Desc AND Row_No = 2) d2 (Descr)
  GROUP BY ID, Row_Desc
  ORDER BY ID, Row_Desc

  [/cod]

  Thank you Laurie, This is good if I have a couple of rows no's but what if I have more than 10 or 20. 

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  January 10, 2019 at 10:38 am

  #2018588

  You can add as many CROSS APPLYs as you need.  If this is no good, post a more accurate example of your data.

  
SELECT S.ID, S.Row_Desc, Descr = MAX(d1.Descr) + ' ' + MAX(d2.Descr)
                                    + MAX(d3.Descr) + ' ' + MAX(d4.Descr)
                                    + MAX(d5.Descr) + ' ' + MAX(d6.Descr)
FROM [Source] S
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 1) d1 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 2) d2 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 3) d3 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 4) d4 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 5) d5 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 6) d6 (Descr)
GROUP BY ID, Row_Desc
ORDER BY ID, Row_Desc

• bonk6399

  SSC Enthusiast

  Points: 120

  More actions

  January 10, 2019 at 10:40 am

  #2018589

  Fond the solution here
  https://www.sqlservercentral.com/Forums/Topic1260952-338-1.aspx

• bonk6399

  SSC Enthusiast

  Points: 120

  More actions

  January 10, 2019 at 12:20 pm

  #2018608

  laurie-789651 - Thursday, January 10, 2019 10:38 AM

  You can add as many CROSS APPLYs as you need.  If this is no good, post a more accurate example of your data.

  
SELECT S.ID, S.Row_Desc, Descr = MAX(d1.Descr) + ' ' + MAX(d2.Descr)
                                    + MAX(d3.Descr) + ' ' + MAX(d4.Descr)
                                    + MAX(d5.Descr) + ' ' + MAX(d6.Descr)
FROM [Source] S
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 1) d1 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 2) d2 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 3) d3 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 4) d4 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 5) d5 (Descr)
CROSS APPLY (SELECT Descr FROM [Source] WHERE S.ID = ID AND S.Row_Desc = Row_DEsc AND Row_No = 6) d6 (Descr)
GROUP BY ID, Row_Desc
ORDER BY ID, Row_Desc

  I have used the below code to achieve this

  SELECT A.ID ,A.Row_Desc ,
  Comments =
     STUFF ( ( SELECT ','+ B.Descr
      FROM Source B
      WHERE B.ID = A.ID
       AND B.Row_Desc = A.Row_Desc
      ORDER BY B.Row_No
      FOR XML PATH(''),TYPE
      ).value('.','VARCHAR(MAX)')
      , 1,1,SPACE(0))
FROM Source A
GROUP BY A.ID ,A.Row_Desc