Merge Two Tables/Concatenate Values into Single Column

Merge two tables / concatenate values into single column

Check this...

IF OBJECT_ID('TableA') IS NOT NULL DROP TABLE TableA
IF OBJECT_ID('TableB') IS NOT NULL DROP TABLE TableB
CREATE TABLE TableA (ID  INT,  Color  VARCHAR(max), Location VARCHAR(max), Class VARCHAR(max))
CREATE TABLE TableB (child_ID  INT,  parent_ID INT,  Color  VARCHAR(10), Location VARCHAR(10), Class VARCHAR(10))
INSERT INTO TableB 
      SELECT 1,1,'white','house'  ,'I' UNION SELECT 2,2,'red'  ,'garage' ,'II'
UNION SELECT 3,2,'white','garage' ,'I' UNION SELECT 4,3,'blue' ,'house'  ,'IV'
UNION SELECT 5,3,'blue' ,'garage' ,'I' UNION SELECT 6,3,'white','garage' ,'I'
UNION SELECT 7,3,'gray' ,'garage' ,'I' UNION SELECT 8,2,'gray' ,'house'  ,'IV'

SELECT * FROM TableB

DECLARE @cmd VARCHAR(max);
SET @cmd = 'INSERT INTO TableA SELECT ID = b.parent_id '
SELECT @cmd = @cmd + ' , ['+COLUMN_NAME+'] = STUFF(
            (   SELECT '', '' +'+COLUMN_NAME+' 
                FROM TableB 
                WHERE parent_id = b.parent_id
                GROUP BY '+COLUMN_NAME+' 
                FOR XML PATH('''') 
            ) , 1, 2, '''' )'
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='TableB' AND COLUMN_NAME NOT IN ('child_ID', 'parent_id')

SELECT @cmd = @cmd + ' FROM TableB AS b GROUP BY b.parent_id'

EXEC(@cmd)
SELECT * FROM TableA

/* -- OUTPUT
  ID | Color             | Location      | Class
  ---------------------------------------------------
  1  | white             | house         | I
  2  | gray, red, white  | garage, house | I, II, IV
  3  | blue, gray, white | garage, house | I, IV
*/

How can I merge the columns from two tables into one output?

Specifying the columns on your query should do the trick:

select a.col1, b.col2, a.col3, b.col4, a.category_id 
from items_a a, items_b b 
where a.category_id = b.category_id

should do the trick with regards to picking the columns you want.

To get around the fact that some data is only in items_a and some data is only in items_b, you would be able to do:

select 
  coalesce(a.col1, b.col1) as col1, 
  coalesce(a.col2, b.col2) as col2,
  coalesce(a.col3, b.col3) as col3,
  a.category_id
from items_a a, items_b b
where a.category_id = b.category_id

The coalesce function will return the first non-null value, so for each row if col1 is non null, it'll use that, otherwise it'll get the value from col2, etc.

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

Join two tables and concatenate multiple rows of a single field

SELECT T1.ID,
      STUFF((SELECT ', ' + T2.Value
             FROM   #Temp2 T2 
             WHERE  T1.ID = T2.ID
             FOR XML PATH(''),TYPE)
             .value('.','NVARCHAR(MAX)'),1,2,'') AS Value
FROM #Temp1 T1 
GROUP BY T1.ID

For SQL Server 2017 and Later Versions

SELECT T1.ID, STRING_AGG(T2.Value , ', ') AS [Value]
FROM #Temp1 T1 
INNER JOIN #Temp2 T2 ON T1.ID = T2.ID
GROUP BY T1.ID

Result Set

╔════╦═══════════════════════════╗
║ ID ║           Value           ║
╠════╬═══════════════════════════╣
║  1 ║ One-One, One-Two, One-One ║
║  2 ║ Two-One, Two-Two, Two-One ║
╚════╩═══════════════════════════╝

How to combine multiple rows from 4 tables into one single row in a new table in SQL?

There are lot of ways to achieve this. You can use STUFF and XML PATH or you can use a user defined function.

The easiest method would be add user defined function and use it wherever you want.You don't even have to create the function.It's already available at GROUP_CONCAT string aggregate for SQL Server.

You can use it as follows:

SELECT some_id,
       dbo.GROUP_CONCAT(some_column) AS as delimited_list
FROM dbo.some_table
GROUP BY some_id;

It gives an output like:

some_id           delimited_list
----------------- -----------------------------------
1                 red,green,blue
2                 cyan,magenta,yellow,key

OR

You can try using STUFF and XML PATH as I said before.

SELECT t.trans_type
      ,t.trk_link
      ,t.service_db     
      ,t.scn
      ,t.given_nm
      ,t.surname_nm
      ,t.cc
      ,t.sex
      ,t.grade
      ,t.dob
      ,t.marital_st
      ,t.pob_city
      ,t.pob_cntry
      ,CASE t.UScitizenship_CD
          WHEN 'H' THEN 'Holds'
          WHEN 'DNH' THEN 'Does Not Hold'
          else ''
          End as Student_Citizenship_Status

      --,t.UScitizenship_CD
      ,t.trk_link20
      ,p.pass_nbr
      --,v.pass_nbr as VisaNo,
      ,STUFF((SELECT VisaNo  
               from VI as v
               where v.[trk_link]=p.[trk_link] 
               FOR XML PATH('')),1,1,'') as VisaNumbers
      ,CONCAT(p2.given_nm, ' ', p2.surname_nm) As DepName
      --,p2.given_nm
      --,p2.surname_nm
      ,p2.dep_rel
      ,p2.birth_dt
      ,CASE p2.UScitizenship_CD
          WHEN 'H' THEN 'Holds'
          else 'Does Not Hold'
          End as Dependent_Citizenship_Status

from TP t
inner join P p
on t.[trk_link] = p.[trk_link]
--inner Join VI v
--on p.[trk_link] = v.[trk_link]  
inner join PD p2 on t.[trk_link] = p2.[trk_link]
where t.trk_link = '22985200458053000003171117104111'

Note: In the description it was given that VisNumber is saved as VisaNo in v table.But in your query you are trying to select pass_nbr. So, I'm not sure how its saved in your db. So do the changes accordingly.

MySQL combine two columns into one column

My guess is that you are using MySQL where the + operator does addition, along with silent conversion of the values to numbers. If a value does not start with a digit, then the converted value is 0.

So try this:

select concat(column1, column2)

Two ways to add a space:

select concat(column1, ' ', column2)
select concat_ws(' ', column1, column2)

Merge data from two tables into single column of another table

The SELECT appears correct (you may want to use UNION ALL instead of UNION to avoid elimination of duplicates).

If you want the results to be in the third table C, you need to make an INSERT from your SELECT, like this:

INSERT INTO C (Col1)
(
    SELECT Col1 from A
UNION ALL
    SELECT Col1 from B
)

---

Related Topics