Returning multiple values to a single column
-
I need to return multiple values to a single comma separated column. Here's an example:
returned data
Joe 123
Joe 456
Joe 789
Ken 357
I need it to return:
Joe 123,456,789
Ken 357
Any suggestions? Thanks
-
DECLARE @text VARCHAR(2000)
SELECT @text = @text + RTRIM(LTRIM(column_value)) + ','
FROM Sometable
-
Thanks for the reply.
We are currently using the suggestion made, but we were hoping to accomplish it with an sql statement or built-in function (i.e., grouping or something like that), not a stored procedure. We can continue to use the sp, just hoping there was some neat little feature of tsql that we hadn't found yet.
-
hmmm...sounds like you need to do a crosstab and then concatenate the fields. Does your second column have a fixed # of possibilities, or is it a numeric field? I can think of some ways to do it if it's got a fixed number of values.
Otherwise I think it's a stored procedure thing, e.g.:
SET NOCOUNT ON
CREATE TABLE #test (Fname Varchar(20), val INT, ID INT IDENTITY)
INSERT #test (fname,val) Values('Joe','123')
INSERT #test (fname,val) Values('Joe','456')
INSERT #test (fname,val) Values('Joe','789')
INSERT #test (fname,val) Values('Ken','357')
SELECT * INTO #t
FROM #test ORDER BY fname, val
Create Table #v (Val varchar(255))
DECLARE
@fname varchar(20)
, @val Varchar(255)
, @STR Varchar(255)
, @ctr INT
, @max-2 INT
SET @ctr = 1
SET @max-2 = (SELECT MAX(ID) FROM #t)
SET @fname = ''
WHILE @ctr <= @max-2
BEGIN
IF @fname = (SELECT fname FROM #t WHERE ID = @ctr)
BEGIN
SET @val = @val + ',' + CAST((SELECT val FROM #t WHERE ID = @ctr) AS Varchar(255))
END
ELSE
BEGIN
IF @val IS NOT NULL
INSERT #v Values(@val)
SET @fname = (SELECT fname FROM #t WHERE ID = @ctr)
SET @val = @fname + ' ' + CAST((SELECT val FROM #t WHERE ID = @ctr) AS Varchar(255))
END
SET @ctr = @ctr + 1
IF @ctr > @max-2
INSERT #v Values(@val)
END
SELECT * FROM #v
DROP TABLE #v
DROP TABLE #t
DROP TABLE #test
Good luck!
-
Our second column does not have a fixed number of values. The input is integer, but will be going to a text column. Looks like a stored procedure is our best option (or only option
)Thanks
-
Use cowboyferrier's method, and write it as a UDF (assuming you're on 2000).
Then in a select you can say:
SELECT name, dbo.myUDF_get_val_list( primarykeyhere ) AS val_list FROM tablename...
Then this can be made into a view if you like.
-
I vote for UDF, too... see my reply in another current thread :
http://qa.sqlservercentral.com/forums/shwmessage.aspx?messageid=133911
This question keeps popping up quite often... I learned this way to create concatenated texts in these forums, half a year ago - and found it very useful.
HTH, Vladan
-
I noted that the main subject of that thread was "to avoid using a cursor".
Just be aware that while "UDF" doesn't spell like "cursor", the implementation of many UDFs shows a "cursory behaviour" - ie what you can get from a UDF is the exact row-by-row processing as a cursor has.
Sometimes this is no issue, other times it will bite you on larger volumes. Then it can be handy to know that performance degradations may be due to "hidden cursor called UDF"
/Kenneth
-
Hey Dude,
I have a quick and easy solution ... I am sure most of the guys do not use COALESCE function. I find it quiet powerful.
Using cursor here will not do much harm, if you really hate cursors there are definitly alternate ways, which I am sure you can try on. Just copy and paste into SQL analyser, and boom.
BEGIN
SET NOCOUNT ON
CREATE TABLE #test (Fname Varchar(20), val INT, ID INT IDENTITY)
INSERT #test (fname,val) Values('Joe','123')
INSERT #test (fname,val) Values('Joe','456')
INSERT #test (fname,val) Values('Joe','789')
INSERT #test (fname,val) Values('Ken','357')
DECLARE @text varchar(255)
DECLARE @m_fname varchar(20)
DECLARE test_cursor CURSOR FOR
SELECT DISTINCT fname FROM #test
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @m_fname
WHILE @@FETCH_STATUS = 0
BEGIN
--return concatinated values of entire table
select @text = COALESCE(@text,'') + convert(varchar(10),val)+ ','
from
#test
where
fname= @m_fname
select @m_fname + ' ' + left(@text, len(@text)-1)
set @text='' --reset @text variable
FETCH NEXT FROM test_cursor INTO @m_fname
END
CLOSE test_cursor
DEALLOCATE test_cursor
DROP TABLE #test
END
-
There is an even simpler and more elegant use the Coalsce() function to "Flatten" a column to a delimited string. I first saw the technique in an article in T-SQL Solutions. At first Glance, I could not believe that it would work, but it does and it performs better than using a cursor or while loop to perform the same operation:
- Declare
@w_resulttext varchar(8000)
SELECT @w_resulttext = coalesce( @w_resulttext +'|', '') + Name
- FROM
sysobjects where type = 'U'
- select
@w_resulttext
The trick to this technique is to use the coalesce function to insert the delimiter. Here is how it works:
- When processing the first row, the variable is null, so the second term of the coalesce function appends the "empty string" to the variable, then the value from the first row is appended
- All subsequent rows are handled by the first term of the coalsce function, resulting in the delimiter being appended to the variable prior to adding the next row's value.
I had written a bunch of user-defined functions using cursors to "flatten" or "Pivot" data for several parent-child tables. To simplify converting them all to this technique, I wrote a helper proc to generate the code for the technique. It generates a functioning code "template" that can be used directly or modified by adding conditions or joins to other tables:
- use
master
- go
- create
procedure sp__CreateFlattener
- @p_table
sysname
,@p_column sysname
,@p_delimeter varchar(20) = ','
,@p_SQL varchar(8000) = null OUTPUT
- as
- /*
- Generate code snippet for collapsing row values into a single delimited string
- Author: clayton_groom@hotmail.com
- Date 2004-07-19
- */
- set
@p_SQL = 'Declare @w_resulttext varchar(8000)
- SELECT @w_resulttext = coalesce( @w_resulttext +'
+ char(39) + @p_delimeter + char(39) + ', ' + char(39) + char(39) + ') + ' + @p_Column
+ char(13) + char(10) + 'FROM ' + @p_table
+ char(13) + char(10) + 'select @w_resulttext'
- print
@p_sql -- comment this out if you don't want the results printed to message window
- return
0
- go
- -- Usage example:
declare @SQL varchar(8000)
- exec
sp__createflattener @p_table = 'sysobjects', @p_column = 'Name', @p_delimeter = '|', @p_SQL = @SQL OUTPUT
- --exec (@SQL)
- print
@sql
- go
Enjoy!
Kindest Regards,
Clayton
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply