June 28, 2011 at 8:49 am
Hi guys . . . I'm guessing that this has been asked before, but I'm going to ask again, anyway.
I have three tables: two data tables (in the following example, I'll call them #Names and #Offices) and a junction table (#Index). I've set up a sample here for your enjoyment:
create table #Names (NameID int primary key, Name varchar(50))
create table #Offices (OffID int primary key, Loc varchar(50))
create table #Index (NameID int, OffID int)
insert into #Names (NameID, Name) values (1, 'Derek Jeter')
insert into #Names (NameID, Name) values (2, 'Hawkeye Pierce')
insert into #Names (NameID, Name) values (3, 'Steve McGarrett')
insert into #Offices (OffID, Loc) values (1, 'New York')
insert into #Offices (OffID, Loc) values (2, 'Crabapple Cove')
insert into #Offices (OffID, Loc) values (3, 'Honolulu')
insert into #Index (NameID, OffID) values (1, 1)
insert into #Index (NameID, OffID) values (2, 2)
insert into #Index (NameID, OffID) values (3, 3)
insert into #Index (NameID, OffID) values (2, 1)
insert into #Index (NameID, OffID) values (2, 3)
insert into #Index (NameID, OffID) values (3, 1)
Here's what I want to do: I want to return the office IDs from the #Index junction table as a single column, not as a table join.
In other words, I'm looking for this (note: commas and order are optional):
NameIDNameOffID
1Derek Jeter1
2Hawkeye Pierce1, 2, 3
3Steve McGarrett1, 3
I don't want to do a JOIN, like this:
NameIDNameOffID
1Derek Jeter1
2Hawkeye Pierce1
2Hawkeye Pierce2
2Hawkeye Pierce3
3Steve McGarrett1
3Steve McGarrett3
I'm at a loss as to how to do this. Can anyone offer any suggestions?
Thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 28, 2011 at 8:54 am
Here's a sample concat code. Enjoy the little tweaks!
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
June 28, 2011 at 9:19 am
Man, you're not kidding about the tweaks!
I got the jist of it, nevertheless. Thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 28, 2011 at 9:23 am
with CTE as
(
select name,nameid
from #names
)
SELECT
name,
offidlist = STUFF((
SELECT
',' + cast(offId as varchar(3))
FROM #index
where nameid = cte.nameid
order by offid asc
FOR
XML PATH('')
) , 1 , 1 , '')
FROM cte
Only because you talk baseball with me.
June 28, 2011 at 9:23 am
Ray K (6/28/2011)
Man, you're not kidding about the tweaks!I got the jist of it, nevertheless. Thanks!
This concept is a litter harder to comprehend so I can't just give you the ready made answer!
Tho it was just a matter of changing 2 table names and adding a join + convert. :hehe:
June 28, 2011 at 9:29 am
calvo (6/28/2011)
with CTE as
(
select name,nameid
from #names
)
SELECT
name,
offidlist = STUFF((
SELECT
',' + cast(offId as varchar(3))
FROM #index
where nameid = cte.nameid
order by offid asc
FOR
XML PATH('')
) , 1 , 1 , '')
FROM cte
Only because you talk baseball with me.
And I'm thankful . . . even if you are a Red Sox fan! 😀
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 28, 2011 at 10:00 am
Ninja's_RGR'us (6/28/2011)
Here's a sample concat code. Enjoy the little tweaks!
SELECTT.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
Thanks Ninja's_RGR'us. I've been using COALESCE for this sort of thing, but this is more straght-forward in a way - I like it. "I like it a lot."
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
June 28, 2011 at 10:06 am
Welcome all, in the name of whoever I stole it from on these forums :hehe:.
Sorry but I don't know the originator so I'll just give credits to Bill Gates. :alien::smooooth:
June 28, 2011 at 10:31 am
June 28, 2011 at 11:08 am
Here is one other option that seems a bit more intuitive to me but that requires the use of SQLCLR.
This:
with CTE as
(
select name,nameid
from #names
)
SELECT
name,
offidlist = STUFF((
SELECT
',' + cast(offId as varchar(3))
FROM #index
where nameid = cte.nameid
order by offid asc
FOR
XML PATH('')
) , 1 , 1 , '')
FROM cte
Can be written as this:
SELECT n.Name,
dbo.GROUP_CONCAT(i.OffID) AS offidlist
FROM #Index i
JOIN #Names n ON i.NameID = n.NameID
GROUP BY n.Name
No Visual Studio required either. You can get the compiled SQLCLR User-defined Aggregate here and deploy it using only T-SQL: http://groupconcat.codeplex.com/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 11:14 am
How much faster is it?
June 28, 2011 at 11:35 am
Ninja's_RGR'us (6/28/2011)
How much faster is it?
Depending on the use case it can be pretty significant, but it depends on the use case and the data of course. The UDA outperformed the "XML PATH, TYPE" method in 4 out of 6 use cases. If you include the "XML PATH" method (which suffers from escape issues) the UDA only outperforms the XML in 3 out of 6 use cases. My tests were done on sets of 300K and 600K rows.
Edit: pretty significant means ~10% faster
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 6:39 pm
Okay, here's a recursive CTE version should other methods not work for you. It does have it's own problems however (such as recursion limit) and I suspect the performance would be pretty poor. But I include it here as another example even though I'd likely try the XML method first.
with
ConcatList as
(
selectdistinct
0 as RowCounter
, NameID
, Name
, cast('' as varchar(max)) as Listed
, 0 as LastOffID
from#Names
union all
selectRowCounter + 1
, cl.NameID
, cl.Name
, cl.Listed + ',' + cast(min(ii.OffID) over (partition by cl.NameID) as varchar(max))
, min(ii.OffID) over (partition by cl.NameID)
fromConcatList cl
inner join #Index ii
on ii.NameID = cl.NameID
whereii.OffID > LastOffID
)
, CleanedList as
(
selectRowCounter
, NameID
, Name
, stuff(Listed, 1, 1, '') as OffID
, max(RowCounter) over (partition by NameID) as LastRowCounter
fromConcatList
)
selectdistinct
NameID
, Name
, OffID
fromCleanedList
whereRowCounter = LastRowCounter
Steve.
June 28, 2011 at 6:43 pm
Why the heck are you going recursive with this???
June 28, 2011 at 9:28 pm
Ninja's_RGR'us (6/28/2011)
Why the heck are you going recursive with this???
Hey, don't get me wrong, I agree with your sentiments exactly. Any form or recursion/looping should be avoided like the plague.
That said, it is still an option albeit one from the bottom of the barrel. The OP may face constraints that prevent the use of better options, and a poor option is better than no option.
Steve.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply