[RESOLVED] SQL/PHP Pivot tables

ee12csvt

Hello,

I have searched for any advice on creating a pivot table result from information in my database table using sql and php, but cannot find any solution that I

can understand!!

Firstly my table includes the following columns respresenting one piece of machinery.

TypeID, RangeID, LastNumber, OrigNumber, Renumber1, Renumber2, Renumber3, Renumber4, BuildID, WorksNumber, ServiceDate, Disposal and

Company.

There are 555 records in the table, TypeID is unique to each entry and is the primary key.

An example of data held is -

0001, 1000, 1000, 1000, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Scrapped, CompanyA
0002, 1800, 1802, 1802, NULL, NULL, NULL, NULL, RSHD, 1000/D001, 1960-12-01, Stored, CompanyC
0003, D200, D201, D201, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Available, CompanyB
0004, K001, K100, K100, NULL, NULL, NULL, NULL, EERQ, 1000/D001, 1960-12-01, Scrapped, CompanyA
0005, XZ01, 2010, 2010, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Scrapped, CompanyA
0006, 1800, 1803, 1803, NULL, NULL, NULL, NULL, RSHD, 1000/D001, 1960-12-01, Stored, CompanyC
0007, D200, D203, D203, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Scrapped, CompanyB
0008, XZ01, 2011, 2011, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Available, CompanyD
0009, XZ01, 2012, 2012, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Private, CompanyD
0010, XZ01, 2013, 2013, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Available, CompanyD
0011, 1000, 1010, 1010, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Available, CompanyA
0012, XZ01, 2100, 2100, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Scrapped, CompanyD
0013, 1000, 1011, 1011, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Unknown, CompanyA
0014, 1800, 1804, 1804, NULL, NULL, NULL, NULL, RSHD, 1000/D001, 1960-12-01, Scrapped, CompanyC
0015, 1000, 1012, 1012, NULL, NULL, NULL, NULL, EEVF, 1000/D001, 1960-12-01, Scrapped, CompanyA

The WorksNumber and Service dates shown are just an example.

What I want to do is created a table on the page of the site where the information is displayed that counts and displays the number of a certain 'RangeID' in terms of 'BuildID' and 'Disposal'.

Similar to this from the example above

| Class | Available | Stored | Scrapped | Private | Unknown | Total |

| 1000 | 1 | 0 | 2 | 0 | 1 | 4 |

| 1800 | 0 | 2 | 1 | 0 | 0 | 3 |

| D200 | 1 | 0 | 1 | 0 | 0 | 2 |

| K001 | 0 | 0 | 1 | 0 | 0 | 1 |

| XZ01 | 2 | 0 | 2 | 1 | 0 | 5 |

| Total | 4 | 2 | 7 | 1 | 1 | 15 |

The table above shows all records, but I would also like to create tables on other pages that would show only the details when BuildID is EEVF, RSHD or EERQ individually and another that would show EEVF and RSHD records merged together.

Any advice would be good

Many Thanks

Phil
www.12csv.com

__berfuzz

ee12csvt;10909686 wrote:
The table above shows all records, but I would also like to create tables on other pages that would show only the details when BuildID is EEVF, RSHD or EERQ individually and another that would show EEVF and RSHD records merged together.

Is it MySQL? Try your querys in myphpadmin, or something similar.

SELECT columns FROM your_table WHERE BuildID = EEVF
SELECT columns FROM your_table WHERE BuildID = RSHD
SELECT columns FROM your_table WHERE BuildID = EERQ
SELECT columns FROM your_table WHERE BuildID = EEVF OR BuildID = RSHD

ee12csvt

Thanks for the advice überfuzz

I have may not have explained myself correctly, but then again I may have posted my question in the wrong forum

__berfuzz

ee12csvt;10910244 wrote:
Thanks for the advice überfuzz

I have may not have explained myself correctly, but then again I may have posted my question in the wrong forum

Now I see that my post was somewhat sloppy. It should be ... WHERE BuildID = 'EEVF'.

ee12csvt

I have an update,

I have written the following code

SELECT OrigClassID,
SUM(if(Disposal = 'Service', Disposal, 0)) AS "Service",
SUM(if(Disposal = 'Stored', Disposal, 0)) AS "Stored",
SUM(if(Disposal = 'Scrapped', Disposal, 0)) AS "Scrapped",
SUM(if(Disposal = 'Preserved', Disposal, 0)) AS "Preserved",
SUM(if(Disposal = 'Unknown', Disposal, 0)) AS "Unknown",
COUNT (*)
FROM growl_locos
GROUP BY OrigClassID

It produces the result that I am looking for except for one thing, it doesn't count the individual listings

OrigClassID Service Stored Scrapped Preserved Unknown COUNT( * )

1000 0 0 0 0 0 65
1300 0 0 0 0 0 20
1320 0 0 0 0 0 25
1851 0 0 0 0 0 16
2350 0 0 0 0 0 12
2370 0 0 0 0 0 4
90 0 0 0 0 0 44
A 0 0 0 0 0 5
D34 0 0 0 0 0 1
DE3 0 0 0 0 0 16
EE3 0 0 0 0 0 309
K 0 0 0 0 0 10
R 0 0 0 0 0 5
RA 0 0 0 0 0 13
Z 0 0 0 0 0 4
ZA 0 0 0 0 0 6

Any advice/help would be good, many thanks