February 12, 2015 at 4:18 am
Hi guys,
I have been stuck with this query for a while now and not sure how to go on about this as reached a brick wall.
I have two tables (People and Relation):
One table stores people data like PersonID, Firstname, Lastname etc… and the second table stores the relationship which has the relationshipID, Boss (PersonID), Helper (PersonID).
I need to find out what person has secretaries (more than one in some occasions) and output it into one column (comma delimited) allowing there to be NULLS with people who have no secretaries but DISTINCT rows of people.
In the beginning I managed to refine it by using ‘SELECT TOP 1’ within the SELECT clause but then was asked to refine it so there are no multiple rows people and they also wanted it in one column containing all the secretaries so I then used :
STUFF(( SELECT
',' + [SecretaryFirst Name] AS [text()]
FROM
[Person]
FOR XML PATH('')
), 1, 1, '' )
This brought all the secretaries into one column (not related to PersonID - just a long list), how can I refine this according to the PersonID on each row?
Your input is much appreciated.
Or is there a better way to this?
Thank you
February 15, 2015 at 5:03 pm
SQL_Padwaan (2/12/2015)
Hi guys,I have been stuck with this query for a while now and not sure how to go on about this as reached a brick wall.
I have two tables (People and Relation):
One table stores people data like PersonID, Firstname, Lastname etc… and the second table stores the relationship which has the relationshipID, Boss (PersonID), Helper (PersonID).
I need to find out what person has secretaries (more than one in some occasions) and output it into one column (comma delimited) allowing there to be NULLS with people who have no secretaries but DISTINCT rows of people.
In the beginning I managed to refine it by using ‘SELECT TOP 1’ within the SELECT clause but then was asked to refine it so there are no multiple rows people and they also wanted it in one column containing all the secretaries so I then used :
STUFF(( SELECT
',' + [SecretaryFirst Name] AS [text()]
FROM
[Person]
FOR XML PATH('')
), 1, 1, '' )
This brought all the secretaries into one column (not related to PersonID - just a long list), how can I refine this according to the PersonID on each row?
Your input is much appreciated.
Or is there a better way to this?
Thank you
DDL and sample data would go a long way to getting you a tested solution.
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
February 16, 2015 at 5:20 am
Hi,
Not sure what you meant but here is some sample data:
Person table:
PersonIDFirst NameLast Name
1BobPatel
2LunSmith
3JulieTaylor
4ChrisPain
5JoeBloggs
Relation table
RelationIDBossIDSlaveID
123413
32133
23115
(not sure how to paste in a table)
What I need to work out as how to show what person has a secretary in one column (no duplicate)
Thanks
February 16, 2015 at 7:02 am
CREATE TABLE dbo.Person (PersonID int,FirstName varchar(20),LastName varchar(20))
CREATE TABLE dbo.Relation (RelationID int,BossID int,SlaveID int)
INSERT dbo.Person VALUES (1,'Bob','Patel'),(2,'Lun','Smith'),(3,'Julie','Taylor'),(4,'Chris','Pain'),(5,'Joe','Bloggs')
INSERT dbo.Relation VALUES (1234,1,3),(321,3,3),(231,1,5)
SELECT p.PersonID,p.FirstName,p.LastName,
STUFF((SELECT ',' + Name
FROM (SELECT DISTINCT s.FirstName + ' ' + s.LastName AS [Name]
FROM dbo.Relation r
JOIN dbo.Person s ON s.PersonID = r.SlaveID
WHERE r.BossID = p.PersonID
AND r.BossID <> r.SlaveID) s
FOR XML PATH('')),1,1,'') AS [Secretaries]
FROM dbo.Person p
Far away is close at hand in the images of elsewhere.
Anon.
February 16, 2015 at 9:33 am
This has worked very well 🙂
Thank you
February 16, 2015 at 4:51 pm
SQL_Padwaan (2/16/2015)
Not sure what you meant ...
Actually I think David got it. But if his solution isn't what you seek, try also posting expected results for the sample data provided.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply