Loading

retrieving data from table

retrieving data from table

satish babu

13y1.9kDatabases & DBA

Hi,

I have small doubt on getting data from table with stored procedure based on parameters from user.i have this table

empno ename

1 hari

2 kumar

1 pavan

1 naveen

But want to get data in this way

empno ename

1 hari,pavan,naveen

2 naveen

and also from the user he will pass colname for order by.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak13y ago
SELECT     empno, SUBSTRING
                          ((SELECT     TOP (100) PERCENT ', ' + ename AS Expr1
                              FROM         employee AS t
                              WHERE     (employee.empno = empno)
                              ORDER BY empno, ename FOR XML path('')), 3, 1000) AS Expr1
FROM         employee
GROUP BY empno
0
Santhosh Kumar Jayaraman13y ago
try this

SELECT empno ,
Stuff((SELECT ', ' + ename
FROM test t2
WHERE t2.empno = t1.empno

FOR XML PATH('')), 1, 2, '')
FROM test t1 group by empno

0
Amit Choudhary13y ago
You can achieve above resultset using below query:

SELECT empno, names =
STUFF((SELECT ', ' + ename
FROM your_table b
WHERE b.empno = a.empno
FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY empno

If you are using EntityFramework, You can use Function Import to call storeporcedure which will create a function with arguments as you have in your StoredProcedure.

Hope this was helpful.
0
venkata kumar13y ago
hi
check fallowing link
https://www.google.co.in/#hl=en&tbo=d&spell=1&q=how+to+combine+multiple+lines+into+single+line+in+sql+server+2005&sa=X&ei=9MbaUKPZJ8forAft54Fg&ved=0CDAQBSgA&bav=on.2,or.r_gc.r_pw.r_cp.r_qf.&bvm=bv.1355534169,d.bmk&fp=763f662d365af90a&bpcl=40096503&biw=1787&bih=860
0