Join Two tables and Column value as Column

Question

I have a two tables one is master and another one is working table.

i would need to Join two tables Item master and Fb, and Fb column values should be column header how to write a sql query for this

Itemmaster table:

 barcode      | design   | shade| size | sleeve | brd| dept      |
+----------+-----------+-------+------+--------+-------------+-----------+
    |          |         |       |      |        |    |           |
    | OD132829 | 87282   | 1     | 36   | F/S    | CE | FORMAL SH |
    | OD132830 | 87282   | 1     | 38   | F/S    | CE | FORMAL SH |
    | OD132832 | 87282   | 1     | 40   | F/S    | CE | FORMAL SH |
    | OD132833 | 87282   | 1     | 42   | F/S    | CE | FORMAL SH |
    | OD132834 | 87282   | 1     | 44   | F/S    | CE | FORMAL SH |
    | OD132837 | 87282   | 2     | 36   | H/S    | CE | FORMAL SH |
    | OD132838 | 87282   | 2     | 38   | H/S    | CE | FORMAL SH |
    | OD132840 | 87282   | 2     | 40   | H/S    | CE | FORMAL SH |
    | OD132841 | 87282   | 2     | 42   | H/S    | CE | FORMAL SH |
    | OD132842 | 87282   | 2     | 44   | H/S    | CE | FORMAL SH |
    | OD132843 | 87282   | 2     | 46   | H/S    | CE | FORMAL SH |

Fabric Table

Lot No             Job No        Barcode     design  shade qty
 LOT/001/17-18   JOB/001/17-18   OD132829    87282      1    3
 LOT/001/17-18   JOB/001/17-18   OD132830    87282      1   12
 LOT/001/17-18   JOB/001/17-18   OD132832    87282      1   13
 LOT/001/17-18   JOB/001/17-18   OD132833    87282      1    5
 LOT/001/17-18   JOB/001/17-18   OD132834    87282      1   11
 LOT/001/17-18   JOB/002/17-18   OD132837    87282      2   30
 LOT/001/17-18   JOB/002/17-18   OD132838    87282      2   34
 LOT/001/17-18   JOB/002/17-18   OD132840    87282      2   30
 LOT/001/17-18   JOB/002/17-18   OD132841    87282      2   13
 LOT/001/17-18   JOB/002/17-18   OD132842    87282      2    2
 LOT/001/17-18   JOB/002/17-18   OD132843    87282      2   11

I would need to be a output

Lot No             Job No        DEsign   shade   36  38  40  42  44  46 
LOT/001/17-18    JOB/001/17-18   87282     1       3  12  13   5  11
LOT/001/17-18    JOB/002/17-18   87282     2      30  34  30  13   2   11

Please do not use tags that do not apply to your question. I removed the database tags as it is unclear which one you are actually using. Please add the tag of *only* the database you are actually using — John Conde, May 29 '18 at 13:11
Personally i would handle that in your your presentation layer, not in your SQL. — Thom A, May 29 '18 at 13:12
https://stackoverflow.com/questions/7674786/mysql-pivot-table — Richard Hansell, May 29 '18 at 13:32

cdaiga · Answer 1 · 2018-05-30T07:44:41.773

0

You a join with window function.

SELECT 
    B.`Lot No`, B.`Job No`, B.design, B.shade, 
    SUM(IF(A.size=36,b.qty,0)) '36',
    SUM(IF(A.size=38,b.qty,0)) '38',
    SUM(IF(A.size=40,b.qty,0)) '40',
    SUM(IF(A.size=42,b.qty,0)) '42',
    SUM(IF(A.size=44,b.qty,0)) '44',
    SUM(IF(A.size=46,b.qty,0)) '46'
FROM Itemmaster A JOIN Fabric B
ON A.shade=B.shade AND A.design=B.design
GROUP BY B.`Lot No`, B.`Job No`, B.design, B.shade;

See a running DEMO on SQL Fiddle.

edited May 30 '18 at 07:44

answered May 29 '18 at 13:42

cdaiga

4,861
3
22
42

@Ayyanar I just updated the code and added a demo. There was a comma before FROM in the previous query. You just had to remove it. – cdaiga May 30 '18 at 07:45
cdaiga, Thanks for your help i have modified my code. – Ayyanar May 30 '18 at 07:51

Join Two tables and Column value as Column

1 Answers1