Stored procedure - 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

General SQL Server Forums

New to SQL Server Programming

Stored procedure

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2008-09-26 : 00:18:59

I have a two tables

table1

magname
ABC
XYZ
PQR
JKL

table2

magname empname
ABC emp1
ABC emp2
ABC emp3
XYZ emp4
XYZ emp5
PQR emp6
JKL emp7
JKL emp8

I want to write a stored procedure which will return the output as follow

magname empname
ABC emp1,emp2,emp3
XYZ emp4,emp5
PQR emp6
JKL emp7,emp8

please give me some idea

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2008-09-26 : 00:48:33

search COALESCE
you can find many examples via google here is one:
http://articles.techrepublic.com.com/5100-10878_11-6183173.html

You can do anything at www.zombo.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 00:49:10

If you're using sql 2005

SELECT t1.magname,LEFT(el.emplist,LEN(el.emplist)-1)
FROM table1 t1
CROSS APPLY(SELECT empname + ',' AS [text()]
            FROM table2
            WHERE magname=t1.magname
            FOR XML PATH(''))el(emplist)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 00:50:37

and if using sql 2000 try any of methods given in below link

http://www.projectdmx.com/tsql/rowconcatenate.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-26 : 02:33:52

quote:
Originally posted by clarkbaker1964

search COALESCE
you can find many examples via google here is one:
http://articles.techrepublic.com.com/5100-10878_11-6183173.html

You can do anything at www.zombo.com

Thats not the correct answer
Refer Visakh's reply or http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources