Is there way to split Data In One Field of Table into Two Fields(Columns) for Printing in MySQL

SenKMathew
April 9, 2023
145 views
0 votes
2 Answers

To make things more clear, let us first create a sample table

CREATE TABLE table_test(
ID INT,
Item VARCHAR(100),
Amount DOUBLE,
Item_Mode VARCHAR(10)
);

Now we can enter some sample data

INSERT  INTO `table_test`(`ID`,`Item`,`Amount`,`Item_Mode`) VALUES
(1,'Allowances',100.00,'a'),
(2,'HRA',200.00,'a'),
(3,'DA',100.00,'a'),
(4,'FBP',100.00,'a'),
(5,'Income Tax',50.00,'d'),
(6,'Insurance ',10.00,'d'),
(7,'Provident Fund',20.00,'d');

We have two types of data here. Mode a and Mode d.

ID	Item	Amount	Item_Mode
1	Allowance	100.00	a
2	HRA	200.00	a
3	DA	100.00	a
4	FBP	100.00	a
5	Income Tax	50.00	d
6	Insurance	10.00	d
7	Provident Fund	20.00	d

I want to show it all the ‘a’ data in one column and all the ‘d’ data in next column. How can I achieve this.

I tried

SELECT A.Item AS EARN,B.Item AS DEDUCT
FROM Table A INNER JOIN TABLE B
ON A.ID = B.ID  WHERE MOD(A.ID,2)=1 AND A.Mode = 'a' OR B.Mode = 'd'

The above query pumped out a wrong answer.

The expected dataset must display data like below

IDA	ItemA	AmountA	Item_ModeA	IDB	ItemB	AmountB	Item_ModeB
1	Allowance	100.00	a	5	Income Tax	50.00	d
2	HRA	200.00	a	6	Insurance	10.00	d
3	DA	100.00	a	7	Provident Fund	20.00	d
4	FBP	100.00	a

Tags: mysql

Answers

You can use the next SELECT statement:

WITH group_a AS (
  SELECT id idA, item ItemA, amount AmountA, Item_Mode Item_ModeA,
         ROW_NUMBER() OVER (ORDER BY id) AS row_num_A
  FROM Table
  WHERE Item_Mode = 'a'
), group_b AS (
  SELECT id idB, item ItemB, amount AmountB, Item_Mode Item_ModeB,
         ROW_NUMBER() OVER (ORDER BY id) AS row_num_B
  FROM Table
  WHERE Item_Mode = 'd'
)
SELECT a.idA, a.itemA, a.AmountA, a.Item_ModeA, 
       a.idB, a.itemB, a.AmountB, a.Item_ModeB 
FROM group_a AS a
    LEFT JOIN group_b AS b ON a.row_num_A = b.row_num_B
ORDER BY a.row_num_A;

Note: if count of rows in the group_b is bigger, then you can lose the data due to LEFT JOIN.

Update

If counts of these two parts are different time-to-time, then you can change the last two lines:

    FULL OUTER JOIN group_b AS b ON a.row_num_A = b.row_num_B
ORDER BY COALESCE(a.row_num_A, b.row_num_B);

Update
For the MySQL 5.5

SELECT a.id idA, a.item ItemA, a.amount AmountA, a.Item_Mode Item_ModeA,
       b.id idB, b.item ItemB, b.amount AmountB, b.Item_Mode Item_ModeB
FROM (
      SELECT id, item, amount, Item_Mode,
           (SELECT COUNT() FROM Table t2 WHERE t2.Item_Mode = 'a' AND t2.id <= t1.id) AS row_num_A
      FROM Table t1
      WHERE Item_Mode = 'a'
) AS a
LEFT JOIN (
      SELECT id, item, amount, Item_Mode,
           (SELECT COUNT() FROM Table t2 WHERE t2.Item_Mode = 'd' AND t2.id <= t1.id) AS row_num_B
      FROM Table t1
      WHERE Item_Mode = 'd'
) AS b ON a.row_num_A = b.row_num_B

UNION

SELECT a.id idA, a.item ItemA, a.amount AmountA, a.Item_Mode Item_ModeA,
       b.id idB, b.item ItemB, b.amount AmountB, b.Item_Mode Item_ModeB
FROM (
      SELECT id, item, amount, Item_Mode,
           (SELECT COUNT() FROM Table t2 WHERE t2.Item_Mode = 'a' AND t2.id <= t1.id) AS row_num_A
      FROM Table t1
      WHERE Item_Mode = 'a'
) AS a
RIGHT JOIN (
      SELECT id, item, amount, Item_Mode,
           (SELECT COUNT() FROM Table t2 WHERE t2.Item_Mode = 'd' AND t2.id <= t1.id) AS row_num_B
      FROM Table t1
      WHERE Item_Mode = 'd'
) AS b ON a.row_num_A = b.row_num_B
WHERE a.id IS NULL
ORDER BY COALESCE(a.row_num_A, b.row_num_B);

Here:

LEFT JOIN and RIGHT JOIN instead of FULL OUTER JOIN
UNION to combine the results of the left and right joins
subqueries to compute the row numbers instead of the OVER() clause

Assuming the number of items a always 4 and higher than number of items d, then you can do it using self left join with a condition (a.ID mod 4) = (b.ID mod 4)

If we take this query :

select *, ID mod 4
from table_test

it will Result :

ID  Item          Amount    Item_Mode   ID mod 4
1   Allowances    100       a           1
2   HRA           200       a           2
3   DA            100       a           3
4   FBP           100       a           0
5   Income Tax    50        d           1
6   Insurance     10        d           2
7   Provident Fund20        d           3

As you can see the first record with item a can be matched with first item d because both having ID mod 4 = 1

So the query can be :

select a.ID as idA, a.Item as itemA, a.Amount as amountA, a.Item_Mode as item_modeA,
  b.ID as idB,  b.Item as itemB, b.Amount as amoutB,    b.Item_Mode as item_modeB
from table_test a
left join table_test b 
      on (a.ID mod 4) = (b.ID mod 4) 
      and a.Item_Mode <> b.Item_Mode
where a.Item_Mode = 'a'

Result :

idA	itemA	amountA	item_modeA	idB	itemB	amoutB	item_modeB
1	Allowances	100	a	5	Income Tax	50	d
2	HRA	200	a	6	Insurance	10	d
3	DA	100	a	7	Provident	Fund	20
4	FBP	100	a	null	null	null	null

Demo here

If the number of rows of items a and d can be any number then :

with cte as (
  select *, count(ID) over (partition by Item_Mode) as count_items,
            row_number() over (partition by Item_Mode) as rn,
            row_number() over() - row_number() over (partition by Item_Mode) as grp
  from table_test
),
cte2 as (
  select *, case when max(count_items) over() = count_items then 1 else 0 end as havingMoreRows
  from cte c
),
item_mode_with_more_rows as (
  select *
  from cte2
  where havingMoreRows = 1
),
item_mode_with_less_rows as (
  select *
  from cte2
  where havingMoreRows = 0
)
select a.ID as idA, a.Item as itemA, a.Amount as amountA, a.Item_Mode as item_modeA,
  b.ID as idB,  b.Item as itemB, b.Amount as amoutB, b.Item_Mode as item_modeB
from item_mode_with_more_rows a
left join item_mode_with_less_rows b 
     on a.rn = b.rn 
     and a.Item_Mode <> b.Item_Mode

First cte used to group items by mode.

Second cte will find the item with the highest number of rows(in the simple data it is a with 4 items).

item_mode_with_more_rows to get set of records from the biggest mode.

item_mode_with_less_rows to get set of records from the smallest mode.

Then using a left join we get the expected output

Demo here

Please signup or login to give your own answer.

Click here to cancel reply.