Find multiple status in one Column from different rows

• shamshad.ali

  Hall of Fame

  Points: 3850

  More actions

  September 5, 2013 at 12:06 am

  #279433

  I need to find the multiple relationship with a Customer in one row. It would be able to search based on given criteria. and return results like :

  Customer: Relations

  AA1: Friend, Relative

  AA2: Friend, Coleague

  AA3: Student

  here is the provided data code:

  CREATE TABLE [dbo].[Customers](

  [Id] [int] IDENTITY(1,1) NOT NULL,

  [CustomerName] [varchar](50) NOT NULL,

  [Status] [varchar](50) NULL,

  [AddedDatetime] [datetime] NULL,

  CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

  (

  [Id] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [DF_Customers_AddedDatetime] DEFAULT (getdate()) FOR [AddedDatetime]

  GO

  insert into dbo.Customers (CustomerName, [Status]) values ('AA1', 'Friend')

  insert into dbo.Customers (CustomerName, [Status]) values ('AA2', 'Friend')

  insert into dbo.Customers (CustomerName, [Status]) values ('AA1', 'Relative')

  insert into dbo.Customers (CustomerName, [Status]) values ('AA2', 'Coleague')

  insert into dbo.Customers (CustomerName, [Status]) values ('AA3', 'Student')

  select * from Customers

  please help.

  Shamshad Ali

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 5, 2013 at 1:56 am

  #1647605

  SELECT t1.CustomerName AS Customer,

  STUFF((SELECT ',' + t2.Status AS "text()"

  FROM dbo.Customers t2

  WHERE t2.CustomerName = t1.CustomerName

  ORDER BY t2.Status

  FOR XML PATH(''),TYPE).value('./text()[1]','varchar(1000)'),1,1,'') AS Relations

  FROM dbo.Customers t1

  GROUP BY t1.CustomerName

  ORDER BY t1.CustomerName;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537