On 3/14/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > "Jake Peavy" <[EMAIL PROTECTED]> wrote on 03/14/2006 01:52:28 PM: > > > > On 3/10/06, Jake Peavy <[EMAIL PROTECTED]> wrote: > > > > > > On 3/7/06, C.R.Vegelin <[EMAIL PROTECTED]> wrote: > > > > > > > Hi Ariel, > > > > > > > > Maybe this example helps you to create CSV output from MySQL. > > > > The first SELECT generates the headerline; the second the data. > > > > ( SELECT 'FieldA','FieldB','FieldC', ... ) > > > > UNION > > > > ( SELECT `FieldA`, `FieldB`, `FieldC`, ... > > > > INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' > > > > FIELDS TERMINATED BY ',' > > > > LINES TERMINATED BY '\r\n' > > > > FROM ... ... > > > > GROUP BY `FieldA`, `FieldB`, `FieldC`, ... > > > > ); > > > > > > > > Don't forget the braces ( and ). > > > > HTH, Cor > > > > > > > > > So, to all the SQL wizards out there... > > > > > > How would one perform this same operation, but using the > > > INFORMATION_SCHEMA virtual db to provide the column headings in the > first > > > row rather having to hand type them as shown above? > > > > > > Bonus marks for beginning the line with a #.... > > > > > > My attempt stalled as soon as I got one column of data in the result > set > > > with a row for each column of the target table. > > > > > > SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND > > > TABLE_NAME = 'testtable' > > > > > > Ideas? > > > > > > TIA, > > > -jp > > > > > > > > > > Nobody has any ideas at all? (sheds solitary tear) > > > > -jp > > Nope, no suggestions. > > You are mixing purposes (not to mention data types) when you add column > names to the output of INTO OUTFILE. Have you looked at using the CSV > storage engine or mysqldump as alternatives? >
Yes, but based on the docs it would appear that it doesn't produce column names either (presumably because the table definition is stored in the .frm): mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV; Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test; +------+------------+ | i | c | +------+------------+ | 1 | record one | | 2 | record two | +------+------------+ 2 rows in set (0.00 sec) If you examine the test.CSV file in the database directory after executing the preceding statements, its contents look like this: "1","record one" "2","record two" I didn't actually try this, however, because my version hasn't been built to include the CSV engine. I could, of course, rebuild, but it didn't look like it would do what I wanted anyway. I think this (INTO OUTFILE w/column names) would be used infrequently, but it would be handy to send a small subset of data to someone so they could pivot it anyway they like in Excel. Appreciate the info though, if anyone else has any ideas.... How would one transform a single column of rows into a single row of columns? Seems like part of a crosstab query... -jp
