Very Computer

Mark, you must concatenate  letters  group by t1f2 and
use dynamic sql (it is might stored procedure that get parameter like
'abc' )

as

Quote:> I haven't been able to figure this one out yet. Maybe someone can tell me

why this isn't working. I am trying to concatenate an updated field onto
itself (SET t1.t1f2 = t1.t1f2 + t2.t2f2 + ','). Judging by the results, only
the last record read where the first column in both tables matches is being
stored in the updated table.

Quote:

> Here's an example

> Table 2
> col1  col2
> ----------
> 1     a
> 1     b
> 1     c
> 2     r
> 2     s

> Table 1 before update
> t1
> col1  col2
> ----------
> 1     DEFAULT
> 2     DEFAULT

> After the update, table 1 should look like
> t1
> col1  col2
> ----------
> 1     DEFAULT,a,b,c,
> 2     DEFAULT,r,s,

> instead the update only produces
> t1
> col1  col2
> ----------
> 1     DEFAULT,c,
> 2     DEFAULT,s,

> Here's the code and output

> CREATE TABLE t1
> (
> t1f1 int,
> t1f2 varchar(50) DEFAULT 'DEFAULT,'
> )

> CREATE TABLE t2
> (
> t2f1 int,
> t2f2 varchar(10)
> )

> INSERT INTO t2
> (
> t2f1,
> t2f2
> )(
> SELECT 1, 'a'
> UNION
> SELECT 1, 'b'
> UNION
> SELECT 1, 'c'
> UNION
> SELECT 2, 'e'
> UNION
> SELECT 2, 'f'
> UNION
> SELECT 2, 'g'
> UNION
> SELECT 3, 'h'
> )

> INSERT INTO t1
> (
> t1f1
> )(
> SELECT DISTINCT
> t2f1
> FROM t2
> )

> UPDATE t1
> SET t1f2 = t1f2 + t2f2 + ','
> FROM t2, t1
> WHERE t1f1 = t2f1
> -- I have also tried full outer join, with the same result

> PRINT 'Contents of t1 after update'
> SELECT *
> FROM t1

> SELECT *
> FROM t2

> DROP TABLE t1
> DROP TABLE t2

> OUTPUT:
> --------

> (7 row(s) affected)

> (3 row(s) affected)

> (3 row(s) affected)

> Contents of t1 after update
> t1f1        t1f2
> ----------- --------------------------------------------------
> 1           DEFAULT,c,
> 2           DEFAULT,g,
> 3           DEFAULT,h,

> (3 row(s) affected)

> t2f1        t2f2
> ----------- ----------
> 1           a
> 1           b
> 1           c
> 2           e
> 2           f
> 2           g
> 3           h

> (7 row(s) affected)

Hi Mark

This will work and I can't think of a better way at the moment!

CREATE PROCEDURE prConcatenateF2 AS
BEGIN


DECLARE t2cursor INSENSITIVE CURSOR FOR
SELECT t2f1, t2f2 FROM t2 ORDER BY t2f1
OPEN t2cursor


BEGIN
 UPDATE t1



END
CLOSE t2cursor
DEALLOCATE t2cursor
END

I've assumed that the initialisation of t1f2 does not have the comma and you
don't want the trailing comma. You may want to exclude null t2f2 in the
cursor.

John

Quote:> I haven't been able to figure this one out yet. Maybe someone can tell me

why this isn't working. I am trying to concatenate an updated field onto
itself (SET t1.t1f2 = t1.t1f2 + t2.t2f2 + ','). Judging by the results, only
the last record read where the first column in both tables matches is being
stored in the updated table.

Quote:

> Here's an example

> Table 2
> col1  col2
> ----------
> 1     a
> 1     b
> 1     c
> 2     r
> 2     s

> Table 1 before update
> t1
> col1  col2
> ----------
> 1     DEFAULT
> 2     DEFAULT

> After the update, table 1 should look like
> t1
> col1  col2
> ----------
> 1     DEFAULT,a,b,c,
> 2     DEFAULT,r,s,

> instead the update only produces
> t1
> col1  col2
> ----------
> 1     DEFAULT,c,
> 2     DEFAULT,s,

> Here's the code and output

> CREATE TABLE t1
> (
> t1f1 int,
> t1f2 varchar(50) DEFAULT 'DEFAULT,'
> )

> CREATE TABLE t2
> (
> t2f1 int,
> t2f2 varchar(10)
> )

> INSERT INTO t2
> (
> t2f1,
> t2f2
> )(
> SELECT 1, 'a'
> UNION
> SELECT 1, 'b'
> UNION
> SELECT 1, 'c'
> UNION
> SELECT 2, 'e'
> UNION
> SELECT 2, 'f'
> UNION
> SELECT 2, 'g'
> UNION
> SELECT 3, 'h'
> )

> INSERT INTO t1
> (
> t1f1
> )(
> SELECT DISTINCT
> t2f1
> FROM t2
> )

> UPDATE t1
> SET t1f2 = t1f2 + t2f2 + ','
> FROM t2, t1
> WHERE t1f1 = t2f1
> -- I have also tried full outer join, with the same result

> PRINT 'Contents of t1 after update'
> SELECT *
> FROM t1

> SELECT *
> FROM t2

> DROP TABLE t1
> DROP TABLE t2

> OUTPUT:
> --------

> (7 row(s) affected)

> (3 row(s) affected)

> (3 row(s) affected)

> Contents of t1 after update
> t1f1        t1f2
> ----------- --------------------------------------------------
> 1           DEFAULT,c,
> 2           DEFAULT,g,
> 3           DEFAULT,h,

> (3 row(s) affected)

> t2f1        t2f2
> ----------- ----------
> 1           a
> 1           b
> 1           c
> 2           e
> 2           f
> 2           g
> 3           h

> (7 row(s) affected)

Quote:> This will work and I can't think of a better way at the moment!

Let me help you.

This will allow u to easily create a concatenated delimited string
for any column(s) for any group by column(s) without using
cursors or UDF's.Then u can use the concatenated string in
the result to easily update a column in another table using
an Update statement.

www.rac4sql.net

Hey Mark.
Try this:

select col1, cast(col2 as varchar(50)) as col2 into #TempTable
from Table2 order by col1, col2

update #TempTable

else '' end + col2,

update Table1
set col2 = Table1.col2 + ', ' + a.col2
from #TempTable a
where a.col2 = (select max(#TempTable.col2) from #TempTable where
#TempTable.col1 = a.col1)
and a.col1 = Table1.col1
select * from Table1

drop table #TempTable

--

Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience  http://launch.yahoo.com
Brainbench MVP for Visual Basic   www.brainbench.com

Quote:> I haven't been able to figure this one out yet. Maybe someone can tell me

why this isn't working. I am trying to concatenate an updated field onto
itself (SET t1.t1f2 = t1.t1f2 + t2.t2f2 + ','). Judging by the results, only
the last record read where the first column in both tables matches is being
stored in the updated table.

Quote:

> Here's an example

> Table 2
> col1  col2
> ----------
> 1     a
> 1     b
> 1     c
> 2     r
> 2     s

> Table 1 before update
> t1
> col1  col2
> ----------
> 1     DEFAULT
> 2     DEFAULT

> After the update, table 1 should look like
> t1
> col1  col2
> ----------
> 1     DEFAULT,a,b,c,
> 2     DEFAULT,r,s,

> instead the update only produces
> t1
> col1  col2
> ----------
> 1     DEFAULT,c,
> 2     DEFAULT,s,

> Here's the code and output

> CREATE TABLE t1
> (
> t1f1 int,
> t1f2 varchar(50) DEFAULT 'DEFAULT,'
> )

> CREATE TABLE t2
> (
> t2f1 int,
> t2f2 varchar(10)
> )

> INSERT INTO t2
> (
> t2f1,
> t2f2
> )(
> SELECT 1, 'a'
> UNION
> SELECT 1, 'b'
> UNION
> SELECT 1, 'c'
> UNION
> SELECT 2, 'e'
> UNION
> SELECT 2, 'f'
> UNION
> SELECT 2, 'g'
> UNION
> SELECT 3, 'h'
> )

> INSERT INTO t1
> (
> t1f1
> )(
> SELECT DISTINCT
> t2f1
> FROM t2
> )

> UPDATE t1
> SET t1f2 = t1f2 + t2f2 + ','
> FROM t2, t1
> WHERE t1f1 = t2f1
> -- I have also tried full outer join, with the same result

> PRINT 'Contents of t1 after update'
> SELECT *
> FROM t1

> SELECT *
> FROM t2

> DROP TABLE t1
> DROP TABLE t2

> OUTPUT:
> --------

> (7 row(s) affected)

> (3 row(s) affected)

> (3 row(s) affected)

> Contents of t1 after update
> t1f1        t1f2
> ----------- --------------------------------------------------
> 1           DEFAULT,c,
> 2           DEFAULT,g,
> 3           DEFAULT,h,

> (3 row(s) affected)

> t2f1        t2f2
> ----------- ----------
> 1           a
> 1           b
> 1           c
> 2           e
> 2           f
> 2           g
> 3           h

> (7 row(s) affected)