[SELECT] question

Hi all,
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??

Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;

Sample data
+-------+--+---+
| date | code | value |
+-------+--+---+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+-------+--+---+

Recordset
+-------+----+----+
| date | value_cod1 | value_cod2 |
+-------+----+----+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|..................................... ..............|
+-------+----+----+

Thank you very much!
Paolo SaudinTo select all of the columns in an arbitrary order, you can use:SELECT *
FROM testTo select the columns in a specific order, you can use:SELECT date, code, value
FROM test-PatP|||ah, the good old denormalization question

mysql doesn't support full outer join, but this is an equivalent --

select t1.date
, t1.value as value_cod1
, t2.value as value_cod2
from `test` as t1
left outer
join `test` as t2
on t1.date
= t2.date
and t2.code = '002'
where t1.code = '001'

union all

select t2.date
, null
, t2.value
from `test` as t1
right outer
join `test` as t2
on t1.date
= t2.date
and t1.code = '001'
where t2.code = '002'
and t1.date is null

you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries|||ah, the good old denormalization questionI wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:SELECT date
, Sum(CASE WHEN '001' = code THEN value END) AS '001'
, Sum(CASE WHEN '002' = code THEN value END) AS '002'
, Sum(CASE WHEN '003' = code THEN value END) AS '003'
FROM test
GROUP BY dateThis only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.

-PatP|||i babble often

i'm wrong occasionally

:cool:|||Thank you all very much !! :)
Paolo Saudin

[2005] How do I script out all indexes?

Hello,
we are using Sql Server 2005 Managament Studio.
Can someone tell me how I can script out all DROP/CREATE INDEXES for a particular database?
We only want to script out the INDEXES, not the tables.
Please advise.
Thanks.
`LeYou can try using a (free) tool I wrote, called scriptdb.exe.

It scripts out all objects (including indexes). The script for each object is created in a separate file. It would be a simple matter to alter the tool so it only scripts the indexes (the source is available so you could do it yourself). Or you can just run it as is and script out all the objects, and throw away what you don't want if all you care about are indexes.

You can get it here: http://www.elsasoft.org/tools.htm

Alternatively, in SSMS, you could try using the Generate Scripts Wizard for this, but it won't do both drop and create at the same time. only drop OR create. not both.

Hope this helps!|||Thanks for the offer, but my company has a policy against 3rd party executables.

As for Sql Server Management Studio, I cannot seem to find anyplace to script out ONLY the indexes.

Are there other suggestions?

`Le|||you appear to be right, GSW won't script the index separate from the table.

even if you have a policy against 3rd party executables, you can still use scriptdb since I posted the source code.

Just build it yourself using VS or csc.exe and then it's *your* executable ;)