?
Solved

T-SQL Function to return a string of concantenated values

Posted on 2006-05-02
5
Medium Priority
?
2,501 Views
Last Modified: 2012-05-05
Here's the deal...

I need a function that will return a string.  The string needs to be created on the fly when I pass it an ITEM_ID.

Say I have a table called COLORS:
ID         Name
1          Red
2          Blue
3          Green

Users may add none, one, or many colors to an item.  For example, an item may be Red and Green.  The user adds Red an then adds Green. so there are two records created in my ITEM_COLORS table.

What I need is a function to return me the colors of ITEM as a string (Red, Green).
0
Comment
Question by:epitec
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16587379
How does a single Item_ID map to multiple colors?
0
 

Author Comment

by:epitec
ID: 16587830
with the ITEM_COLORS table Primare key is join of Item_ID and Color_ID
Item_ID      Color_ID
234            1
234            3
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16588143
CREATE FUNCTION dbo.ItemColors(@ItemID integer)
RETURNS varchar(250)
BEGIN

DECLARE @Colors varchar(250)
SET @Colors = ''

SELECT @Colors = @Colors + COLORS.Name + ', '
FROM ITEM_COLORS
INNER JOIN COLORS
   ON ITEM_COLORS.Color_ID = COLORS.Color_ID
WHERE ITEM_COLORS.Item_ID = @ItemID

IF LEN(@Colors) > 1
  SET @Colors = SUBSTRING(@Colors, 1, LEN(@Colors - 2))

RETURN @Colors

0
 
LVL 28

Accepted Solution

by:
imran_fast earned 2000 total points
ID: 16593466

CREATE FUNCTION dbo.ItemColors(@ItemID integer)
RETURNS varchar(250)
BEGIN
DECLARE @Colors varchar(250),
      @Color varchar(50)
SET @Colors = ''
declare c cursor for select NAME from COLORS where ID IN (SELECT COLOR_ID FROM ITEM_COLORS WHERE ITEM_ID = @ITEMID) FOR READ ONLY
OPEN C
FETCH NEXT FROM C INTO @Color
while @@fetch_status = 0
begin
      set  @Colors = @Colors + @Color
FETCH NEXT FROM C INTO @Color
      if @@fetch_status = 0
      set  @Colors = @Colors + ','
end
Close c
Deallocate C

RETURN @Colors
end
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16724672
hi,
did you get your solution?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question