June 16, 2017 at 9:21 am
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
June 16, 2017 at 10:29 am
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;
June 16, 2017 at 2:22 pm
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)
June 17, 2017 at 7:28 am
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
June 18, 2017 at 4:26 am
Any updates??
Thanks
Majdoleen
June 18, 2017 at 1:33 pm
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 ...
June 18, 2017 at 3:04 pm
Your syntax problem: you need an extra `)` at the end of the line before the FROM section.
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 19, 2017 at 2:42 am
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
June 19, 2017 at 2:49 am
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?
June 19, 2017 at 7:08 am
m.awadallah92 - Monday, June 19, 2017 2:49 AMAdding 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?
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.
June 19, 2017 at 9:00 am
m.awadallah92 - Saturday, June 17, 2017 7:28 AMThank 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
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 S
DROP TABLE dbo.S1;
Performance will need to be tested.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2017 at 1:35 am
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.rar
I will be waiting your kindly response.
Thank you in advance
Best,
Majdoleen
June 20, 2017 at 6:47 am
m.awadallah92 - Tuesday, June 20, 2017 1:35 AMThank 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
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.
June 20, 2017 at 6:51 am
m.awadallah92 - Tuesday, June 20, 2017 1:35 AMThank 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
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)
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply