Table to comma delimited

20 Jan 2006 8:59 PM

Matt Sonic

Looking for a little function/trigger writting help.

I need to convert data in one column of a table/view to a comma delimited
string in a single field in a related row on another table. I want to use a
trigger for when a table is either appended, deleted or updated in SQL
Server. Can you help me with this?

For Example;
If I just added '1    Matt' to table 1, I want to have a trigger to update a
column in another table where column A = 1 to 'Matt, Mark, Tom'.

Table 1
Col A Col B
1          Matt
1          Mark
1          Tom

Table 2
Col A     Col B
1          Matt, Mark, Tom,

Mucho Gracies. < I may also need help spelling this since I am not spanish!

Go Steelers !!!!!

20 Jan 2006 9:38 PM

Anith Sen

>> If I just added '1 Matt' to table 1, I want to have a trigger to
>> update a column in another table where column A = 1 to 'Matt, Mark, Tom'.

Is this table being used as a source for client side display or report?

As for the answer: http://tinyurl.com/ap2gm

--
Anith

23 Jan 2006 1:22 PM

Matt Sonic

I am creating this column because I want to filter the records in table 2 by
one of the values in table 1 without including table one in the sql as to
avoid duplicating records in the results. The relationship between table 1
and 2 is a many-to-many. Am I right to do this? Is there a bette way? I am
querying this from web pages build with Dreamweaver and using LIKE
'*parameter*' in the sql.

Show quote

"Anith Sen" wrote:

> >> If I just added '1 Matt' to table 1, I want to have a trigger to
> >> update a column in another table where column A = 1 to 'Matt, Mark, Tom'.
>
> Is this table being used as a source for client side display or report?
>
> As for the answer: http://tinyurl.com/ap2gm
>
> --
> Anith
>
>
>

20 Jan 2006 11:07 PM

Mark Williams

create table testz (cola int, colb varchar(500), PRIMARY KEY (cola,colb))
GO
create table testy (cola int, colb varchar(500), PRIMARY KEY (colb))
GO

INSERT INTO testz VALUES (1, 'Mark')
INSERT INTO testz VALUES (1, 'Jerry')
INSERT INTO testz VALUES (1, 'Bob')
INSERT INTO testy VALUES (1, 'Mark,Jerry,Bob')

CREATE TRIGGER concat_trigger ON testz
FOR INSERT, UPDATE, DELETE
AS
BEGIN

DECLARE @insertedcount int
DECLARE @deletedcount int
SELECT @insertedcount = COUNT(*) FROM inserted
SELECT @deletedcount = COUNT(*) FROM deleted

IF (@deletedcount = 0) --an insert
BEGIN
    PRINT 'Insert statement'

    UPDATE testy SET colb = testy.colb + inserted.colb + ','
    FROM testy INNER JOIN inserted ON testy.cola = inserted.cola
    INNER JOIN testz ON inserted.cola = testz.cola

    INSERT INTO testy SELECT i.cola, i.colb + ','
    FROM inserted i WHERE i.cola NOT IN (SELECT cola FROM testy)

END

IF (@insertedcount = 0) --a delete
BEGIN
    PRINT 'Delete statement'
    UPDATE testy SET colb = REPLACE(testy.colb, deleted.colb + ',', '')
FROM testy,deleted
    WHERE testy.cola = deleted.cola
END

IF (@insertedcount = @deletedcount) --an update statement
BEGIN
    PRINT 'Update statement'
    UPDATE testy SET colb = REPLACE(testy.colb, deleted.colb, inserted.colb)
    FROM testy INNER JOIN inserted ON testy.cola = inserted.cola
    INNER JOIN deleted ON inserted.cola = deleted.cola
END
END

INSERT INTO testz VALUES (2, 'Zebra')
select * from testy

INSERT INTO testz VALUES (1, 'Bill')
select * from testy

DELETE FROM testz WHERE cola = 1 AND colb = 'Bob'
select * from testy

UPDATE testz SET colb = 'Adam' WHERE cola = 1 AND colb = 'Jerry'
select * from testy

If you can get your current data into the right format, the above trigger
should do the trick. There's one thing left over though, and that's cleaning
up any rows in testy that don't have a corresponding value for cola in testz
(I'll leave that up to you).

--

Show quote

"Matt Sonic" wrote:

> Looking for a little function/trigger writting help.
>
> I need to convert data in one column of a table/view to a comma delimited
> string in a single field in a related row on another table. I want to use a
> trigger for when a table is either appended, deleted or updated in SQL
> Server. Can you help me with this?
>
> For Example;
> If I just added '1    Matt' to table 1, I want to have a trigger to update a
> column in another table where column A = 1 to 'Matt, Mark, Tom'.
>
> Table 1
> Col A Col B
> 1          Matt
> 1          Mark
> 1          Tom
>
> Table 2
> Col A     Col B
> 1          Matt, Mark, Tom,
>
>
> Mucho Gracies. < I may also need help spelling this since I am not spanish!
>
> Go Steelers !!!!!

23 Jan 2006 2:20 PM

Matt Sonic

Thanks Mark! That is even better than what I was intending on doing which
was use a UDF to aggregate/concatinate the values.

Show quote

"Mark Williams" wrote:

> create table testz (cola int, colb varchar(500), PRIMARY KEY (cola,colb))
> GO
> create table testy (cola int, colb varchar(500), PRIMARY KEY (colb))
> GO
>
> INSERT INTO testz VALUES (1, 'Mark')
> INSERT INTO testz VALUES (1, 'Jerry')
> INSERT INTO testz VALUES (1, 'Bob')
> INSERT INTO testy VALUES (1, 'Mark,Jerry,Bob')
>
>
> CREATE TRIGGER concat_trigger ON testz
> FOR INSERT, UPDATE, DELETE
> AS
> BEGIN
>
>   DECLARE @insertedcount int
>   DECLARE @deletedcount int
>   SELECT @insertedcount = COUNT(*) FROM inserted
>   SELECT @deletedcount = COUNT(*) FROM deleted
>
>   IF (@deletedcount = 0) --an insert
>   BEGIN
>     PRINT 'Insert statement'
>
>     UPDATE testy SET colb = testy.colb + inserted.colb + ','
>     FROM testy INNER JOIN inserted ON testy.cola = inserted.cola
>     INNER JOIN testz ON inserted.cola = testz.cola
>
>     INSERT INTO testy SELECT i.cola, i.colb + ','
>     FROM inserted i WHERE i.cola NOT IN (SELECT cola FROM testy)
>
>   END
>
>   IF (@insertedcount = 0) --a delete
>   BEGIN
>     PRINT 'Delete statement'
>     UPDATE testy SET colb = REPLACE(testy.colb, deleted.colb + ',', '')
> FROM testy,deleted
>     WHERE testy.cola = deleted.cola
>   END
>
>   IF (@insertedcount = @deletedcount) --an update statement
>   BEGIN
>     PRINT 'Update statement'
>     UPDATE testy SET colb = REPLACE(testy.colb, deleted.colb, inserted.colb)
>     FROM testy INNER JOIN inserted ON testy.cola = inserted.cola
>     INNER JOIN deleted ON inserted.cola = deleted.cola
>   END
> END
>
> INSERT INTO testz VALUES (2, 'Zebra')
> select * from testy
>
> INSERT INTO testz VALUES (1, 'Bill')
> select * from testy
>
> DELETE FROM testz WHERE cola = 1 AND colb = 'Bob'
> select * from testy
>
> UPDATE testz SET colb = 'Adam' WHERE cola = 1 AND colb = 'Jerry'
> select * from testy
>
>
> If you can get your current data into the right format, the above trigger
> should do the trick. There's one thing left over though, and that's cleaning
> up any rows in testy that don't have a corresponding value for cola in testz
> (I'll leave that up to you).
> --
>
>
>
> "Matt Sonic" wrote:
>
> > Looking for a little function/trigger writting help.
> >
> > I need to convert data in one column of a table/view to a comma delimited
> > string in a single field in a related row on another table. I want to use a
> > trigger for when a table is either appended, deleted or updated in SQL
> > Server. Can you help me with this?
> >
> > For Example;
> > If I just added '1    Matt' to table 1, I want to have a trigger to update a
> > column in another table where column A = 1 to 'Matt, Mark, Tom'.
> >
> > Table 1
> > Col A Col B
> > 1          Matt
> > 1          Mark
> > 1          Tom
> >
> > Table 2
> > Col A     Col B
> > 1          Matt, Mark, Tom,
> >
> >
> > Mucho Gracies. < I may also need help spelling this since I am not spanish!
> >
> > Go Steelers !!!!!