I want to join two tables (they are called Registers and Reads). As a result I would like to obtain 4 columns, corresponding to Registers ID.
According to the next example, by joining the tables, I obtain 2 rows. There could be some cases that I may find/obtain 4 rows because they exist in Reads table.
ID Number Prod_ID 331 01 112233 332 02 112233 333 03 112233 334 04 112233
Read_Id Register_Id 011 331 012 332
I use this query to link join both tables:
SELECT rg.ID FROM Reads rd LEFT JOIN Registers rg on rd.Register_ID = rg.ID WHERE rg.Prod_ID = 112233;
My result is next one:
ID 331 332
What I really want is to obtain just one row, assigning the first result to the first column, second one to the second and so on. In addition, I would like to add an extra column that shows how many columns contain information.
RegisterID1 RegisterID2 RegisterID3 RegisterID4 Count 331 332 2
Is there any easy way to do this? Thank you very much!