Concatenate many rows in one AND group by ?

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  June 25, 2015 at 8:11 am

  #301975

  Hi

  I have a table with this sample data

  ParentObject| DeliveryMethod| DeliveryStatus| ContactMethod| ContactStatus| Other

  974224 |Other | true | Face to Face | true |skills

  974224 |Other | true |Collateral Contact| true | skills

  974238 |Concepts |true | Face to Face |true | NULL

  974238 |face |true | Face to Face |true | NULL

  974238 |Phone |true |Face to Face |true | NULL

  I would like to concatenate DeliveryMethod and ContactMethod and when Deliverymethod = 'Other' then use value in 'Other" field

  So my output would look like

  974224 | Skills | Face to Face, Collateral Contact

  974238 | concepts,face,Phone | Face to Face

  I have tried using STUFF but get the fields repeating for example

  I am using 2008r2

  Any ideas would be great, I'm confused at this point :unsure:

  Thanks

  Joe

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 25, 2015 at 8:19 am

  #1808083

  seems this is your second post on this subject.....you will probably get a better response if you provide the required create table / insert data statement that provide a readily usable script...you have been around here a bit am sure you know how

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• imex

  Ten Centuries

  Points: 1234

  More actions

  June 25, 2015 at 9:01 am

  #1808108

  Hi,

  Try something like this:

  SELECT

  a.ParentObject,

  STUFF( (SELECT DISTINCT

  ', ' + case when s.DeliveryMethod = 'Other'

  then s.Other

  else s.DeliveryMethod

  end

  FROM MyTable as s

  WHERE s.ParentObject = a.ParentObject

  FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

  ,1, 2, '') as DeliveryMethod,

  STUFF( (SELECT DISTINCT

  ', ' + s.ContactMethod

  FROM MyTable as s

  WHERE s.ParentObject = a.ParentObject

  FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

  ,1, 2, '') as ContactMethod

  FROM MyTable as a

  group by

  a.ParentObject

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  June 25, 2015 at 10:12 am

  #1808139

  Thanks imex,

  That worked exactly as I needed.

  Sorry for the bad explanation..

  Thanks AGain

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 25, 2015 at 10:21 am

  #1808146

  jbalbo (6/25/2015)

  ---

  Thanks imex,

  That worked exactly as I needed.

  Sorry for the bad explanation..

  Thanks AGain

  but doesnt this solution still give duplicates in "delivery method" and " contact method" ?

  I thought this was your problem.

  see code below......

  USE [tempdb]

  GO

  CREATE TABLE [dbo].[MyTable](

  [ParentObject] [int] NOT NULL,

  [DeliveryMethod] [varchar](50) NULL,

  [DeliveryStatus] [varchar](50) NULL,

  [ContactMethod] [varchar](50) NULL,

  [ContactStatus] [varchar](50) NULL,

  [Other] [varchar](50) NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[MyTable] ([ParentObject], [DeliveryMethod], [DeliveryStatus], [ContactMethod], [ContactStatus], [Other]) VALUES (974224, N'Other ', N' true ', N' Face to Face ', N' true ', N'skills')

  INSERT [dbo].[MyTable] ([ParentObject], [DeliveryMethod], [DeliveryStatus], [ContactMethod], [ContactStatus], [Other]) VALUES (974224, N'Other ', N' true ', N'Collateral Contact', N' true ', N' skills')

  INSERT [dbo].[MyTable] ([ParentObject], [DeliveryMethod], [DeliveryStatus], [ContactMethod], [ContactStatus], [Other]) VALUES (974238, N'Concepts ', N'true ', N' Face to Face ', N'true ', N' NULL')

  INSERT [dbo].[MyTable] ([ParentObject], [DeliveryMethod], [DeliveryStatus], [ContactMethod], [ContactStatus], [Other]) VALUES (974238, N'face ', N'true ', N' Face to Face ', N'true ', N' NULL')

  INSERT [dbo].[MyTable] ([ParentObject], [DeliveryMethod], [DeliveryStatus], [ContactMethod], [ContactStatus], [Other]) VALUES (974238, N'Phone ', N'true ', N'Face to Face ', N'true ', N' NULL')

  SELECT

  a.ParentObject,

  STUFF( (SELECT DISTINCT

  ', ' + case when s.DeliveryMethod = 'Other'

  then s.Other

  else s.DeliveryMethod

  end

  FROM MyTable as s

  WHERE s.ParentObject = a.ParentObject

  FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

  ,1, 2, '') as DeliveryMethod,

  STUFF( (SELECT DISTINCT

  ', ' + s.ContactMethod

  FROM MyTable as s

  WHERE s.ParentObject = a.ParentObject

  FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

  ,1, 2, '') as ContactMethod

  FROM MyTable as a

  group by

  a.ParentObject

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  June 25, 2015 at 3:00 pm

  #1808248

  Hi J Livingston SQL

  For some reason it doesn't. Unfortunately I don't do this as much as I'd like to.

  As I check the data , I am going to play around with what you sent also .

  Thanks for all the input.

  Joe

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 26, 2015 at 3:21 am

  #1808337

  oops...my bad ...sorry :blush:

  seems that the sample data I created for you had some leading spaces in the columns !!

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day