how to join 2 table column data into single column-CodePudding

I did find some examples but they do not merge into single column. So, I am trying to join 2 table columns data into single column I have Url1, site1, url2, site2, endurl 5 columns in table1 and keywords column in table2

I want to join or merge these columns into one column like url1 site1 keywords,url2 site2 keywords endurl this will convert to a url generation just for understanding.

I tried

SELECT table1.Url1, table1.site1, table1.url2, table1.site2, table1.endurl, table2.keywords
    FROM table1 
LEFT JOIN table2
    ON table1.site1 = table2.keywords AND table1.site2 = table2.keywords;

want to merge all columns into single column.

CodePudding user response：

You could probably concat those columns into one.

SELECT CONCAT(t1.Url1,' ',t1.site1,' ',t2.keywords,',',t1.url2,' ',t1.site2,' ',t3.keywords)
FROM table1 t1, table2 t2, table2 t3
WHERE t1.site1 = t2.keywords AND t1.site2 = t3.keywords;

CodePudding user response：

What you're probably looking for is the format function which uses SQLite's builtin printf implementation. So, assuming your columns are all TEXT columns, this will give you what you're looking for:

SELECT format('%s, %s, %s, %s, %s, %s', table1.Url1, table1.site1, table1.url2, table1.site2, table1.endurl, table2.keywords) as my_column
    FROM table1
LEFT JOIN table2
    ON table1.site1 = table2.keywords AND table1.site2 = table2.keywords;