Combine latest data based on ids

Question

I have the data this way in my table and i am trying to get data as below. I tried using coalese but having issues getting data for the latest.

Key             Hazards             DateTime
170021          Safety              2016-01-25 
170021          Concerns            2016-01-25
170021          Abuse               2016-01-25
252098          Financial           2016-10-28 
250606          Environmental       2016-10-26 
359287          food,utilities      2016-08-08 
409153          climate control     2016-06-24 
671881          None                2016-05-24 

Answer: Safety,Concerns,Abuse

Result Should be as above. What iam trying to do is get the key based on latest value of DateTime and if multiple records available for that Key concatenate them into a string and return back. If only single record just get that record back.

Help us help you - please share the table's structure and the sample data that should produe the result you're trying to get. — Mureinik, Feb 27 '17 at 14:24
Are you looking for something like this? http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — James Z, Feb 27 '17 at 14:35

score 1 · Answer 1 · answered Feb 27 '17 at 14:37

1

SQL Server 2016, you can use STRING_AGG

SELECT STRING_AGG([Hazards], ',')
FROM MyTable
GROUP BY [Key]

answered Feb 27 '17 at 14:37

gbn

422,506
82
585
676

Like this approach but we are not on Sql 2016 so it wont work for us. – user3038399 Feb 27 '17 at 15:13

score 1 · Answer 2 · answered Feb 27 '17 at 14:40

Is this helpful for you?

;WITH testtable([Key],Hazards,[DateTime])AS(
    SELECT '170021','Safety',CONVERT(DATE,'2016-01-25') UNION all
    SELECT '170021','Concerns','2016-01-25' UNION all
    SELECT '170021','Abuse','2016-01-25' UNION ALL
    SELECT '170021','Abuse','2016-01-24' UNION ALL
    SELECT '170021','Abuse2','2016-01-23' UNION ALL
    SELECT '170021','Abuse3','2016-01-22' UNION ALL
    SELECT '170021','Abuse4','2016-01-21' UNION all
    SELECT '252098','Financial','2016-10-28' UNION all
    SELECT '250606','Environmental','2016-10-26' UNION all
    SELECT '359287','food,utilities','2016-08-08' UNION all
    SELECT '409153','climate control','2016-06-24' UNION all
    SELECT '671881','None','2016-05-24'
)
SELECT DISTINCT t.[Key],t.DateTime,STUFF(c.Hazards,1,1,'') AS Hazards FROM (
    SELECT *,RANK()OVER(PARTITION BY [Key] ORDER BY DateTime DESC) AS rn FROM testtable
) AS t 
CROSS APPLY(SELECT ','+tt.Hazards FROM testtable AS tt WHERE tt.[Key]=t.[Key] AND DATEDIFF(d,tt.DateTime,t.DateTime)=0 FOR XML PATH('')) AS c(Hazards)
WHERE rn=1

Key    DateTime   Hazards
------ ---------- --------------
170021 2016-01-25 Safety,Concerns,Abuse
250606 2016-10-26 Environmental
252098 2016-10-28 Financial
359287 2016-08-08 food,utilities
409153 2016-06-24 climate control
671881 2016-05-24 None

score 0 · Answer 3 · answered Feb 27 '17 at 14:44

Using while loop in stored procedures

Declare @date_1 char(10)
Declare @cnt int=1
Declare @aktual_cnt int
Declare @Haz nvarchar(max)=''
Declare @Haz_1 nvarchar(max)=''

select *
into test_table_1
from test_table -- Enter your table name

set @date_1=(select top 1 DateTime from test_table_1
order by Datetime )

SET @aktual_cnt=(select count(*) from test_table_1 where DateTime=@date_1)

 while (@cnt<=@aktual_cnt)
 begin
if (@cnt=1)
  begin
    set @Haz_1=(select top 1 Hazards from test_table_1 where DateTime=@date_1)
    set @Haz = @Haz_1
    delete from test_table_1 where DateTime=@date_1 and Hazards=@Haz_1
  end
else  
  begin
    set @Haz_1=(select top 1 Hazards from test_table_1 where DateTime=@date_1)
    set @Haz = @Haz + ','+@Haz_1
    delete from test_table_1 where DateTime=@date_1 and Hazards=@Haz_1
  end


SET @cnt=@cnt+1


end
drop table test_table_1
select @Haz

Combine latest data based on ids

3 Answers3