SQL - Select multiple rows into single column

Here im going to demonstrate how can you concate all the values in multiple rows into a single value:

Data
TABLE: @tbl_user_request

Raw data
userid request_id
1 'A000001'
1 'A000002'
2 'A000003'
3 'A000004'
4 'A000005'
5 'A000006'
5 'A000007'

Expected result
userid request_id
1 A000001,A000002
2 A000003
3 A000004
4 A000005
5 A000006,A000007

SQL

DECLARE @tbl_user_request TABLE (userid int, request_id varchar(50)) INSERT INTO @tbl_user_request SELECT 1, 'A000001' UNION ALL SELECT 1, 'A000002' UNION ALL SELECT 2, 'A000003' UNION ALL SELECT 3, 'A000004' UNION ALL SELECT 4, 'A000005' UNION ALL SELECT 5, 'A000006' UNION ALL SELECT 5, 'A000007' SELECT a.userid, request_id=substring((SELECT ( ', ' + request_id) FROM @tbl_user_request b WHERE a.userid=b.userid ORDER BY userid FOR XML PATH('')), 3, 1000)FROM @tbl_user_request a GROUP BY userid

note

There is a subquery in the SQL which joining the same table as the main query. The purpose of this subquery is to select all the "requet_id" pertaining to the same userid in the "tbl_user" table and concatenate them into a single "concatename" variable by using the FOR XML PATH

For more example you can refer here:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist