Stored Procedure Need Help - 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 Need Help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-16 : 09:52:31

We created a view from two tables which stores Requestnumber(RQ) and purchaseid(PD).

There will be many purchaseid's associated with a a request number.So there will be many rows of data in the view for a request number

I want to write a stored procedure which when executed will copy all the purchaseid's into one column separated by coma(,)

Ex
Requestnumber Puchaseid
1 aab
1 bcc
1 c12
2 x13
2 y27

After Executing the stored procedure
should look like

Requestnumber Purchaseid
1 aab,bcc,c12
2 x13,y27

Thanks

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-16 : 09:59:58

[code]
SELECT
RQ.RequestNumber
, STUFF((
SELECT
', ' + PD.Purchaseid
FROM
dbo.Table1 AS PD
INNER JOIN dbo.Table2 AS RQ
ON PD.RequestNumber = RQ.RequestNumber
ORDER BY
PD.PurchaseID
FOR XML PATH('')
), 1, 2, '') AS PurchaseIDs
FROM
dbo.Table2 AS RQ

[/code]

I think I did that right.

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-16 : 10:10:08

we dont want to use the two tables in the stored procedures as the purchseid'sare saved differantly in the two tables
We created this view to bring consistency the way the data looks and the stored procedure needs to be using the view(RQPD)

Thanks

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-16 : 10:20:49

So, substitute in the view in place of the tables.

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-16 : 11:08:23

Tried to execute the below SQL and the query seemsed to run for ever
Also the reqults are not meaningful

SELECT
Requestno , STUFF((
SELECT
', ' + Purchaseid
FROM
VWRQPD
ORDER BY
Purchaseid FOR XML PATH('')
), 1, 2, '') AS Purchasids
FROM
VWRQPD

Thanks

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-16 : 11:27:30

Does this work? Keep in mind, I am guessing at table structure and data.


SELECT
	v1.Requestno 
	, STUFF((
		SELECT
			', ' + v2.Purchaseid
		FROM
			VWRQPD AS v2
		WHERE
			(v2.Requestno = v1.Requestno)
		ORDER BY
			v2.Purchaseid 
		FOR XML PATH('')
	), 1, 2, '') AS Purchasids
FROM
	VWRQPD AS v1

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-16 : 12:48:48

I tried the above and it gives me the same result as before
Query never stops and results not meaningful

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-16 : 12:55:45

OK, then. How about you post the table structure and some sample data.

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-17 : 08:19:59

Table 1 Structure
Requestnumber
Purchasid1
Purchaseid2
Purchaseid3
.
.
.
.
.
.
.
.
Purchaseid24
platform
city
State

Table 2 Structure
Requestnumber
purchaseid
platform
city
state

Combining both tables we created a new view

Requestnumber
Puchaseid
platform
city
state

Table1 was our old table which had limitations to save the purchaseid's(24).In the new table there is no more limitation on purchaseids
The view is created basically to transform the old table data to new format

Thanks

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-17 : 10:32:35

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-17 : 15:23:41

Can anyone please help me out here.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-17 : 15:41:43

Here is a link that might help you post your question so we can help you answer it. I did a quick reas through and I'm not even sure what then question is or which set of "tables" you want to use.

Maybe closing this thread and starting a new, more focused, one would help?

At anyrate, here is teh link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources