How do I write this code?
-
Can't seem to figure this one out, although it seems like it should be rather simple. I really appreciate your time and response.
Consider the following Table:
How would I write SQL code to return the following result:
Randy Davis
Denver, CO
-
rrdavis07 (9/2/2014)
Can't seem to figure this one out, although it seems like it should be rather simple. I really appreciate your time and response.Consider the following Table:
How would I write SQL code to return the following result:
Randy Davis
Denver, CO
with basedata as (
select distinct
Name
from
dbo.YourTableName
)
select
bd.Name,
stuff((select ',' + ytn.Group
from dbo.YourTableName ytn
where bd.Name = ytn.Name
order by ytn.Group
for xml path(''),TYPE).value('.','varchar(max)'),1,1,'')
from
basedata bd;
Typed that off the top of my head, give it a try.
-
Lynn's code is explained in here:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
-
Further on the previous solution, here is an executable sample
😎
USE tempdb;GO
;WITH BASE_DATA(NNAME,NGROUP) AS
(SELECT NNAME,NGROUP FROM (VALUES
('Bill','Finance')
,('Nancy','Ops')
,('Bill','IT')
,('Stacy','Mktg')
) AS X(NNAME,NGROUP)
)
SELECT DISTINCT
BD.NNAME
,(STUFF((SELECT N',',X.NGROUP
FROM BASE_DATA X
WHERE X.NNAME = BD.NNAME
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(1024)'),1,1,'')) AS GGROUP
FROM BASE_DATA BD;
Results
NNAME GGROUP----- -----------
Bill Finance,IT
Nancy Ops
Stacy Mktg
-
Eirikur Eiriksson (9/2/2014)
Further on the previous solution, here is an executable sample😎
USE tempdb;GO
;WITH BASE_DATA(NNAME,NGROUP) AS
(SELECT NNAME,NGROUP FROM (VALUES
('Bill','Finance')
,('Nancy','Ops')
,('Bill','IT')
,('Stacy','Mktg')
) AS X(NNAME,NGROUP)
)
SELECT DISTINCT
BD.NNAME
,(STUFF((SELECT N',',X.NGROUP
FROM BASE_DATA X
WHERE X.NNAME = BD.NNAME
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(1024)'),1,1,'')) AS GGROUP
FROM BASE_DATA BD;
Results
NNAME GGROUP----- -----------
Bill Finance,IT
Nancy Ops
Stacy Mktg
Sorry, pet peeve time. Semicolons are statement terminators not statement begininators. The above code written without begininators:
USE tempdb;GO
WITH BASE_DATA(NNAME,NGROUP) AS
(SELECT NNAME,NGROUP FROM (VALUES
('Bill','Finance')
,('Nancy','Ops')
,('Bill','IT')
,('Stacy','Mktg')
) AS X(NNAME,NGROUP)
)
SELECT DISTINCT
BD.NNAME
,(STUFF((SELECT N',',X.NGROUP
FROM BASE_DATA X
WHERE X.NNAME = BD.NNAME
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(1024)'),1,1,'')) AS GGROUP
FROM BASE_DATA BD;
Results
NNAME GGROUP----- -----------
Bill Finance,IT
Nancy Ops
Stacy Mktg
Pet peeve done. No hard feelings. 🙂
-
😎
-
Eirikur Eiriksson (9/2/2014)
😎
Nope, I meant what I wrote; begininators. My term for the improper use of terminators at the beginning of statements. 😀
-
Lynn Pettis (9/2/2014)
Nope, I meant what I wrote; begininators. My term for the improper use of terminators at the beginning of statements. 😀Are you working for Skynet?
😎
-
Lynn Pettis (9/2/2014)
Sorry, pet peeve time. Semicolons are statement terminators not statement begininators.Here, here! This terminator policeman once set me straight.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply