Is it possible?

Question

Post reply

Is it possible?

boyfriend-alex

Grasshopper

Points: 13
More actions
June 16, 2008 at 2:10 am

#93489

There are two tables with relationship.
TABLE-A
ID, Name
1001 kkk
1002 ccc
1003 ddd
TABLE-B
ID Subject
1001 Math
1001 English
1001 Biology
1002 English
1002 Computer
....
By the way i want the following result
Result
ID Name Subject
1001 kkk Math,English,Biology
1002 ccc Englsih,Computer
.....
Is it possible?
Anyone can help me with this problem?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

declare @TABLEA Table ( ID int , Name varchar(20) )

declare @TABLEB Table ( ID int , Subject varchar(20) )

insert into @TABLEA ( ID, Name ) values (1001, 'kkk')

insert into @TABLEA ( ID, Name ) values (1002, 'ccc')

insert into @TABLEA ( ID, Name ) values (1003, 'ddd')

insert into @TABLEB ( ID, Subject) values (1001, 'Math')

insert into @TABLEB ( ID, Subject) values (1001, 'English')

insert into @TABLEB ( ID, Subject) values (1001, 'Biology')

insert into @TABLEB ( ID, Subject) values (1002, 'English')

insert into @TABLEB ( ID, Subject) values (1002, 'Computer')

select a.ID, a.Name, (select stuff((select ', ' + b.Subject as [text()]

from @TableB b

where b.ID = a.ID

order by id

for xml path('')), 1, 2, '')) as Subjects

from @TABLEA a

* Noel

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

This is a cross tab. Search for that and you'll see lots of solutions.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Ugh. How many times a week can we get the "String concatenation" question? We need some FAQ or permanent link that we can just send people to.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

boyfriend-alex Grasshopper Points: 13 More actions · Answer 4

Thank you very much.

It works well.

By the way can i ask one thing more?

In this query....

for xml path('')

What is it for ? What do i have to input?

Thank you for your help.

🙂

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

boyfriend-alex (6/16/2008)
Thank you very much.
It works well.
By the way can i ask one thing more?
In this query....
for xml path('')
What is it for ? What do i have to input?
Thank you for your help.
🙂

It just creates a comma separated "path" in this case... STUFF removes the first comma...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

rbarryyoung (6/16/2008)
Ugh. How many times a week can we get the "String concatenation" question? We need some FAQ or permanent link that we can just send people to.

Heh... we've had one for a while, now... includes the XML method, as well.

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Thanks Jeff, I've added it to my briefcase.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]