October 14, 2009 at 1:04 pm
select stuff((select ', '+ name from fruit
order by ', ' +name
for xml path ('')), 1, 2, '')
--returns
Apple, Banana, Grape, Melon, Orange
October 14, 2009 at 1:04 pm
select stuff((select ', '+ name from fruit
order by ', ' +name
for xml path ('')), 1, 2, '')
--returns
Apple, Banana, Grape, Melon, Orange
October 14, 2009 at 1:11 pm
Here is an easy and quick alternative for all versions of SqlServer(unless you are not looking for any solution beyond CTE).
OUTPUT:
--------
Apple, Banana, Orange, Melon, Grape
CODE:
-----
ALTER TABLE fruit
ADD gr int
GO
UPDATE fruit
SET gr = 1
CREATE FUNCTION dbo.salad (@gr as varchar(50))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @RetVal varchar(1000)
SELECT @RetVal = ''
SELECT @RetVal=@RetVal + name + ', '
FROM fruit
WHERE gr=@gr
order by id
select @RetVal = left(@RetVal, len(@RetVal)-1)
RETURN (@RetVal)
END
GO
SELECT dbo.salad(gr) as delicious
FROM fruit
GROUP BY gr
October 14, 2009 at 1:14 pm
(haven't read through all the posts; sorry if someone already has this)
I've modified so that there is aggregation to an ID:
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fruit2]')
AND type in (N'U'))
DROP TABLE [dbo].[fruit2]
CREATE TABLE [dbo].[fruit2](
[id] [int] NOT NULL,
[name] [nvarchar](50) NOT NULL
)
INSERT INTO [dbo].[fruit2]
([id]
,[name])
SELECT 101,'Apple'
UNION ALL SELECT 101,'Banana'
UNION ALL SELECT 102,'Orange'
UNION ALL SELECT 102,'Melon'
UNION ALL SELECT 102,'Grape'
;with
list as (
select
id as id,
name as value,
row_number() over(PARTITION BY id ORDER BY name)as num
from
fruit2
)
,
concatenations as (
select
id as id,
value as head,
cast('' as nvarchar(MAX)) as tail,
num,
(select top 1 name from fruit2 where id = list.id order by name desc) as terminator
from
list
union all
select
id,
head,
(select value from list where num = prev.num - 1 and id=prev.id) + ', ' + tail as tail,
num - 1,
terminator
from
concatenations as prev
where
num > 0
and head = terminator
)
,
concatenated as (
select
id,
max(tail + head) as items
from
concatenations
where
num = 1
group by id
)
select * from concatenated
RESULTS:
id items
----------- --------------------------------------------------
101 Apple, Banana
102 Grape, Melon, Orange
October 14, 2009 at 1:40 pm
I would like to thank everyone for posting your comments and contributing to this discussion. I enjoyed reading along and have learned a few valuable tips and tricks in the process. I never knew about the STUFF function and I appreciate the xquery tip from Adam Haines. Thanks to Jeff Moden for the several insightful additions. I wanted to thank Jeff.Mlaker for his post just now - I was about to address the same issue in this thread. I wonder if it's possible to achieve the same behavior using the FOR XML construct...
Cheers,
Carl Anderson
Data Architect, Northwestern University
October 14, 2009 at 2:01 pm
create table #fruit
(id int, name varchar(50))
declare fruits cursor
read_only
for select distinct id from fruit
declare @id int
declare @value varchar(500)
open fruits
fetch next from fruits into @id
while (@@fetch_status<>-1)
begin
if (@@fetch_status<>-2)
begin
select @value = stuff((select distinct ', '+ name from fruit
where id = @id
order by ', '+ name
for xml path('')), 1, 2, '')
insert into #fruit
values (@id, @value)
end
fetch next from fruits into @id
end
close fruits
deallocate fruits
select * from #fruit
drop table #fruit
--Returns
101Apple, Banana
102Grape, Melon, Orange
October 14, 2009 at 2:17 pm
I have no performance comparisons but you could also use the COALESCE function. I pulled this from one of my stored procedures:
DECLARE @result varchar(max)
SELECT
@result = coalesce(@result + ', ', '') + NAME
FROM
Person.StateProvince
WHERE
Person.StateProvince.CountryRegionCode = @CountryRegionCode
RETURN @result
--RETURNS: Australia | New South Wales, Queensland, South Australia, Tasmania, Victoria
October 14, 2009 at 2:53 pm
DECLARE @FRUIT TABLE (
id SMALLINT,
name VARCHAR(10)
)
INSERT INTO @FRUIT (id, name)
SELECT TOP 100 PERCENT id, name FROM (VALUES (101, 'Apple'), (102, 'Banana'), (103, 'Orange'), (104, 'Melon'), (105, 'Grape')) X (id, name)
SELECT * FROM @FRUIT
-- =============================================================
DECLARE @FruitList VARCHAR(100)
SET @FruitList=''
SELECT @FruitList=@FruitList + NM FROM (SELECT name + ', ' AS NM FROM @FRUIT)X
SET @FruitList = SUBSTRING(@FruitList, 1, LEN(@FruitList)-1)
SELECT @FruitList
October 14, 2009 at 2:54 pm
carl.anderson-1037280 (10/14/2009)
I would like to thank everyone for posting your comments and contributing to this discussion. I enjoyed reading along and have learned a few valuable tips and tricks in the process. I never knew about the STUFF function and I appreciate the xquery tip from Adam Haines. Thanks to Jeff Moden for the several insightful additions. I wanted to thank Jeff.Mlaker for his post just now - I was about to address the same issue in this thread. I wonder if it's possible to achieve the same behavior using the FOR XML construct...Cheers,
Carl Anderson
Data Architect, Northwestern University
Actually, Adam Haines demonstrated grouping in the same code where he demonstrated the TYPE option:
I have paraphrased for fruit:
with produce (id,fruit)
as (
SELECT 101,'Apple'
UNION ALL SELECT 101,'Banana'
UNION ALL SELECT 102,'Orange'
UNION ALL SELECT 102,'Melon'
UNION ALL SELECT 102,'Grape'
)
SELECT
id,
STUFF(
(
SELECT ',' + fruit
FROM produce p2
WHERE p1.id = p2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(100)')
,1,1,'')
FROM produce p1
GROUP BY id
returns:
101Apple,Banana
102Orange,Melon,Grape
October 14, 2009 at 3:25 pm
Jeff Moden
On my box, the hands down winner is the XML solution with the caveat that it won't handle special characters. The 8K ISNULL function and the XML TYPE solution are just about tied with the caveat that the XML TYPE solution will also handle things bigger than 8k.
Once again, Jeff hits it out of the park! Thanks Jeff, for the detailed test code. I ran it and got similar results, but was surprised at how much overhead is in compiling the execution plan. The XML solution was also the fastest on my machine, but it went from 2.9 S (CPU time) the first time I ran it, down to as low as 1.1 S for subsequent executions.
Also, I noticed the varchar(5) type you used in your " XML TYPE solution" truncated the results. But, when I tested with varchar(max) or varchar(2000) I did not notice any change in performance.
Finally, when I added "special character" replacement to the XML solution (Hey, apples to apples:-D), the CPU times were comparable to the XML TYPE solution times.
October 14, 2009 at 3:34 pm
Much thanks, Adam Haines, for the XMl rollup example.
Any way to get it for multiple columns? As in raw data:
101,'Apple','Cleveland'
101,'Apple','Pittsburgh'
101,'Banana','Pittsburgh'
102,'Grape','Cleveland'
102,'Melon','Cleveland'
103,'Melon','Cleveland'
103,'Melon','Pittsburgh'
results in:
101 'Apple, Banana', 'Cleveland, Pittsburgh'
102 'Grape, Melon', 'Cleveland'
103 'Melon', 'Cleveland, Pittsburgh'
??
October 14, 2009 at 3:42 pm
Actually, I just realized that doing it for multiple columns is as easy as adding another FOR XML (and including a DISTINCT to prevent dups):
with produce (id,fruit,city)
as (
SELECT 101,'Apple','Cleveland'
UNION ALL SELECT 101,'Apple','Pittsburgh'
UNION ALL SELECT 101,'Banana','Pittsburgh'
UNION ALL SELECT 102,'Grape','Cleveland'
UNION ALL SELECT 102,'Melon','Cleveland'
UNION ALL SELECT 103,'Melon','Cleveland'
UNION ALL SELECT 103,'Melon','Pittsburgh'
)
SELECT
id
,STUFF(
(
SELECT DISTINCT ',' + fruit
FROM produce p2
WHERE p1.id = p2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,1,'')
,STUFF(
(
SELECT DISTINCT ',' + city
FROM produce p2
WHERE p1.id = p2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,1,'')
FROM produce p1
GROUP BY id
YIELDS:
101 Apple,Banana Cleveland,Pittsburgh
102 Grape,Melon Cleveland
103 Melon Cleveland,Pittsburgh
Just as I wanted.
October 14, 2009 at 3:59 pm
Here's one more for all you brainiacs. I usually use this approach when I need to concatenate rows. (Thank you to Linda Wierzbecki.)
-- Concatenate all the possible fruits in the order of
-- their id, *without* using cursors, adapted from
-- an approach "borrowed" from Linda Wierzbecki
CREATE TABLE #Fruits
(
FruitNum INT,
ListFruitNames VARCHAR(100)
)
INSERT INTO #Fruits
SELECT [id], [name] FROM dbo.FRUIT WHERE [id] =
(SELECT MIN([id]) FROM dbo.Fruit)
WHILE (SELECT COUNT(FruitNum) FROM #Fruits) > 0
BEGIN
UPDATE #Fruits
SET ListFruitNames = ListFruitNames + COALESCE(
(
SELECT ', ' + [name]
FROM dbo.Fruit
WHERE [id] =
(SELECT MIN([id]) FROM dbo.Fruit WHERE [id] > #Fruits.FruitNum)
), ''),
FruitNum =
(
SELECT MIN([id])
FROM dbo.Fruit
WHERE [id] > #Fruits.FruitNum
)
FROM #Fruits
END
SELECT ListFruitNames FROM #Fruits
DROP TABLE #Fruits
October 14, 2009 at 6:46 pm
Here is another solution to the problem that I usually use.
declare @res varchar(max)
select @res = isnull(@res + ', ', '') + name from table_source order by id
select @res
Here table_source can be table, derived table, view, CTE.
Using fruit CTE from original example it becomes:
declare @res varchar(max);
with
fruit as (
select 'Apple' as name, 101 as id union all
select 'Banana' as name, 102 as id union all
select 'Orange' as name, 103 as id union all
select 'Melon' as name, 104 as id union all
select 'Grape' as name, 105 as id
)
select @res = isnull(@res + ', ', '') + name from fruit order by id
select @res
October 14, 2009 at 7:11 pm
john.kilgo (10/14/2009)
I have no performance comparisons but you could also use the COALESCE function. I pulled this from one of my stored procedures:DECLARE @result varchar(max)
SELECT
@result = coalesce(@result + ', ', '') + NAME
FROM
Person.StateProvince
WHERE
Person.StateProvince.CountryRegionCode = @CountryRegionCode
RETURN @result
--RETURNS: Australia | New South Wales, Queensland, South Australia, Tasmania, Victoria
The performance comparisons between ISNULL, COALESCE, and XML are all in the test code I posted. The code I used is the same basic code that everyone has been posting throughout most of this thread. Take a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 159 total)
You must be logged in to reply to this topic. Login to reply