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
 combine multiple rows to single column

Author  Topic 

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-07-08 : 03:21:29
Hi,
with the below query iam able to retrieve all the tables invloved in a stored proc.
but,what i want to display the table names as comma separated list for each table.

;WITH stored_procedures AS (
SELECT o.id,
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

Thanks,
javeed

Javeed Ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-07-08 : 09:44:15
;WITH stored_procedures AS (
SELECT o.id, o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT DISTINCT proc_name, STUFF((SELECT ','+table_name FROM stored_procedures WHERE id= p.id FOR XML PATH('')), 1, 1, '') TableNames
FROM stored_procedures p
WHERE row = 1

--
Chandu
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-07-10 : 04:59:49
Hi Chandu,
Did you notice,the table names are repeating in the output?

Javeed Ahmed
Go to Top of Page

Ranjay Kumar
Starting Member

3 Posts

Posted - 2014-07-10 : 05:57:10
Hiiii Javeed Ahmed

Use below code after stuff,You will never get Duplicate tables.

SELECT distinct ','+table_name FROM stored_procedures WHERE id= p.id FOR XML PATH('')), 1, 1, ''

Ranjay Kumar

ranjay
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-07-10 : 05:59:20
[code]SELECT distinct id,
proc_name,
table_name = stuff(tb.table_names, 1, 1, '')
FROM stored_procedures sp
cross apply
(
select ',' + table_name
from stored_procedures x
where x.id = sp.id
group by table_name
for xml path('')
) tb (table_names)
WHERE row = 1
ORDER BY proc_name, table_name[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Ranjay Kumar
Starting Member

3 Posts

Posted - 2014-07-10 : 06:00:46
[quote]Originally posted by Ranjay Kumar

Hiiii Javeed Ahmed

Use below code after stuff in Mr Chandu Query ,You will never get Duplicate tables.

SELECT distinct ','+table_name FROM stored_procedures WHERE id= p.id FOR XML PATH('')), 1, 1, ''

Ranjay Kumar


ranjay
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-07-10 : 06:09:58
great thanks

Javeed Ahmed
Go to Top of Page
   

- Advertisement -