code_junkie

How to get list of values in GROUP_BY clause

Nov 13th, 2011
149
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.70 KB | None | 0 0
  1. id data
  2. -- ----
  3. 1 1, 2, 3
  4. 2 4, 5
  5. 3 6, 4
  6.  
  7. CREATE FUNCTION [dbo].[GetDataForID]
  8. (
  9. @ID int
  10. )
  11. RETURNS varchar(max)
  12. AS
  13. BEGIN
  14. declare @output varchar(max)
  15. select @output = COALESCE(@output + ', ', '') + data
  16. from table
  17. where ID = @ID
  18.  
  19. return @output
  20. END
  21. GO
  22.  
  23. CREATE FUNCTION [dbo].[GetDataForID]
  24. (
  25. @ID int
  26. )
  27. RETURNS varchar(max)
  28. AS
  29. BEGIN
  30. declare @output varchar(max)
  31. select @output = COALESCE(@output + ', ', '') + data
  32. from table
  33. where ID = @ID
  34.  
  35. return @output
  36. END
  37. GO
  38.  
  39. SELECT ID, dbo.GetDataForID(ID) as Data
  40. FROM Table
  41. GROUP BY ID
  42.  
  43. SELECT id,
  44. LIST(data)
  45. FROM table
  46. GROUP BY id
  47.  
  48. SELECT id, LIST(data||', ')
  49. FROM yourtable
  50. GROUP BY id
Add Comment
Please, Sign In to add comment