Working with Oracle DB.
Having an issue converting an set of columns to rows and getting a unique count for each row.
I'm working with a table that identifies a set of values (up to six) that are attached to a specific account number. I wanted to unpivot these six columns so that they are listed in there own row. Each row would have a count representing the positional value (i.e., Value 1 = 1, Value 3 = 3, etc...)
For example, I have a table like below. The Values(1-6) will always be unique per Account Number
AccountNumber|Value1|Value2|Value3|Value4|Value5|Value6
-------------------------------------------------------
1 |123 |1234 |12345 |12 |12345 |1234
2 |123 |1234 |12345 |12 |12345 |1234
3 |123 |1234 |12345 |12 |12345 |1234
And I want the order of precedence to be attached to the Value number.
AccountNumber|Order|Value
-------------------------
1 |1 |123
1 |2 |1234
1 |3 |12345
1 |4 |12
1 |5 |123456
1 |6 |1
2 |1 |123
2 |2 |1234
2 |3 |12345
2 |4 |12
2 |5 |123456
2 |6 |1
3 |1 |123
3 |2 |1234
3 |3 |12345
3 |4 |12
3 |5 |123456
3 |6 |1
I have the following,
SELECT
'1' AS FACILITYID,
HL.ACCOUNT_ID AS AccountNumber,
--UNPIVOT_ROW,
DECODE(UNPIVOT_ROW, 1, TDL.DX_ONE_ID,
2, TDL.DX_TWO_ID,
3, TDL.DX_THREE_ID,
4, TDL.DX_FOUR_ID,
5, TDL.DX_FIVE_ID,
6, TDL.DX_SIX_ID) AS Value,
FROM ACCOUNT_LIST HL
INNER JOIN TRANSACTIONS TRAN ON HL.ACCOUNT_ID = TRAN.ACCOUNT_ID,
(SELECT LEVEL AS UNPIVOT_ROW FROM DUAL CONNECT BY LEVEL <= 6)
I am able to unpivot (using DECODE(), UNPIVOT() was giving me issues) but I can't seem to wrap my head around associating a unique count.
This is probably easy but it is just slipping out of my head trying to think of an efficient way to handle the 20,000+ plus rows before I unpivot without to much overhead.
I tried returning the value UNPIVOT_ROW and using a second decode to output a number but it just kept spitting out the number '1'
Any help or suggestions would be greatly appreciated!
I think you're over-complicating this, and just need a basic unpivot operation:
select account_id as "AccountNumber", position as "Order", value as "Value"
from (
select 1 as facility_id, hl.account_id, tdl.dx_one_id, tdl.dx_two_id,
tdl.dx_three_id, tdl.dx_four_id, tdl.dx_five_id, tdl.dx_six_id
from account_list hl
inner join transactions tdl on hl.account_id = tdl.account_id
)
unpivot (value for position in (dx_one_id as 1, dx_two_id as 2, dx_three_id as 3,
dx_four_id as 4, dx_five_id as 5, dx_six_id as 6))
Demo with starting data to match your first example:
-- CTEs for sample data
with account_list (account_id) as (
select 1 from dual
union all select 2 from dual
union all select 3 from dual
),
transactions (account_id, dx_one_id, dx_two_id, dx_three_id, dx_four_id, dx_five_id, dx_six_id) as (
select 1, 123, 1234, 12345, 12, 12345, 1234 from dual
union all select 2, 123, 1234, 12345, 12, 12345, 1234 from dual
union all select 3, 123, 1234, 12345, 12, 12345, 1234 from dual
)
-- actual query
select account_id as "AccountNumber", position as "Order", value as "Value"
from (
select 1 as facility_id, hl.account_id, tdl.dx_one_id, tdl.dx_two_id,
tdl.dx_three_id, tdl.dx_four_id, tdl.dx_five_id, tdl.dx_six_id
from account_list hl
inner join transactions tdl on hl.account_id = tdl.account_id
)
unpivot (value for position in (dx_one_id as 1, dx_two_id as 2, dx_three_id as 3,
dx_four_id as 4, dx_five_id as 5, dx_six_id as 6))
order by facility_id, account_id, position;
which gets
AccountNumber Order Value
------------- ---------- ----------
1 1 123
1 2 1234
1 3 12345
1 4 12
1 5 12345
1 6 1234
2 1 123
2 2 1234
2 3 12345
2 4 12
2 5 12345
2 6 1234
3 1 123
3 2 1234
3 3 12345
3 4 12
3 5 12345
3 6 1234
18 rows selected.