How to make multiple row to one row

Question

i have mysql table like this :

based on table 1, i want to merge row in colomn mayor and minor with the same NIP.The result like this :

How to make it?

Answer 1

So the sql should be very simple:

select * from Table1 t, Results r where t.nip = r.nip and t.mayor=r.mayor;

I am not sure if MySQL is case sensitive to column names , so you'd might have to capitalize the column/table names in the query.

I do recommend you to read about database joins. http://en.wikipedia.org/wiki/Join_(SQL)

Let me know if something is unclear.
what you want to do shouldn't be hard, and it's a great starting practice for SQL.

Answer 2

SELECT nid, GROUP_CONCAT(string SEPARATOR ', ') FROM table GROUP BY nid, bidang;

To know more about take a look here:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

How to use GROUP BY to concatenate strings in MySQL?

Answer 3

In MSSQL I'd be looking at FOR XML_PATH type construct as one option. For MySQL use GROUP_CONCAT.

Sample would be:

select
NIP, 
BIDANG, 
GROUP_CONCAT(DISTINCT Mayor) Mayor, 
GROUP_CONCAT(DISTINCT Minor) Minor
FROM NIPTableName
group by
NIP, 
BIDANG