Same ID with Different Names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Same ID with Different Names

Author

Topic

2011SQL
Starting Member

12 Posts

Posted - 2011-06-28 : 18:22:20

SQL Team,

I have a table with this following fields:

ID Number | Names

123 | Paul Thomas
123 | Frank Biker
123 | Mandy
123 | Rose Mike
123 | Lisa

I would like to see this results:

Paul Thomas, Frank Biker, Mandy, Rose Mike, Lisa

I tried TSQL syntax : SELECT Name as [Online Names] FROM tblname WHERE Id = '123'

the query results will display :

Online Names

Paul Thomas
Frank Biker
Mandy
Rose Mike
Lisa

I would like to know, what is the correct sql query sintax to display this following results:

Paul Thomas, Frank Biker, Mandy, Rose Mike, Lisa

Hope you could help me on this. Your help is much appreciated.
Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-28 : 18:37:28

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

KH
[spoiler]Time is always against us[/spoiler]

2011SQL
Starting Member

12 Posts

Posted - 2011-06-28 : 19:10:56

Thanks for your quick response.
I saw the link you gave me and the samples.
It was good to see the samples, but I want a little quick and simple SQL Statement for my VB.net programming.
For what I saw so sould be : SELECT DISTINCT ID, STUFF((SELECT ',' + name FROM tblname WHERE ID = '123' FOR XML PATH ('')), 1, 1, '' ) AS OneLineNames

Please correct me if I am wrong.

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-29 : 02:19:31

It looks ok.
Connect to a query window and give it a try then you will know if it works.
If it is not working then please come back with informations like executed statement and error message and so on...

No, you're never too old to Yak'n'Roll if you're too young to die.

2011SQL
Starting Member

12 Posts

Posted - 2011-06-29 : 11:25:16

Webfred,

Thanks for verify the syntax line.
I have a SQL table called Docs:

pk_id parcel_id name

1 12345 Paul Thomas
2 12345 Frank Biker
3 12345 Mandy
4 12345 Rose Mike
5 12345 Lisa
6 45678 Richie

When I wrote this syntax

SELECT DISTINCT parcel_id,
(STUFF((SELECT ',' + name FROM Docs
WHERE parcel_id = '12345'
FOR XML PATH ('')),1,1,'')) AS name

Display an error: invalid column name 'parcel_id'

I hope you could help on this.

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-29 : 11:36:26

I think you mean this:

SELECT DISTINCT d1.parcel_id,
(STUFF((SELECT ',' + name FROM Docs d2 WHERE d2.parcel_id = d1.parcel_id FOR XML PATH ('')),1,1,'')) AS name
from docs d1
WHERE d1.parcel_id = '12345'

No, you're never too old to Yak'n'Roll if you're too young to die.

2011SQL
Starting Member

12 Posts

Posted - 2011-06-29 : 12:05:37

webfred,

I was trying this following code before you sent your:
Look what I did:

SELECT DISTINCT parcel_id,
STUFF((SELECT ',' + name FROM Docs WHERE parcel_id = '12345' FOR XML PATH('')), 1, 1, '') AS NAMES
FROM Docs
ORDER BY parcel_id

It works pretty good but I don't like the look

parcel_id Names
12345 Paul Thomas ,Frank Biker ,Mandy ,Rose Mike ,Lisa

There is a waste of space between names... but it's working and I would like to see this way:

parcel_id Names
12345 Paul Thomas, Frank Biker, Mandy, Rose Mike, Lisa

How to fix the spaces between names?

I tried yours and works pretty good too.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-29 : 13:37:25

quote:
Originally posted by 2011SQL

webfred,

I was trying this following code before you sent your:
Look what I did:

SELECT DISTINCT parcel_id,
STUFF((SELECT ', ' + ltrim(rtrim(name)) FROM Docs WHERE parcel_id = '12345' FOR XML PATH('')), 1, 2, '') AS NAMES
FROM Docs
ORDER BY parcel_id

It works pretty good but I don't like the look

parcel_id Names
12345 Paul Thomas ,Frank Biker ,Mandy ,Rose Mike ,Lisa

There is a waste of space between names... but it's working and I would like to see this way:

parcel_id Names
12345 Paul Thomas, Frank Biker, Mandy, Rose Mike, Lisa

How to fix the spaces between names?

I tried yours and works pretty good too.

No, you're never too old to Yak'n'Roll if you're too young to die.

2011SQL
Starting Member

12 Posts

Posted - 2011-06-29 : 14:56:13

Webfred,

It worked, but there is a little problem, display the others names that is not part of parcel_id 12345.

Please review this following line that I did:

SELECT DISTINCT pk_id, parcel_id, name, STUFF((SELECT ',' + ltrim(rtrim(name)) FROM Docs FOR XML PATH('')), 1, 2, '') AS NAMES FROM Docs WHERE parcel_id LIKE '" & TextBox1.Text & "%' "

Why it display all names that are not part of parcel_id 12345.

It's pretty close to the final solution.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-29 : 16:38:06

Take my previous solution and not yours.
Alter my solution like I did it with yours.
Then it will work.

No, you're never too old to Yak'n'Roll if you're too young to die.

2011SQL
Starting Member

12 Posts

Posted - 2011-06-30 : 12:54:29

Your previous solution nothing seems to working:

THis is working :
SELECT DISTINCT
STUFF((SELECT ', ' + ltrim(rtrim(name)) FROM Docs FOR XML PATH('')), 1, 2, '') AS NAMES
FROM Docs
WHERE parcel_id = '12345'

but display too many names that doesn't belong to the parcel_id '12345'

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources