October 14, 2009 at 5:15 am
how about this:
declare @FinalString nvarchar(250)
SET @FinalString=''
SELECT @FinalString = COALESCE(@FinalString + ', ', '') + (CAST([name] AS nvarchar(50)))
FROM Fruit
order by [name]
SELECT REPLACE(@FinalString,'''',' ')
October 14, 2009 at 5:17 am
It's worth learning!
With regard to software I could be wrong but I believe that Visual Studio Express[/url] is all that you need. The CLR stuff comes in useful in other areas for instance with Regular Expressions or for making external web service calls.
One hacky way to support changing the separator character is to use something obscure in the code such as '¦' and then use the T-SQL REPLACE function to substitute it as required.
Personally I just have a version for comma (',') and then another that uses semi-colon (';') as that keeps the usage of the function simple.
October 14, 2009 at 5:22 am
and what about PIVOT?
October 14, 2009 at 5:26 am
I was trying this the same way as Koenraad Dendievel using a function i found elsewhere, seems to work a treat with this:
CREATE FUNCTION dbo.ConcatFruit()
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+', ', '') + Name
FROMfruit
RETURN @Output
END
GO
SELECT TOP 1 dbo.ConcatFruit()
FROM fruit
GO
DROP FUNCTION dbo.ConcatFruit
GO
October 14, 2009 at 6:17 am
declare @l varchar(max)
,@comma varchar(2)
set @l = ''
set @comma = ''
select @l = @l + @comma + name
,@comma = ', '
from
fruit
ORDER BY name
This is the more performant!
October 14, 2009 at 7:13 am
I've been using Solution 2, FOR XML PATH, for a while with good results. I use STUFF as SSChampion mentions to get rid of the extra separator.
One other thing to watch for with FOR XML PATH is that since it's creating XML, it will encode the characters "<", ">", and "&". You'll need to unencode them manually. Something like this.
-- The REPLACE calls undo the XML encoding
-- and STUFF deletes the first comma and space
REPLACE(REPLACE(REPLACE(STUFF(
(
SELECT
', ' + ...
FROM
...
FOR XML PATH('')
)
, 1, 2, ''), '& lt;', '<'), '& gt;', '>'), '& amp;', '&') -- See note below
On the last line in the example, take out the spaces between the ampersands and the "lt;", "gt;" and "amp;". This forum software was encoding my example of unencoding so I couldn't get it display correctly. How ironic! 😉
October 14, 2009 at 7:21 am
Very helpful, thanks. You can also take out the dangling delimiter...
DECLARE @var varchar(8000)
SET @var = ''
SELECT @var = @var + FieldName + '/'
FROM Table
SET @Var = substring(@Var,1,len(@Var)-1)-- Removes trailing slash
SELECT @Var
October 14, 2009 at 7:25 am
Tom Winter (10/14/2009)
I've been using Solution 2, FOR XML PATH, for a while with good results. I use STUFF as SSChampion mentions to get rid of the extra separator.One other thing to watch for with FOR XML PATH is that since it's creating XML, it will encode the characters "<", ">", and "&". You'll need to unencode them manually. Something like this.
-- The REPLACE calls undo the XML encoding
-- and STUFF deletes the first comma and space
REPLACE(REPLACE(REPLACE(STUFF(
(
SELECT
', ' + ...
FROM
...
FOR XML PATH('')
)
, 1, 2, ''), '& lt;', '<'), '& gt;', '>'), '& amp;', '&') -- See note below
On the last line in the example, take out the spaces between the ampersands and the "lt;", "gt;" and "amp;". This forum software was encoding my example of unencoding so I couldn't get it display correctly. How ironic! 😉
Actually you dont have replace the invalid characters if you use the FOR XML Clause, with the TYPE directive. You can then use an xquery to extract the value as it is precieved in the column, without replacing invalid chars.
DECLARE @t TABLE(
id INT,
col CHAR(2)
);
INSERT INTO @t VALUES (1,'aa');
INSERT INTO @t VALUES (1,'bb');
INSERT INTO @t VALUES (1,'c>');
INSERT INTO @t VALUES (2,'a<');
SELECT
id,
STUFF(
(
SELECT ',' + col
FROM @t t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(100)')
,1,1,'')
FROM @t t1
GROUP BY id
October 14, 2009 at 7:31 am
I use the method below for concatenating multiple values into one. Wouldn't this work?
-- Fill a temporary table variable with the data in the example
DECLARE @TempTable TABLE
(idx int IDENTITY(1,1) PRIMARY KEY,
fruit varchar(15) )
INSERT INTO @TempTable (fruit) VALUES ('Apple')
INSERT INTO @TempTable (fruit) VALUES ('Banana')
INSERT INTO @TempTable (fruit) VALUES ('Grape')
INSERT INTO @TempTable (fruit) VALUES ('Melon')
INSERT INTO @TempTable (fruit) VALUES ('Orange')
-- These three lines take the data from above and concatenate it into one field and then return the field value
DECLARE @Listvarchar(Max)
SELECT @List = COALESCE(@List + ', ','') + fruit FROM @TempTable ORDER BY fruit
SELECT @List
October 14, 2009 at 7:33 am
joe.strollo (10/14/2009)
I use the method below for concatenating multiple values into one. Wouldn't this work?-- Fill a temporary table variable with the data in the example
DECLARE @TempTable TABLE
(idx int IDENTITY(1,1) PRIMARY KEY,
fruit varchar(15) )
INSERT INTO @TempTable (fruit) VALUES ('Apple')
INSERT INTO @TempTable (fruit) VALUES ('Banana')
INSERT INTO @TempTable (fruit) VALUES ('Grape')
INSERT INTO @TempTable (fruit) VALUES ('Melon')
INSERT INTO @TempTable (fruit) VALUES ('Orange')
-- These three lines take the data from above and concatenate it into one field and then return the field value
DECLARE @Listvarchar(Max)
SELECT @List = COALESCE(@List + ', ','') + fruit FROM @TempTable ORDER BY fruit
SELECT @List
This method only works if you want to return a single row. If you need to concatenate values, for each grouping, another method will have to be used, like FOR XML, UDF etc.
October 14, 2009 at 7:37 am
------ CREATE AND LOAD THE TABLE
create table aaatrash(id int, name varchar(20))
insert into aaatrash
select 1, 'apple'
union all
select 2, 'peach'
union all
select 3, 'grape'
union all
select 4, 'banana'
union all
select 5, 'melon'
union all
select 6, 'orange'
---------------------------------
---------- SOLUTION -----------
declare @fruit varchar (200)
select @fruit = coalesce(@fruit + ', ' + name, name ) from aaatrash
select @fruit
---------------------------------
Jerry D
October 14, 2009 at 7:40 am
I've been using Solution 2, "FOR XML PATH" for a while now. I use substring to remove the first separator, I think I will look into using stuff.
I have learned something new today. I think I can kick back and enjoy the day now. 😀
October 14, 2009 at 7:43 am
also a typical response to this question is "don't ask the DB to format strings"
... meaning the consumer of this content should be responsible for display/output.
I'm not sure there is a more 'performant' solution than to offload the presentation work to another tier better suited to the task.
October 14, 2009 at 7:57 am
Why isn't the Coalesce function being used?:
-- Results created neatly in one shot, more fields and other variations can be added 😎
Select @SomeVariable = Coalesce(@SomeVariable + ',', '[if null]') + IsNull(SomeField, '')
FROM tblSomeTable
-- Remove last delimiter
If Right(@SomeVariable , 1) = ',' Set @SomeVariable = Left(@SomeVariable , Len(@SomeVariable ) - 1)
October 14, 2009 at 7:58 am
the simplest one ....
--create sample data set
declare
@table table (name varchar(100))
insert into @table values ('Apple'),('Melons'),('Orange'),('Mangoes')
--concattinate
declare @nsql nvarchar(max)
set @nsql = ''
select @nsql = @nsql+' '+ name
from @table
select @nsql
Viewing 15 posts - 16 through 30 (of 159 total)
You must be logged in to reply to this topic. Login to reply