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.
Table: Registers
ID Number Prod_ID
331 01 112233
332 02 112233
333 03 112233
334 04 112233
Table: Reads
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.
Expected result:
RegisterID1 RegisterID2 RegisterID3 RegisterID4 Count
331 332 2
Is there any easy way to do this? Thank you very much!
Best Answer
You are on 10g, so
PIVOT
is unavailable, but still have the good oldsum(case ...)
.