if I have table1 like this,

I want to bind with table2, like below, and conut.

I want to get this like that

How can I achieve in SQL, Thank you.

CodePudding user response：

You can't do this with standard SQL but for example MS SQL has a PIVOT operator. Also Oracle has it. PostgreSQL has a CROSSTAB table function. See this article for more information.

And you can always write a stored procedure if it is supported by your DBMS.

CodePudding user response：

Getting the row-wise name values into column names in the output calls for a pivot technique, as explained in the first answer.

In order to count codes by name, you will also need a device to multiply rows based on code : two rows with 001 for name Tom, one row with 001 and one with 002.2 for name Mary, etc. Like so :

 ---- ----- 
|name|code |
 ---- ----- 
|Tom |001  |
|Tom |001  |
|Mary|001  |
|Mary|002.2|
|Mike|002.2|
|Mike|001  |
|Amy |003  |
|Amy |003  |
 ---- ----- 

which is easy to count(*) ... group by (name, code), yielding a pivotable result.

How to achieve this may vary depending on your rdbms, here's my take in postgresql :

select table1.name, name_code.code, table2.string
from table1
-- create a row for each of (test2, test 2) of each row
cross join unnest(array[test1, test2]) name_code(code)
join table2 on name_code.code = table2.code

dbfiddle (without pivot)