SQL, need to show value rows wise

user19955452 0
Viewed 47

enter image description here

I have value in this format in database, I need to show these value rows wise, How can I do it?

I need to write down sql query for the same to show all the values row wise using sql query only.

S.No State Name
1 Texas
2 Calofornia
mysql database sql
1 Answers

Because the real table and column names are unclear, I'll give a more abstract answer.

There's some column containing values which are separated by a comma (or any other separator), thus forming a list of values. Having list semantics, the contents of the columns can be defined recursively as a list is

  • either empty,
  • consists of a single value
  • consists of a single value that we call the head and a list that we call the tail while head and tail being separated by a comma (separator).

Because of this recursive definition, we can use a recursive CTE to split the string into head and tail using the functions locate() and substring().

WITH RECURSIVE list(head, tail) AS (
    SELECT CASE WHEN locate(',', coalesce(txt, '')) > 0
                THEN substring(txt, 1, locate(',', txt) - 1)
                ELSE txt
           END,                                              -- head
           CASE WHEN locate(',', coalesce(txt, '')) > 0
                THEN substring(txt, locate(',', txt) + 1)
                ELSE NULL
           END                                               -- tail
      FROM (SELECT 'a,b,c,d,e' AS txt  -- use
            UNION ALL                  -- your
            SELECT 'm,n,o') some_data  -- table instead
     WHERE coalesce(txt, '') <> ''
    UNION
    SELECT CASE WHEN locate(',', tail) > 0
                THEN substring(tail, 1, locate(',', tail) - 1)
                ELSE tail
           END,
           CASE WHEN locate(',', tail) > 0
                THEN substring(tail, locate(',', tail) + 1)
                ELSE null
           END
      FROM list
     WHERE tail IS NOT NULL
)
SELECT * FROM list;

results in

head tail
a b,c,d,e
m n,o
b c,d,e
n o
c d,e
o NULL
d e
e NULL

We can now use this CTE as a subquery to get the desired result:

SELECT row_number() over () as pos, head FROM (
  WITH RECURSIVE list(head, tail) AS (
    SELECT CASE WHEN locate(',', coalesce(txt, '')) > 0
                THEN substring(txt, 1, locate(',', txt) - 1)
                ELSE txt
           END,                                              -- head
           CASE WHEN locate(',', coalesce(txt, '')) > 0
                THEN substring(txt, locate(',', txt) + 1)
                ELSE NULL
           END                                               -- tail
      FROM (SELECT 'a,b,c,d,e' AS txt  -- use
            UNION ALL                  -- your
            SELECT 'm,n,o') some_data  -- table instead
     WHERE coalesce(txt, '') <> ''
    UNION
    SELECT CASE WHEN locate(',', tail) > 0
                THEN substring(tail, 1, locate(',', tail) - 1)
                ELSE tail
           END,
           CASE WHEN locate(',', tail) > 0
                THEN substring(tail, locate(',', tail) + 1)
                ELSE null
           END
      FROM list
     WHERE tail IS NOT NULL
  )
  SELECT * FROM list) splitted;

results in

pos head
1 a
2 m
3 b
4 n
5 c
6 o
7 d
8 e

From that, it should be easy to adapt the query to your needs.

Mihe0
Related