Hello, I am creating a table and I would want two particular fields to concatenate to form a 3rd field. Take for instance:
___________________________________________
class int not null,
studentNo int unique IDENTITY(100000,1) Primary Key,
StudentCode...
_____________________________________________
Class field will be 3-digits integer. StudentNo will be automatically generated, starting from 10,000 StudentCode (I want it to contain the class 3-digits for each student and also the studentNo. It will start with the 3-digits of the class. i.e to concatenate the class value and the studentNo value) I try as much to explain because I want explicit suggestion. If you are proficient with database (MySql or Microsoft Sql), and you know about this, then kindly help. Please help with a line or lines of codes.
In Alteryx you can wrap a column with ToString([ColumnName]) to effectively cast it to a string. You can also use a select tool if you want to modify the column data type.
Hi @CEO ,
You can do this in two was.
1. Concatenate within Alteryx:- Please refer to the attached sample workflow on how to do this. Within the workflow i have used a Text Input just for showcasing. You will need to replace this with your input to the actual database.
2. Concatenate using SQL:- You can do this by executing a query similar to one described below.
SELECT CONCAT(c.Class, ':', c.studentNo) AS studentCode,
c.*
FROM `tablename` c;
Hope this helps!
Best,
Jagdeesh
Thanks for your time. But using the SELECT command would only make it effective for that command. I would want it my table in the database to be programmed with that. I want the studentCode field to be affected by that command whenever a record or an update is made. How do I go about it please without using a SELECT statement?
@jagdeeshN
...and I will appreciate if you kindly write the line of code as if you're teaching a beginner, so I can study it and understand. I don't understand what you wrote there
I want to write the code in my database server, not in my application code.
Hi @CEO ,
Please check the below link. It describes how to create virtual calculated columns within a table.
https://www.sqlshack.com/an-overview-of-computed-columns-in-sql-server/
Hope this helps.
Best,
Jagdeesh