skip to Main Content

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

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search