How do I join two tables and create an additional column with comma seperated values

joining a table based on comma separated values in sql server
how to get comma separated values in mysql query
how to insert comma separated values in mysql
comma separated values in mysql where clause
comma separated tables in sql
display comma separated value row wise mysql
join by comma sql server
remove value from comma separated string mysql

I don't have much experience with SQL apart from CRUD queries; please forgive me if this is trivial. I am trying to join two tables and create an additional column in the results with a series of comma seperated values.

Table 1

Table 2

I can get the comma seperated values from table 2 using

DECLARE @tmp varchar(MAX)
SET @tmp = ''
SELECT @tmp = @tmp + CONVERT(VARCHAR(10), [materialTypeID])  + ', '
FROM [industryActivityMaterials] 
WHERE typeID = 1145 AND activityID =  1
SELECT SUBSTRING(@tmp, 0, LEN(@tmp)) AS materialsRequired

I can't figure out though how to join the tables together on the typeID to contain a set of results containing the column of materialsReguired.

You can join, aggregate, and use aggregate function string_agg() to generate the comma separated list. This function is available since SQL Server 2017.

select 
    t1.typeID, 
    t1.activityID,
    t1.activityName,
    t.Blueprint,
    t1.productTypeID,
    t1.ProductName,
    string_agg(t2.materialTypeID, ',') 
        within group (order by t2.materialTypeID) materialTypeIDs
from 
    table1 t1
    inner join table2 t2
        on  t2.typeID = t1.typeID
        and t2.activityID = t1.activityID
group by
    t1.typeID, 
    t1.activityID,
    t1.activityName,
    t.Blueprint,
    t1.productTypeID,
    t1.ProductName

How to join two tables using a comma-separated-list in the join field , Using comma separated lists in a database field is an anti-pattern and should be avoided at all costs. Because it is a PITA to extract those� Split comma separated values into columns with Text to Columns function. You can apply the Text to Columns function to split comma separated values of selected cells into columns. Please do as follows. 1. Select the range of cells you want to split values into columns, and then click Data > Text to Columns. See screenshot: 2.

below 2016 sql server version you can use xml path to group by string concat

SELECT 
  T1.typeId,
  STUFF((
    SELECT ', ' + materialTypeID 
    FROM Table2 T2 
    WHERE (T2.typeId = T1.typeId) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS materialsRequired
FROM Table1 T1
GROUP BY T1.typeId

over sql server 2016 version you can use string_agg.

STRING_AGG (Transact-SQL) - SQL Server | Microsoft Docs

MySQL joining two tables when ID from one table is in comma string , MySQL joining two tables when ID from one table is in comma string in other table styles (ID's) are stored in a TEXT file as a comma separated list (e.g. 1,2,3 ). This query is to populate a “filtering” list - so as other filters/queries reduce please give CREATE TABLE statements, as well as INSERT INTO� Merging two tables into new table by ID and date. sql,sql-server,phpmyadmin. You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some

As a note, regardless of approach, you might find a correlated subquery simpler than a big aggregation:

select t1.*,
       (select string_agg(t2.materialTypeID, ',') within group (order by t2.materialTypeID)
        from table2 t2
        where t2.typeID = t1.typeID and
              t2.activityID = t1.activityID
       ) as materialTypeIds
from table1 t1;

Or:

select t1.*,
       stuff( (select concat(',', t2.materialTypeID) 
               from table2 t2
               where t2.typeID = t1.typeID and
                     t2.activityID = t1.activityID
               for xml path ('')
              ), 1, 1, ''
            ) as materialTypeIds
from table1 t1;

In general, avoiding the outer aggregation improves performance, especially if there is an index on table2(typeID, activityId).

MySQL join by field with comma seperated list - Databases, So for example I want to do a join on the table below: SELECT jobs.job_title, is stored by id (but there can be more that one and the IDs are separated by comma )… join on a comma-delimited column, but it will always require a table scan ( and Would I need an ID field (primary key) or would it just be best to create an� The fourth argument is the optional ‘[text2, …]’ which is available for additional text items to be joined to ‘text1’. Including ‘text1’, this argument can be used as many times to join a single text strings or ranges of text strings to create a maximum total of 252 text arguments.

Join multiple tables related by a comma-separated list of IDs , else new fee particular with all other non defined fee particular select * from FeePreference pref inner join FeeParticular part -- this line matches (Id) ); create table FeeParticular ( Id smallint not null, -- plus all the other columns Then your join doesn't have to deal with parsing out that string column: Example – Null Values. If your result set contains null values, those values are ignored and a corresponding separator is not added. If this isn’t suitable, you can provide a value for null values by using the ISNULL() function and passing in the value you’d like to use whenever a null value is encountered. Doing this ensures that you

Join with comma separated values in SQL Server, You really should have a junction table for the courses a student is Since your comma-separated values are numeric, you can get away CREATE FUNCTION dbo. CourseId char(2), CourseName char(3) ); create table dbo. and assuming that function returns a column called item for each list item): This result assumes that the AnimalId column is an IDENTITY column (incrementing values are inserted automatically with each new row). The Full Script. Here’s a script that allows you to do the above example in one go. It creates the database, creates the table, inserts the data, then checks the results. Create the database:

php, This would be a much simpler task if you had another table that joins your products to categories i.e. productstocategories productid categoryid 1 2 1 4 1 5 1 6 2� Each computer record has three values and is seperated by a / and then if more than one record those are seperated by commas. Here is an example of the table I would be pulling from and the table I want to put the data in.-- Table to pull data from CREATE TABLE TABLEONE (USER_ID INT, COMPUTER_RCD VARCHAR2(1000));-- Two Computers INSERT INTO

Comments
  • Thanks Gordon. I will try your apporach also.