SQL Server - Concat multiple rows in single row

I m working sql server 2008, and can it be possible that the multiple rows values in  a table can be conctinate in a single row,

like

table structure as  follow

UserID  Value

1 ,  check

1,no

2 ,test

2, check.

then the outcome will be

UserID Value

1, "Check No" 

2, "Test Check"

Can any body help in building such query

Hi,
Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:

    * MySQL: CONCAT()
    * Oracle: CONCAT(), ||
    * SQL Server: +

The syntax for CONCAT() is as follows:

CONCAT(str1, str2, str3, ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.

Let's look at some examples. Assume we have the following table:

Table Geography
region_name     store_name
East              Boston
East             New York
West           Los Angeles
West          San Diego

Example 1:

MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';

Result:

'EastBoston'

Example 2:

Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';

Result:

'East Boston'

Example 3:

SQL Server:
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';

Result:

'East Boston'

http://www.projectdmx.com/tsql/rowconcatenate.aspx

select id,

Min(Case when name = 'vvv' then name end) as 'name',

Min(Case when name = 'fff' then name end) as 'name'

from tbl

group by id

now put your values and run it....

If it's always only two rows like that, you could use a self-join.  How do you know the order of the rows, though?  I.e., what's the primary key on the table?  If, say, the table has another column named Sequence and a primary key of (UserID,Sequence), and there are always two rows per UserID, then an example of such a self-join is:

SELECT a.UserID, a.Value + ' ' + b.Value
FROM dbo.YourTable a INNER JOIN dbo.YourTable b ON a.UserID = b.UserID AND a.Sequence < b.Sequence;

If there may be more than just two per UserID, you can extend this idea up to seven or eight self-joins before it becomes sluggish, and you'd be faced with (slower) outer joins if the number of rows per ID isn't fixed:

SELECT a.UserID, a.Value + ISNULL(' ' + b.Value,'') + ISNULL(' ' + c.Value,'') + ISNULL(' ' + d.Value,'')
FROM dbo.YourTable a LEFT JOIN dbo.YourTable b ON a.UserID = b.UserID AND a.Sequence < b.Sequence
LEFT JOIN dbo YourTable c ON a.UserID = c.UserID AND b.Sequence < c.Sequence
LEFT JOIN dbo.YourTable d ON a.UserID = d.UserID AND c.Sequence < d.Sequence;

There are a couple of other ways to do this in SQL Server, one that works in any version since 6.5 is to use a UDF:

CREATE FUNCTION dbo.ConcatMyTableValues (@UserID int) RETURNS varchar(8000) AS
BEGIN
 DECLARE @Values varchar(8000);
 SELECT @Values = ISNULL(@Values,'') + ' ' + Value
 FROM dbo.YourTable
 WHERE UserID = @UserID
 ORDER BY Sequence;
 RETURN @Values;
END;

Then you can use this as an aggregate function:

SELECT UserID, dbo.ConcatMyTableValues(Value) AS Values
FROM dbo.YourTable
GROUP BY UserID;

You can also use SQL Server's XML capablitities if on SQL Server 2005 or later:

SELECT a.UserID, STUFF(
(SELECT b.Value + ' '
 FROM dbo.YourTable b
 WHERE a.UserID= b.UserID
 ORDER BY b.Sequence
 FOR XML PATH(''))
,1,1,'')
FROM dbo.YourTable a
GROUP BY a.UserID

you can show this code just Highlight

BEGIN
DECLARE @LastCode INT,
@LastDesc VARCHAR(512)

SELECT Code, Seq_no, Text_desc
INTO #S
FROM S1
ORDER BY Code, Seq_no

SET @LastDesc = ''

UPDATE #S
SET @LastDesc = CASE WHEN Seq_no = 0
THEN Text_desc
ELSE @LastDesc +Text_desc END,

Text_desc = CASE WHEN Seq_no = 0 THEN Text_desc
ELSE @LastDesc +Text_desc END

SELECT Code, Text_desc
FROM #S
GROUP BY Code HAVING Seq_no=max(Seq_no)

END

if you want to do using LINQ then visit this link
http://stackoverflow.com/questions/614542/use-linq-to-concatenate-multiple-rows-into-single-row-csv-property