special gruping in sql sever

Question

0.00/5 (No votes)

See more:

hi i have this table

---------
ID | Name
--------
1 | A1
2 | A2
3 | A3
1 | B1
2 | B2
3 | B3
---------

i wanna get this table from up table :

---------
ID | Name
--------
1 | A1 - B1
2 | A2 - B2
3 | A3 - B3
---------

please guide me ...

Posted 14-Oct-11 6:04am

vahid_erad

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon Bang Terkildsen · Accepted Answer · 2011-10-14T06:09:00

Solution 1

Use a join

SQL

select t1.ID, t1.name + '-' + t2.name from myTable t1 JOIN myTable t2 ON(t1.ID = t2.ID)

SQL

SELECT ID,
       REPLACE(RTRIM((SELECT Name + ' ' FROM myTable
                      WHERE (ID = t.ID)
                      FOR XML PATH (''))),' ',' - ')
FROM myTable t
GROUP BY ID

Posted 14-Oct-11 6:09am

Simon Bang Terkildsen

Updated 14-Oct-11 8:32am

v3

Comments

vahid_erad 14-Oct-11 12:14pm

what am i for this table :
---------
ID | Name
--------
1 | A1
2 | A2
3 | A3
1 | B1
2 | B2
1 | C1
---------

get table :

---------
ID | Name
--------
1 | A1 - B1 - C1
2 | A2 - B2
3 | A3
---------

Simon Bang Terkildsen 14-Oct-11 12:23pm

Updated the solution

vahid_erad 14-Oct-11 14:02pm

this query :
SELECT ID,
REPLACE(RTRIM(SELECT Name + ' ' FROM myTable
WHERE (ID = t.ID)
FOR XML PATH ('')),' ',' - ')
FROM myTable t
GROUP BY ID

Have this errors :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

Simon Bang Terkildsen 14-Oct-11 14:34pm

If you had tried to play around with it you would have found out that there were missing a parenthesis set around the inner select.
The solution is updated.

Espen Harlinn 15-Oct-11 4:21am

Good reply :)

Simon Bang Terkildsen 15-Oct-11 14:01pm

Thank you :)