Sql to loop through table

[honkmaster]

Hi, I have been stuck on best way to achieve the following (I'm still learning php and Sql so for give me if this is obvious)

I have a table (See Screen Shot) which has the following headings placeID, place, job1, job2, job3, job4, job5 etc I'm trying to run query which will return as the the result screen shot attached.

I'm not sure if I should be doing this within PHP or SQL? What I have is correct for job 001but i need all the other jobs underneath as in screen shot.

SELECT Campaign.PlaceId, Campaign.Place, Campaign.`001`
FROM Campaign
WHERE Campaign.`001` >='1' 

Any help would be great, Cheers Chris

[Barand]

The first part of the solution is normalize your data. Those "job" columns should be in a separate table, one job per row  plus place id

+--------+---------------+
|placeid | area          |
+--------+---------------+
| 310    | Abbotsinch    |
| 9002   | Ballymena     |
+--------+---------------+
    |
    +------------+
                 |
            +---------+-------+---------+
            | placeid |  job  |   qty   |
            +---------+-------+---------+
            |  310    |   001 |      1  |
            |  310    |   003 |      1  |
            |  9002   |   002 |      1  |
            |  9002   |   003 |      1  |
            +---------+-------+---------+

Then the query would be

SELECT c.placeid
     , area
     , job
     , qty
FROM campaign as c
     JOIN job as j USING (placeid)
ORDER BY job, area

[honkmaster]

Hi Thank you very much for your response, which is a great solution. The issue we have is the data comes in as displayed. What we are trying to do is not have to manipulate the data as this is the bit that takes time. So the benefit is only there is sql or php can produce the result as displayed in the result jpg. Cheers Chris

[Barand]

Then you need 10 queries with UNIONS

SELECT Campaign.PlaceId
     , Campaign.Place
     , '001' as job
     , Campaign.`001`
FROM Campaign
WHERE Campaign.`001` >='1'
UNION
SELECT Campaign.PlaceId
     , Campaign.Place
     , '002' as job
     , Campaign.`002`
FROM Campaign
WHERE Campaign.`002` >='1'
UNION

...

UNION
SELECT Campaign.PlaceId
     , Campaign.Place
     , '010' as job
     , Campaign.`010`
FROM Campaign
WHERE Campaign.`010` >='1'

ORDER BY job, Place;

 

[benanamen]

Quote

What we are trying to do is not have to manipulate the data as this is the bit that takes time.

The process can easily be automated. Allow me to introduce you to ETL, Extract, Transform, Load. There are many software's both free and paid for this purpose.

https://en.wikipedia.org/wiki/Extract,_transform,_load