How to make a single row in db have 2 rows in output depending on its column values?-CodePudding

Suppose I have an items table in the database that can have a maximum of 2 units, sample below:

item_name	unit_1	unit_2
pencil	pc	pc
ballpen	pc	box
notebook	pc	pc

If an item only has 1 unit, then unit_1 and unit_2 is the same.

Is there a way to output it like this:

item_name	unit
pencil	pc
ballpen	pc
ballpen	box
notebook	pc

So the ballpen who has 2 units, ended up having 2 rows. If an item has 1 unit, then it only has 1 row.

CodePudding user response：

Does this work for you?

WITH tmp_1 AS(
  SELECT item_name, unit_1 as _unit, count(1) AS unit
  GROUPBY item_name, unit_1
), 
WITH tmp_2 AS
  SELECT item_name, unit_2 as _unit, count(1) AS unit
  GROUPBY item_name, unit_2
),
WITH tmp_3 AS (
  SELECT item_name, unit
  FROM tmp_1
  UNION(
    SELECT item_name, unit
    FROM tmp_2
  )
)
SELECT DISTINCT *  FROM tmp_3

CodePudding user response：

You didn't state your DBMS, but the following is standard ANSI SQL (and tested in Postgres)

You can use a CROSS JOIN against a unique list of units:

select i.item_name, u.unit
from items i
  cross join lateral (
     values (i.unit_1) 
     union
     values (i.unit_2)
  ) as u(unit)

The UNION will in the derived table will return a single row if both units are the same and two rows if they are different. The CROSS JOIN then does the rest.

Another option is to use a UNION which will remove duplicate rows:

select item_name, unit_1 as unit
from items
union 
select item_name, unit_2
from items;

Online example