string formation - 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

SQL Server Development (2000)

string formation

Author

Topic

Saeed
Starting Member

39 Posts

Posted - 2002-12-16 : 19:46:44

I have a table with 2 columns
there is a one to many relationships betwen the first column and the 2nd one

How do I get to do a spr to return back the related C2s that belong to a C1
Has anyone got any already existing code that might do this.?

MyTable1

C1 C2
==== =====
100 10001
100 10002
100 899
101 2002
103 999
103 3238

i.e:
execute @MyString =sprGetMeAll 100
woudl return '10001,10002,899'

so i could form a new table

MyTable2

C1(int) S1 (Varchar)
=== ====
100 '10001,10002,899
101 '2002'
103 '999,3238'

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-16 : 19:51:44

Read my article on Converting Multiple Rows into a CSV String.

Jay White
{0}

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-16 : 22:15:43

This should work as well.

DECLARE @X TABLE (c1 varchar(10), c2 varchar(10), list varchar(100))
DECLARE @list varchar(8000),
@y int

INSERT INTO @x VALUES (100, 10001,NULL)
INSERT INTO @x VALUES (100, 10002,NULL)
INSERT INTO @x VALUES (100, 899,NULL)
INSERT INTO @x VALUES (103, 999,NULL)
INSERT INTO @x VALUES (103, 3238,NULL)

SET @y = (SELECT MIN(c1) FROM @x)
WHILE @y IS NOT NULL
BEGIN

SELECT @list = '' + @list + a.c2 + ','
FROM @x a
WHERE EXISTS (
SELECT 1 FROM @x b
WHERE a.c1 = b.c1 AND a.c1 = @y
)

SET @list = LEFT(@list,LEN(@list)-1)

UPDATE @x
SET
list = @list
WHERE c1 = @y

SET @list =''
SET @y = (SELECT MIN(c1) FROM @x WHERE c1 > @y)
END

SELECT c1, list FROM @x
GROUP BY c1, list

Saeed
Starting Member

39 Posts

Posted - 2002-12-16 : 22:55:02

quote:

I have a table with 2 columns
there is a one to many relationships betwen the first column and the 2nd one

How do I get to do a spr to return back the related C2s that belong to a C1
Has anyone got any already existing code that might do this.?

MyTable1

C1 C2
==== =====
100 10001
100 10002
100 899
101 2002
103 999
103 3238

i.e:
execute @MyString =sprGetMeAll 100
woudl return '10001,10002,899'

so i could form a new table

MyTable2

C1(int) S1 (Varchar)
=== ====
100 '10001,10002,899
101 '2002'
103 '999,3238'

for those seeking the same solution..
This spr does the job
CREATE PROCEDURE sprSlowGetJobs
@MCCN int ,
@MYSTR varchar(1000) output
as

DECLARE @JOBN int
DECLARE @CNTR int
Begin
SET NOCOUNT ON

declare con_cursor cursor for
select JOBN from MaterialPlan where MCCN = @MCCN
Set @MYSTR=''
Set @CNTR=0
open con_cursor
FETCH NEXT FROM CON_CURSOR into @JOBN
WHILE @@FETCH_STATUS =0

BEgin
set @CNTR=@CNTR+1
if @CNTR >1
begin
set @mystr=@mystr+','+convert(varchar(11),@JobN)
end
else
begin
set @mystr=+convert(varchar(11),@JobN)
end

FETCH NEXT FROM CON_CURSOR into @JOBN
END

close con_cursor
deallocate con_cursor

END

RETURN
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-16 : 22:58:31

Why would you want to use a cursor to do this? And without an ORDER BY clause, there's nothing to guarantee that the string gets built in the correct order.

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-16 : 23:48:32

And , I think the above code I wrote gives the result needed.

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources