November 27, 2010 at 5:04 pm
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
*/
November 28, 2010 at 3:11 am
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
November 28, 2010 at 6:11 pm
Thanks, That works like a champ!
I don't yet understand why but I will learn.
Thanks again
Bill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply