Solved

sql server Query question

Posted on 2013-01-04

Microsoft SQL Server 2008

1 solution

282 Views

Last Modified: 2013-01-04

Hi,

I have a student table & has three coulumns. The sample data exists in this table as below.
I want a query which should output(please see in expected output). I don't want to use cursor.

Student table with sample row

StudenRowid Name course
1 paul Math
1 paul English
2 David science
2 David English
3 Tim Matk

Expected output:

Name course
Paul Math,English
David Science,English
Tim Math

Thanks for your help.

Comment

Question by:r_pat72

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 27

Expert Comment

by:Chris Luttrell

ID: 387455312013-01-04

Try this:

select DISTINCT Name
, REPLACE((select Course AS [data()] from #students s where s.SudentRowId = x.SudentRowId for xml path('')), ' ', ',') AS Course_List
from students x

Open in new window

Author Comment

by:r_pat72

ID: 387456282013-01-04

Hi ,

Thanks for your reply. But if the course is social science, the results which returns is social,science.

StudenRowid Name course
1 paul Math
1 paul English
2 David science
2 David English
3 Tim Math
4 Tom Social science

For Tom it returns social,science which should return social science.

Thanks

LVL 27

Accepted Solution

by:Chris Luttrell

Chris Luttrell earned 500 total points

ID: 387456602013-01-04

Ah, the embedded space, then this is another way to do it and should work. I just liked the elegance of the REPLACE instead of SUBSTRING

select DISTINCT Name
, substring((select ','+Course from students s where s.SudentRowId = x.SudentRowId for xml path('')),2,2000) as Couse_list
from students x

Open in new window

Just make sure you have the last value for the length in SUBSTRING long enough.

Sample output:

David	Science,English
paul	Math,English
Tim	Social Science

Open in new window

Author Comment

by:r_pat72

ID: 387456972013-01-04

thank a lot. This worked.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pass Tables to Stored Procedure / Table Valued Parameter

Article by: apexpert

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …

Microsoft SQL Server 2008

How to recover deleted rows in SQL Server

Article by: Yashwant

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

NetCrunch network monitor live walk-through

Video by: AdRem

NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

Bar Graphs in an Access Query

Video by: crystal

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …