Retrieve any one maximum value from column containing array separated by semicolon in sql

Question

I have columns like

periodbal                  balancetype
-------------              -------------
0;15;11;-13;-16;20           ABS
22;25;-78;0;1                ABS
67;89;-36;83;90;55           ABS
…                            ACS
…

I want to retrieve

periodbal                  balancetype
---------                  -------------
20                            ABS
25                            ABS
90                            ABS

As per the Project Requirement we need to use unpivot.. But i'm not getting how to use.. I'm new to sql.. Kindly can any one help me??? — Mamatha .s, Dec 25 '14 at 11:21
have a look at http://stackoverflow.com/questions/19195280/connect-by-clause-in-regex-substr — Dmitry Grekov, Dec 25 '14 at 11:48

score 3 · Accepted Answer · edited Dec 25 '14 at 13:59

The idea here is:

for each value of balancetype, extract all the values from periodbal column, convert them in separate rows and then calculate maximum for each row's balancetype value.

i.e.

PERIODBAL   BALANCETYPE     ID
0           ABS             1
15          ABS             1
11          ABS             1
-13         ABS             1
-16         ABS             1
20          ABS             1

SPLIT FOR ROW#2

22        ABS          2
25        ABS          2
-78       ABS          2
0         ABS          2
1         ABS          2
......

FINALLY, FETCH MAX(PERIODBAL) and group the data by columns balancetype, ID

CREATE TABLE SYS.TEST
(
  PERIODBAL    VARCHAR2(50 BYTE),
  BALANCETYPE  VARCHAR2(5 BYTE)
)

INSERT INTO TEST VALUES ('0;15;11;-13;-16;20', 'ABS');
INSERT INTO TEST VALUES ('22;25;-78;0;1', 'ABS');
INSERT INTO TEST VALUES ('67;89;-36;83;90;55 ', 'ABS');
INSERT INTO TEST VALUES ('0;15;10;-13;-16;23', 'ACS');
INSERT INTO TEST VALUES ('0;14;11;-13;-16;25', 'ACS');

solution:

SELECT BALANCETYPE,MAX(BAL) AS PERIODBAL
FROM
(
SELECT SUB1.*, TRIM(REGEXP_SUBSTR( PERIODBAL, '[^;]+', 1, LVL)) BAL
FROM             (SELECT TEST.*, ROW_NUMBER() OVER (ORDER BY BALANCETYPE ) UNIQ_ID FROM TEST) SUB1 , 
                (SELECT LEVEL LVL FROM DUAL,(SELECT  MAX(LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+')))+1 AS MAX_BAL FROM TEST)  TEMP
                 CONNECT BY LEVEL<= TEMP.MAX_BAL ) SUB2
WHERE LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+'))+1>= SUB2.LVL
) GROUP BY BALANCETYPE, UNIQ_ID

output:

BALANCETYPE PERIODBAL
ACS         23
ABS         20
ABS         25
ABS         90
ACS         25

Hope it helped, Thanks.

score 0 · Answer 2 · edited May 23 '17 at 11:51

In MySQL, you could try along

SELECT balanceType, MAX(CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(t.periodBal, ';', n.n), ';', -1), SIGNED INTEGER)) maxValue
FROM SomeData t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.periodBal) - LENGTH(REPLACE(t.periodBal, ';', '')))
GROUP BY periodBal, balanceType
ORDER BY periodBal, maxValue;

See it in action: SQL Fiddle (with the data slightly adjusted in order to check some border cases).

This borrowed from SQL split comma separated row
But the oldest SO reference for this approach, I was able to find, is Creating a MySQL SET's from a string

Retrieve any one maximum value from column containing array separated by semicolon in sql

2 Answers2