CSV Function - 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

SQL Server 2000 Forums

Transact-SQL (2000)

CSV Function

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-17 : 11:55:36

Morning,

I'm trying to combine field values into one line.
I used the post http://www.sqlteam.com/item.asp?ItemID=2368 to get this:


DECLARE @po varchar(100)
select @PO  = COALESCE(@po + ', ', '') + CAST(PO AS varchar(50))
FROM tmpPickups
where ref = '1101188'
SELECT @po

Which works fine but I want to put it into an update statement that takes all the PO's for each reference id and puts it on one line. I was looking at making it a function but the one I have:


create function dbo.FnUpdatePos
(@ref varchar(20))
returns varchar(8000)
as
begin
DECLARE @PO varchar(100)
SELECT @PO = COALESCE(@PO + ',', '') + CAST(po AS varchar(50))
FROM vwSKPOs
WHERE ref = @ref
RETURN @PO
end

used in a stored procedure:


UPDATE tmpPickups
SET tmpPickups.PO = dbo.FnUpdatePos(vwSKPOs.ref)
FROM tmpPickups 
INNER JOIN dbo.vwSKPOs 
ON dbo.tmpPickups.ref = dbo.vwSKPOs.ref

doesn't work. I get one PO where there should be at lease 2 and the rest of the columns get set to null.

Any help you could provide would be much appreciated.

Thanks

Laura

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-17 : 12:54:30

I can give u a workaround

First backup ur table (Select * into bkup from urtbl)
Delete all records concerned
Insert using the Select statement
Insert Into Urtbl Select ....
or
Insert Into Urtbl exec (@SelectQuery)

Then Update all the other fields with data in bkup table

[I'm sure there may be a Update method, which would be much straight forward]

Kristen
Test

22859 Posts

Posted - 2006-01-18 : 01:20:03

"I get one PO where there should be at lease 2 "

Looks alright to me, although you probably ought to extend the size of the local variable; there's no chance that you've got a "vwSKPOs" owned by your User Name as well as one owned by "dbo" is there? (If not good practice to put the "dbo." prefix in on all tables/views/Sprocs/Functions/etc. to avoid ambiguity and save the optimiser checking each time!)


create function dbo.FnUpdatePos
(@ref varchar(20))
returns varchar(8000)
as
begin
DECLARE @PO varchar(100 8000)
SELECT @PO = COALESCE(@PO + ',', '') + CAST(po AS varchar(50))
FROM dbo.vwSKPOs
WHERE ref = @ref
RETURN @PO
end

What happens if you just execute this in Query Analyser):

SELECT dbo.FnUpdatePos('1101188')

and

SELECT CAST(po AS varchar(50))
FROM vwSKPOs
WHERE ref = '1101188'

??

Kristen

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-18 : 08:09:16

First thanks Kristen for the help:

Everything is prefixed by dbo.

when I run

SELECT dbo.FnUpdatePos('1101188')

88017,14705,14763

and

SELECT CAST(po AS varchar(50))
FROM vwSKPOs
WHERE ref = '1101188'

14705
14763
88017

Does that make it clearer or muddier?

Laura

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 08:23:20

Do you really want to updation or just Display them in CSV format?
If it is for Display, refer this also
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-18 : 08:51:19

I found the problem, Kristen was right. There was nothing wrong with the code. It was the fields in the table I changed them and now it works a treat!

Thanks to all.

Laura

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-18 : 09:17:13

quote:
Originally posted by madhivanan

Do you really want to updation or just Display them in CSV format?
If it is for Display, refer this also
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

That is the key question; Laura -- I hope you got a chance to think about that before doing any updates on your tables. Don't confuse the need to store data in a certain format with the ability to display it in a query (or at the presentation layer).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources