Getting a MySQL query to return a specific format

[Lews_Therin]

This is my current query:
SELECT FirstVisit, COUNT(*), Prov1 FROM pat_dat GROUP BY FirstVisit, Prov1 ORDER BY FirstVisit;

Which produces output like this:

+------------+----------+-------+
| FirstVisit | COUNT(*) | Prov1 |
+------------+----------+-------+
| 2005-12-06 |        1 |  AAAA |
| 2005-12-06 |        1 |  BBBB |
| 2005-12-07 |        1 |  BBBB |
| 2005-12-08 |        5 |  AAAA |
| 2005-12-08 |        1 |  CCCC |
| 2005-12-08 |        3 |  BBBB |
| 2005-12-09 |        1 |  AAAA |
| 2005-12-09 |        1 |  CCCC |
| 2005-12-09 |        1 |  BBBB |
| 2005-12-10 |        3 |  CCCC |
+------------+----------+-------+

I'd like to be able to format the output like this, for easier graphing in Excel:

+------------+------+------+------+
| FirstVisit | AAAA | BBBB | CCCC |
+------------+------+------+------+
| 2005-12-06 |    1 |    1 |    0 |
| 2005-12-07 |    0 |    1 |    0 |
| 2005-12-08 |    5 |    3 |    1 |
| 2005-12-09 |    1 |    1 |    1 |
| 2005-12-10 |    0 |    0 |    3 |
+------------+------+------+------+

What sort of things would allow me to do that?

 

[Lews_Therin]

I've gotten closer - this is the query now:

SELECT FirstVisit,

COUNT(IF(Provider="AAAA",1,NULL)) AS "AAAA",
COUNT(IF(Provider="BBBB",1,NULL)) AS "BBBB",
COUNT(IF(Provider="CCCC",1,NULL)) AS "CCCC"

FROM proc_log GROUP BY FirstVisit ORDER BY FirstVisit;

It gives the results I'm looking for, but will have to be modified whenever a provider is added. How can I plug the results of "SELECT DISTINCT Provider" into my query, so that it can handle more providers later?

 

[mikeblas]

This is called pivoting. (Or, unpivoting, more accurately. I think.)

Unlike SQL Server 2005, MySQL doesn't support PIVOT and UNPIVOT operators (as far as I can tell). There's no real way to do the query in SQL directly, since you've got to make the metadata dynamic and that's between not possible and not easy, depending on your implementation.

If you're landing the data into Excel, you might find it easiest to do the pivot in Excel too. John Lacher has a tutorial on pivot tables in Excel.

 

[jizzypop]

it might be easier just to write a simple php script to out put the results you're looking for.

 

[Lews_Therin]

it might be easier just to write a simple php script to out put the results you're looking for.

I'm starting to think that'll be needed. A separate query to find the providers, construct a new query using that, then run the second query.