how to make comm seprated list with control break. in sql 2012

Question

hi,

-- col "R" has report no like "R1" "R2" ETC COL "D" AS Direction like "D" "D1". direction can be more than one in one report.
--uniqueness of record is col R + D
-- result needed is all directions of the table IN one SCALAR column with all comma seprated and ";" seprated like FOLLOWING.
-- 'D,D1;D,D1,D2' in order of "R" COL that is first i need DIRECTIONS of report "R1" THEN "R2" AND DIRECTIONs ALSO SHOULD BE IN --ORDER OF "D" COL

SELECT * INTO #T FROM (
SELECT 'R1' R , 'D' D
UNION
SELECT 'R1' R , 'D1' D
UNION
SELECT 'R2' R , 'D' D
UNION
SELECT 'R2' R , 'D1' D
UNION
SELECT 'R2' R , 'D2' D
) T1

SELECT * FROM #T
DROP TABLE #T

Answer 1

Try this query:

select string_agg(a, ';') within group (order by R)
from
(
    select R, string_agg(D, ',') within group (order by D) a
    from #T
    group by R
) t

If string_agg is not available, then:

select stuff((select ';' + a from 
(
    select R, stuff((select ',' + D from #T where R = t.R order by D for xml path('')), 1, 1, '') a
    from #T t
    group by R
) t
order by R
for xml path('')), 1, 1, '')

Answer 2

A simple example of a custom function:

SELECT * INTO T FROM (  
SELECT 'R1' R , 'D' D  
UNION  
SELECT 'R1' R , 'D1' D  
UNION  
SELECT 'R2' R , 'D' D  
UNION  
SELECT 'R2' R , 'D1' D  
UNION  
SELECT 'R2' R , 'D2' D  
) T1  
  
  
CREATE FUNCTION fn_hb(@R VARCHAR(100))  
RETURNS VARCHAR(8000)  
AS   
BEGIN  
DECLARE @Rst VARCHAR(8000)  
SET @Rst=''  
SELECT @Rst=@Rst+[D]+',' FROM T WHERE R=@R  
SET @Rst=Left(@Rst,LEN(@RSt)-1)  
RETURN @Rst  
END  
GO  
  
  
SELECT R,dbo.fn_hb(R) AS Abbr   
FROM T  
GROUP BY R  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".