SQL: HOW to concatenate two fields using trigger,
-
Hi All,
I have SQL database called “S1”, and a table called “WORKORDER”, inside the table, I have two field on is called “ WORKORDERID” and the second field is called “SOURCEWOID” and would like to concantanate these two fields in one fields called “TEXT10”
For example, suppose I have the records in the first and second column as shown, and I would like to write concatenation results as shown in the field text10 manually using a trigger which depends on the “SOURSEWOID” field,“ WORKORDERID” SOURCEWOID” TEXT10 10 1 10,12 11 2 11 12 1 10,12 Anyone may send me the code and the trigger, I don’t have any experience in coding.
Thank you in advance
Best,
Majdoleen - Use a computed column instead.
CREATE TABLE dbo.S1 (WORKERORDERID varchar(10), SOURCEWOID varchar(10));
INSERT dbo.S1
(WORKERORDERID, SOURCEWOID)
VALUES
( '10', '1' )
, ( '11', '2' )
, ( '12', '1' );
SELECT
s.WORKERORDERID
, s.SOURCEWOID
FROM dbo.S1 s
ALTER TABLE dbo.S1 ADD TEXT10 AS Concat(WORKERORDERID, ',', SOURCEWOID)
SELECT
s.WORKERORDERID
, s.SOURCEWOID
, s.TEXT10
FROM dbo.S1 s;
DROP TABLE dbo.S1;
- Joe Torre - Friday, June 16, 2017 10:29 AMUse a computed column instead.
CREATE TABLE dbo.S1 (WORKERORDERID varchar(10), SOURCEWOID varchar(10));
INSERT dbo.S1
(WORKERORDERID, SOURCEWOID)
VALUES
( '10', '1' )
, ( '11', '2' )
, ( '12', '1' );
SELECT
s.WORKERORDERID
, s.SOURCEWOID
FROM dbo.S1 s
ALTER TABLE dbo.S1 ADD TEXT10 AS Concat(WORKERORDERID, ',', SOURCEWOID)
SELECT
s.WORKERORDERID
, s.SOURCEWOID
, s.TEXT10
FROM dbo.S1 s;
DROP TABLE dbo.S1;After looking twice at the original post, I'm not sure this is quite what the user wanted, but as there's no clarification, there's no way to be sure. The values that show up in the TEXT10 field do not agree with your results, and I suspect that the original poster may want to concatenate all the WORKORDERID values based on the SOURCEWOID values that match other records. That sounds like a disaster in a trigger if this table grows to any size. Much better to derive the values using a query. Even a computed column is likely a VERY BAD IDEA. Of course, we haven't heard from the original poster as to whether or not what you wrote or what I'm thinking is "what the user wants". I'll be happy to write that query if the original poster can at least clarify why his/her TEXT10 values disagree with the above query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thank you Joe Torre, but what sgmunson mentioned is right.I want to concatenate all the WORKORDERID values based on the SOURCEWOID values that match other records. the numbers are an example and not the real value, my table contains more than 5000 values.
I will be happy if you share your genetic query with me Sgmunson, the first post is not match my needs.Thank you in advance.
Best,
Majdoleen -
Any updates??
Thanks
Majdoleen -
I tried to write this code but seems not to work with me, and displayed the error attached.
CREATE TRIGGER dbo.My_TR_WORKORDER
ON dbo.WORKORDER
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
UPDATE U
SET[TEXT10] = COALESCE(CONVERT(varchar(20), I.WORKORDERID) + ',', '')
+ COALESCE(CONVERT(varchar(20), I.SOURCEWOID)
FROM inserted AS I
JOIN dbo.WORKORDER AS U
ON U.MyPKey1 = I.MyPKey1
AND U.MyPKey2 = I.MyPKey2
AND ... -
Your syntax problem: you need an extra `)` at the end of the line before the FROM section.
Thomas Rushton
blog: https://thelonedba.wordpress.com -
Thank you Thomas, But I think my code needs some enhancement, since I have a null values in the field "
SOURCEWOID", do you have any ideas how to fix this issue?Thank you in advance
Majdoleen -
Adding extra `)` at the end of the line before the FROM section hadn't resolve my issue, I think in my opinion it is due to Null values in the field "
SOURCEWOID", but I don't know how to fix this.
What do you think? - m.awadallah92 - Monday, June 19, 2017 2:49 AM
Check syntax for COALESCE to solve this problem.
Concatenating 2 columns or rows is a bad practice and a violation to 1NF. Please leave this concatenation for display only. - m.awadallah92 - Saturday, June 17, 2017 7:28 AM
Of all the things you need to worry about, chances are, using a TRIGGER or even a COMPUTED field, are both in the VERY BAD IDEA category. If the number of records that share a given value increases very much, or the number of overall rows in the table gets very big, the performance of an INSERT could degrade very quickly to the point where you have trouble doing so, or you experience significantly reduced concurrency. This kind of data can be computed for reporting purposes. Here's a query that can SELECT the data:
CREATE TABLE dbo.S1 (
WORKERORDERID varchar(10),
SOURCEWOID varchar(10)
);
INSERT dbo.S1 (WORKERORDERID, SOURCEWOID)
VALUES ('10', '1'),
('11', '2'),
('12', '1');SELECT S.WORKERORDERID, S.SOURCEWOID,
STUFF(
(
SELECT ',' + S2.WORKERORDERID
FROM dbo.S1 AS S2
WHERE S2.SOURCEWOID = S.SOURCEWOID
FOR XML PATH('')
), 1, 1, '') AS TEXT10
FROM dbo.S1 AS SDROP TABLE dbo.S1;
Performance will need to be tested.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thank you so much for your reply, I would appreciate if you try to send me a generic code depends on the name of my fields. I tried the one you send but seems not to work with my data base.
Feel free to download my Database and work on the table called "Azteca.WORKORDER"
http://www.mediafire.com/file/116dojhcv1cr1lr/S1_Database.rarI will be waiting your kindly response.
Thank you in advance
Best,
Majdoleen - m.awadallah92 - Tuesday, June 20, 2017 1:35 AM
The code posted works fine. For a detailed explanation on how the code works, read the following article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
P.S. I'm not downloading a database in a rar file, and many others won't either. For better help, read the articles linked in my signature.
- m.awadallah92 - Tuesday, June 20, 2017 1:35 AM
Sorry, but I have no means to "unzip" a .rar file. Also, you say only that it "seems not to work with my data base", but don't say what happened or what results you got. You're going to have to be a lot more specific. You can post at least the table create statement for this table, and then some INSERT statements with some sample data, and at least go so far as to test the query provided against that same sample data. Also state the results you got, along with exactly what happens when you run the query I already provided against the actual table. We just can't fix problems that are specified as "it doesn't seem to work".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
You can use WinRar
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply