Search code examples
mysqlsqlunpivot

mysql - subqueries

I have this table

enter image description here

And I want to and can make it look like this

enter image description here

the query I used is this {

SELECT  Cat,Reg,Branch, Mnth, Qty
FROM
(
  SELECT Cat,Reg,Branch, 'Jan' Mnth, Jan Qty
  FROM BQV
  WHERE Jan > 0
  UNION ALL
  SELECT Cat,Reg,Branch, 'Feb', Feb
  FROM BQV

  UNION ALL
  SELECT Cat,Reg,Branch, 'Mar', Mar
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'Apr', Apr
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'May', May
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'Jun', Jun
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'Jul', Jul
  FROM BQV
  WHERE Feb > 0

  UNION ALL
  SELECT Cat,Reg,Branch, 'Aug', Aug
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'Sept', Sep
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'Oct', Octo
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'Nov', Nov
  FROM BQV

UNION ALL
  SELECT Cat,Reg,Branch, 'Dec', Dece
  FROM BQV

) QV

}

Can I achieve this via Views or a Stored procedure or anything ?? my MySQL ver is 5.5 and it can't have sub queries in a view and I cant upgrade my DB as I can't afford ANY loss or error whatsoever.

Solution

  • The following VIEW does what I needed perfectly.

    CREATE VIEW scheme-test.trybqv1 AS

    SELECT Cat,Reg,Branch, 'Jan' Mnth,Jan Qty FROM BQV A UNION ALL SELECT Cat,Reg,Branch, 'Feb', Feb FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Mar', Mar FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Apr', Apr FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'May', May FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Jun', Jun FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Jul', Jul FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Aug', Aug FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Sept', Sep FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Oct', Octo FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Nov', Nov FROM BQV

    UNION ALL SELECT Cat,Reg,Branch, 'Dec', Dece FROM BQV