Query help?
hi all,
declare @sid varchar(10) set @sid = '2301001001' create table #temp ( sid varchar(10), bid varchar(10), pid varchar(10), dt datetime, est varchar(8000) ) insert into #temp values ('2301001001', 'hour', 's21', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'hour', 's21', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'hour', 's21', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 's') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 's') insert into #temp values ('2301001001', 'min', 's22', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'min', 's22', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'min', 's22', '2006-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'hour', 's21', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'hour', 's21', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'hour', 's21', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'hour', 's22', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'hour', 's22', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'hour', 's22', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'min', 's21', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'min', 's21', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'min', 's21', '2006-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'min', 's22', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'min', 's22', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'min', 's22', '2006-01-01 00:00:00.000', 'dh') select * from #temp where sid = @sid order by bid, pid drop table #temp
expected o/p:-
sid bid pid dt est 2301001001 hour s21 2005-01-01 00:00:00.000 a, d, dh 2301001001 hour s22 2005-01-01 00:00:00.000 a, d, dh, s 2301001001 min s21 2006-01-01 00:00:00.000 a, d, dh, s 2301001001 min s22 2006-01-01 00:00:00.000 a, d, dh
help?
thanks
kumar
kg
try
declare @sid varchar(10) set @sid = '2301001001' create table #temp ( sid varchar(10), bid varchar(10), pid varchar(10), dt datetime, est varchar(8000) ) insert into #temp values ('2301001001', 'hour', 's21', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'hour', 's21', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'hour', 's21', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001001', 'hour', 's22', '2005-01-01 00:00:00.000', 's') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001001', 'min', 's21', '2006-01-01 00:00:00.000', 's') insert into #temp values ('2301001001', 'min', 's22', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001001', 'min', 's22', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001001', 'min', 's22', '2006-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'hour', 's21', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'hour', 's21', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'hour', 's21', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'hour', 's22', '2005-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'hour', 's22', '2005-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'hour', 's22', '2005-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'min', 's21', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'min', 's21', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'min', 's21', '2006-01-01 00:00:00.000', 'dh') insert into #temp values ('2301001002', 'min', 's22', '2006-01-01 00:00:00.000', 'a') insert into #temp values ('2301001002', 'min', 's22', '2006-01-01 00:00:00.000', 'd') insert into #temp values ('2301001002', 'min', 's22', '2006-01-01 00:00:00.000', 'dh') select t.sid, t.bid, t.pid, t.dt, stuff((select ', ' + est from #temp t1 where t1.bid = t.bid and t1.dt = t.dt and t1.pid = t.pid and t1.sid = t.sid order by est for xml path('')),1,2,'') as est from #temp t where t.sid = @sid group by t.sid, t.bid, t.pid, t.dt order by bid, pid drop table #temp
premature optimization root of evil in programming. (c) donald knuth
naomi nosonovsky, sr. programmer-analyst
my blog
SQL Server > Transact-SQL
Comments
Post a Comment