Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Combine Multiple Rows in One

Posted by Viral Sarvaiya on July 9, 2010

I have a table as:

itemID        tagID
100                111
200                222
200                223
300                333
300                334
300                335

I want to have a select statement to group records by itemID and one itemID will have only one row, multiple tagIDs will be combined in one field.

Step1: create a scalar-valued function (UDF)

ALTER FUNCTION sUDF_itemTagIds(@aItemId int)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @tagids varchar(max)
SELECT @tagids = COALESCE(@tagids + ‘,’,”) + convert(varchar(20),tagID)
FROM myTable
WHERE itemID = @aItemId
RETURN @tagids
END
GO

Step2: call the UDF from the SQL script:

SELECT itemID,dbo.sUDF_itemTagIds(itemID) as tagIDs
FROM myTable
GROUP BY itemID

Here is the result:

itemID         tagIDs
100                111
200                222,223
300                333,334,335

Intro to User Defined Functions

The ability to create a user-defined function (UDF) is a new feature added to SQL Server 2000.

A user-defined function is a database object that encapsulates one or more Transact-SQL statements for reuse. This definition is similar to the one for stored procedures, but there are many important differences between user-defined functions and stored procedures—the most pronounced being what types of data they can return.

Scalar Functions

A scalar function returns a single value of the data type referenced in the RETURNS clause of the CREATE FUNCTION statement. The returned data can be of any type except text, ntext, image, cursor, or timestamp.

Inline Table-Valued Functions

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement.

Multi-Statement Table-Valued Functions

The multi-statement table-valued function is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement. Unlike the inline table-valued function, a table variable must be explicitly declared and defined.

COALESCE

Returns the first nonnull expression among its arguments.

Syntax

COALESCE ( expression [ ,...n ] )

http://bimawen.wordpress.com/2009/09/03/combine-multiple-rows-in-one/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: