SQL Server 2005 (onwards): SELECT * FROM sys.traces SQL Server 2000 : USE msdb SELECT * FROM fn_trace_getinfo(default); Ref: fn_trace_getinfo Column descriptions for sys.traces DMV can be found here: sys.traces
Use the DISTINCT keyword in a subquery to remove duplicates before combining the results: SQL Fiddle SELECT ProjectID ,STRING_AGG(value, ‘,’) WITHIN GROUP (ORDER BY value) AS NewField from ( select distinct ProjectId, newId.value FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],’;’) AS newID WHERE newID.value IN ( ‘O95833’ , ‘Q96NY7-2’ ) ) x GROUP BY ProjectID ORDER … Read more
Here is one way to do it. Since you want the distinct counts as well, it can be done simply by grouping the rows twice. The first GROUP BY will remove duplicates, the second GROUP BY will produce the final result. WITH Sitings AS ( SELECT * FROM (VALUES (1, ‘Florida’, ‘Orlando’, ‘bird’), (2, ‘Florida’, … Read more