July 14, 2015 at 2:55 pm
Hi
I have a table called Signatures
It contains the fields
Parentobject and [View As]
there can be more than one Parentobject so I want to concatenate them so I have them on one line
for example
901 Joe Dow
901 Jane Dow
I want one line - | 901 | Joe Dow, Jane Dow
I found something similar as below but I'm getting dups like
901 |Joe Dow , Joe Dow
901 | Jane Dow, Jane Dow
DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values
SELECT DISTINCT
ParentObject,
(SELECT STUFF(
(SELECT @Delimiter + s1.[View As]
FROM Signatures s2
WHERE s2.ParentObject = s1.parentobject
FOR XML PATH(''), ROOT('root'), TYPE
).value('/root[1]','VARCHAR(MAX)') -- using the .value method allows use to extract special characters such as &.
,1,1,'')) AS Signatures
FROM Signatures s1;
Thanks
Joe
July 14, 2015 at 3:11 pm
Ok so I think I found something in the forum and adapted it to work
SELECT
ParentObject,
STUFF((SELECT ', ' + [View As] FROM dbo.Signatures t2 WHERE t2.ParentObject = t1.ParentObject FOR XML PATH('')),1,2,'') as sig
FROM
dbo.Signatures t1
GROUP BY
ParentObject
Can I incorporate this into another select statement
For example:
Select "many other fields", ..then( the above select....)
From....
Thanks and sorry I S*ck at this 🙂
July 15, 2015 at 6:47 am
jbalbo (7/14/2015)
Ok so I think I found something in the forum and adapted it to workSELECT
ParentObject,
STUFF((SELECT ', ' + [View As] FROM dbo.Signatures t2 WHERE t2.ParentObject = t1.ParentObject FOR XML PATH('')),1,2,'') as sig
FROM
dbo.Signatures t1
GROUP BY
ParentObject
Can I incorporate this into another select statement
For example:
Select "many other fields", ..then( the above select....)
From....
Thanks and sorry I S*ck at this 🙂
Once you incorporate other fields from the Signatures table, you have to worry about your GROUP BY. If you have multiple values for these other fields within a given ParentObject value, then you'll have a problem, as you won't be able to just incorporate the fields into both the SELECT and the GROUP BY. Then the question becomes: which of those values would you want to have appear? You'll end up needing to use an aggregate, and that may not work if you don't always want the MIN or MAX value within a given ParentObject value. So basically, the answer is, IT DEPENDS....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 15, 2015 at 7:00 am
jbalbo (7/14/2015)
Ok so I think I found something in the forum and adapted it to workSELECT
ParentObject,
STUFF((SELECT ', ' + [View As] FROM dbo.Signatures t2 WHERE t2.ParentObject = t1.ParentObject FOR XML PATH('')),1,2,'') as sig
FROM
dbo.Signatures t1
GROUP BY
ParentObject
Can I incorporate this into another select statement
For example:
Select "many other fields", ..then( the above select....)
From....
Thanks and sorry I S*ck at this 🙂
One thing the code you have will do is to create duplicates - each ParentObject will have the same sig.
Also, your sig is not ordered.
Now, for adding many other columns: you might want to try putting this into a Common Table Expression (cte), and joining this back to the table to get the many other columns. Something like (untested):
WITH cte AS
(
SELECT ParentObject,
STUFF((SELECT ', ' + [View As] FROM dbo.Signatures t2 WHERE t2.ParentObject = t1.ParentObject FOR XML PATH('')),1,2,'') as sig
FROM dbo.Signatures t1
GROUP BY ParentObject
)
SELECT cte.ParentObject, cte.sig, sig2.manyothercolumns
FROM cte
JOIN dbo.Signatures sig2 ON cte.ParentObject = sig2.ParentObject;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply