Pankaj Gupta Pankaj Gupta - 2 months ago 5
MySQL Question

Need to convert a table rows to column in another table based on Table 1 elements condition

I have 2 tables T1 and T2.
I want to insert data into T2 based on conditions of T1.

Say T1 has data

SID LinkName
S1 Link1
S1 Link2
S2 Link1
S2 Link2
S2 Link3
S3 Link2


I want to insert data into a new table T2 having above Link1, Link2 etc as columns in such a way that if S1 has link 1 then its column for Link1 would show Yes and so on.
Example for S3, column of Link1 and Link3 will show NO, Link2 Column will show YES

SID Link1 Link2 Link3
S1 Yes Yes No
S2 Yes Yes Yes
S3 No Yes No

Answer

Just run a query:

select sid,
       max(case when LinkName = 'Link1' then 'yes' else 'no' end) as Link1,
       max(case when LinkName = 'Link2' then 'yes' else 'no' end) as Link2,
       max(case when LinkName = 'Link3' then 'yes' else 'no' end) as Link3
from t1
group by sid;

You can use create table as or insert to add this information to another table.

Note: This uses the fact that 'yes' > 'no'.