|
|
SQL Server T-SQL User-Defined Function of the Week
Volume 1 Number 30 June 10, 2003
Joining Strings into Delimiter Separated Lists
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Last week we looked at udf_Txt_SplitTAB, which can split up a
delimited list of entries that were stored in a string. This
week we're going to do the opposite. Combine the values in a
column into a comma separated list.
I find this comes up in newsgroup posting and on-line forums
fairly frequently. In fact, the example that I'm using is from
a question posted on the Experts-Exchange. I lurk there on
occasion and I've used it to get answers to a few questions.
It's a pretty good forum and not limited to SQL Server.
Before we go to far, lets review a little from last week's
issue. Storing multiple items of data in a single column
violates the rules of First Normal Form. You're not suppose
to be doing that in a relational database. But just because
it's not relational doesn't mean it's not convenient and used by
many people all the time. Comma separated lists are often used
as a data exchange format.
Here's an example of how to use udf_Txt_SplitTAB to split up
a comma separated string.
SELECT Item as [Stooge]
FROM udf_Txt_SplitTAB(
'Moe, Larry, Shemp, Curly, Joe, Curly-Joe'
, ',')
(Results)
Stooge
----------------------------------------
Moe
Larry
Shemp
Curly
Joe
Curly-Joe
Be sure not to forget that Thursday June 19th is the 106th
anniversary of Moe Howard's birth. Drink a toast to the Stooges!
Getting back to the original question it was phrased something
like this:
My database has a table with this schemas and sample data. I want
a comma separated lists of all the Col3s that go with unique
combinations of Col1 and Col2.
CREATE TABLE Tbl1(
Col1 int
, Col2 int
, Col3 char(1)
)
GO
INSERT INTO Tbl1 VALUES (1, 10, 'A')
INSERT INTO Tbl1 VALUES (1, 10, 'B')
INSERT INTO Tbl1 VALUES (1, 20, 'A')
INSERT INTO Tbl1 VALUES (2, 10, 'B')
INSERT INTO Tbl1 VALUES (1, 10, 'C')
GO
Take a look at the sample data:
SELECT * FROM Tbl1
GO
(Results)
Col1 Col2 Col3
----------- ----------- -----------
1 10 A
1 10 B
1 20 A
2 10 B
2 10 C
The goal is to list each combination of Col1 and Col2 with a
comma separated list of Col3 values that go with that
combination.
Col1 Col2 CombinedDesc
----------- ---------- ------------------------------
1 10 A,B
1 20 A
2 10 B,C
I've had to solve this one before so the answer is pretty easy.
It has to be broken down into two steps:
Step 1) Write a SELECT that creates the desired groups. In this
case it's:
SELECT Col1, Col2
FROM Tbl1
GROUP BY Col1, Col2
GO
(Results)
Col1 Col2
----------- -----------
1 10
1 20
2 10
2) Write a UDF that creates the desired list given values of
Col1 and Col2 as parameters.
The problem of creating the comma separated list can be easily
handled by a UDF. However, because any references to tables
inside a UDF must name the table, it's not possible to make a
general purpose UDF for the task. Instead, a UDF specific to
the tables and columns to be concatenated is needed.
Here's the CREATE FUNCTION script to concatenate the Col3 values
given a Col1 and Col2:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_Example_JoinDesc (
@Col1Value int -- Value of Col1 to select
, @Col2Value int -- Value of Col2 to Select
) RETURNS varchar(2000) -- combine descriptions
WITH SCHEMABINDING
/*
* Creates a comma separated list of Desc columns for all Col1 and
* Col2 values in Tbl1.
*
* Example:
select dbo.udf_Example_JoinDesc(1, 10)
*
* History:
* When Who Description
* ------------- ------- -----------------------------------------
* 2003-04-26 ASN Initial Coding
****************************************************************/
AS BEGIN
DECLARE @Result varchar(2000)
SET @Result = ''
SELECT @Result = @Result
+ CASE WHEN LEN(@Result)>0 THEN ',' ELSE '' END
+ Col3
FROM dbo.Tbl1
WHERE Col1 = @Col1Value
AND Col2 = @Col2Value
RETURN @Result
END
GO
GRANT EXEC, REFERENCES ON [dbo].[udf_Example_JoinChars]
TO [PUBLIC]
GO
After setting @Result to an empty string comes the SELECT
statement that does all the work. By repeatedly concatenating
to the @Result variable we get the same effect as if there
a aggregation operator for concentration:
@Result = @Result
+ CASE WHEN LEN(@Result)>0 THEN ',' ELSE '' END
+ Col3
The same result could have been achieved with a cursor but this
concatenation trick is both easier to write and executes
much faster.
Combining the SELECT statement that groups by Col1 and Col2
with our UDF gives us the result that we are looking for:
SELECT Col1
, Col2
, dbo.udf_Example_JoinDesc(Col1, Col2) as [Col3 List]
FROM Tbl1
GROUP BY Col1, Col2
GO
(Results)
Col1 Col2 Col3 List
----------- ----------- ----------------------------------------
1 10 A,B
1 20 A
2 10 B,C
This solution isn't a barn burner but it does the job.
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
