Trouble using Coalesce with Grouping

• Bill Marriott-486259

  Grasshopper

  Points: 19

  More actions

  November 27, 2010 at 5:04 pm

  #229568

  I am trying to use Coalesce to build a list of status bit values for a row grouped by device eg 1, 0, 1, 1 - meaning that there were 4 status events for this device and the second one failed.

  I can get this to work on a simple query but not with grouping or partitioning.

  Any ideas?

  Sample Code follows

  Thanks

  Bill

  CREATE TABLE [dbo].[tblDeviceStatus](

  [DeviceId] [int] NOT NULL,

  [CheckDate] [datetime] NOT NULL,

  [Status] [bit] NOT NULL

  ) ON [PRIMARY]

  GO

  insert into tblDeviceStatus values (4,'1/1/2010',1)

  insert into tblDeviceStatus values (5,'1/1/2010',1)

  insert into tblDeviceStatus values (4,'1/2/2010',1)

  insert into tblDeviceStatus values (5,'1/2/2010',0)

  insert into tblDeviceStatus values (4,'1/3/2010',1)

  insert into tblDeviceStatus values (5,'1/3/2010',1)

  select * from tbldevicestatus

  -- This very basic version works and gets me what I want - a delimited list of the status entries in date order = 1,0,1

  DECLARE @StatusHistory VARCHAR(1000)

  select @StatusHistory = COALESCE(@StatusHistory + ', ', '') + CAST(Status AS varchar(5))

  from tblDeviceStatus

  where DeviceId =5

  order by Checkdate

  select @StatusHistory as StatusList

  -- but the following grouped by device version( what I really need) does not work

  DECLARE @StatusHistory VARCHAR(1000)

  select DeviceId, (select @StatusHistory = COALESCE(@StatusHistory + ', ', '') + CAST(Status AS varchar(5))) as StatusList

  from tblDeviceStatus

  Group by DeviceId

  order by Checkdate

  /*

  Desired resultset

  DeviceIdStatusList

  4 1, 1, 1

  5 1, 0, 1

  */

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  November 28, 2010 at 3:11 am

  #1256011

  A commonly used trick to concatenate values of a column is to use a FOR XML PATH('') subquery, i.e.

  SELECT DISTINCT

  ds.DeviceId,

  -- Use STUFF to delete the first comma

  STUFF((SELECT

  ',' + CAST(Status AS CHAR(1))

  FROM

  tblDeviceStatus s

  WHERE

  s.DeviceId = ds.DeviceId

  FOR XML PATH(''))

  , 1, 1, '') StatusList

  FROM

  tblDeviceStatus ds

  Note that the above code only works properly with numeric data. If you have to concatenate a (N)(VAR)CHAR column you have to deal with entitization. Lookup entitization on this site for an explanation of that.

  Peter

• Bill Marriott-486259

  Grasshopper

  Points: 19

  More actions

  November 28, 2010 at 6:11 pm

  #1256079

  Thanks, That works like a champ!

  I don't yet understand why but I will learn.

  Thanks again

  Bill