No More Solutions

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Select Several Rows into One Row in SQL

In SQL Server, there are times when we want to consolidate the content of several rows into one row.

Though it is not meant for sophisticated purposes, there is actually a simple and easy way to do this.
Suppose we have a Member table containing  MemberID and Name fields.

To select the names of the members in one row, simply use the query:


DECLARE @strResult VARCHAR(5000)
SELECT @strResult = COALESCE(@strResult + ', ', '') + Name
FROM Member

SELECT MembersName = @strResult


The result will be:


We can also add some conditions to the select statement.
e.g. To generate the members with name containing 'Himura', we can simply do:


DECLARE @strResult VARCHAR(5000)
SELECT @strResult = COALESCE(@strResult + ', ', '') + Name
FROM Member
WHERE Name LIKE '%himura%'

SELECT TheHimura = @strResult


And we'll get:

That's all folks :)
Bookmark and Share

0 comments:

Post a Comment