select and show some rows as columns

Larry

unread,

Oct 23, 2012, 3:57:57 PM10/23/12

to

Hi,

I have a table like the following:

CREATE TABLE IF NOT EXISTS `OPT_FTSEMIB` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`SDATE` date NOT NULL,
`SPOT` float NOT NULL,
`EXPIRY` char(5) NOT NULL,
`TYPE` char(1) NOT NULL,
`STRIKE` int(11) NOT NULL,
`SETTLEMENT` int(11) NOT NULL,
PRIMARY KEY (`ID`)
)

Here is how data is inserted into this table:

INSERT INTO `OPT_FTSEMIB` (`ID`, `SDATE`, `SPOT`, `EXPIRY`, `TYPE`,
`STRIKE`, `SETTLEMENT`) VALUES
(1, '2012-08-20', 14971, 'DEC15', 'C', 10000, 4473),
(2, '2012-08-20', 14971, 'DEC15', 'C', 11000, 3893),
(3, '2012-08-20', 14971, 'DEC15', 'C', 12000, 3332),
(4, '2012-08-20', 14971, 'DEC15', 'C', 13000, 2858),

...

(912, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(913, '2012-08-21', 15330, 'DEC15', 'C', 11000, 21),
(914, '2012-08-21', 15330, 'DEC15', 'C', 12000, 30),
(915, '2012-08-21', 15330, 'DEC15', 'C', 13000, 46),

and so forth...

Now, I would like to do something different other then to: select * from
`OPT_FTSEMIB` where ecc...

I would like to have data shown like this (where TYPE + STRIKE is name of
column):

.....SDATE...SPOT...EXPIRY...C10000...C11000...C12000...C13000
2012-08-20...14971..DEC15....4473.....3893.....3332.....2858..
2012-08-21...15330..DEC15....13.......21.......30.......46....

Do you think this can actually be done?

So I would basically like to SELECT SDATE, SPOT, EXPIRY and those 4 columns
(which are rows actually) FROM table WHERE EXPIRY="DEC15" AND TYPE="C"

Thanks

Erick T. Barkhuis

unread,

Oct 24, 2012, 2:42:28 AM10/24/12

to

Larry:

>Hi,

Hi Larry,

[...]

>Here is how data is inserted into this table:
>
>INSERT INTO `OPT_FTSEMIB` (`ID`, `SDATE`, `SPOT`, `EXPIRY`, `TYPE`,
>`STRIKE`, `SETTLEMENT`) VALUES

> (1, '2012-08-20', 14971, 'DEC15', 'C',10000, 4473),

> (2, '2012-08-20', 14971, 'DEC15', 'C', 11000, 3893),
> (3, '2012-08-20', 14971, 'DEC15', 'C', 12000, 3332),
> (4, '2012-08-20', 14971, 'DEC15', 'C', 13000, 2858),
>...
> (912, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
> (913, '2012-08-21', 15330, 'DEC15', 'C', 11000, 21),
> (914, '2012-08-21', 15330, 'DEC15', 'C', 12000, 30),
> (915, '2012-08-21', 15330, 'DEC15', 'C', 13000, 46),

[...]

>I would like to have data shown like this (where TYPE + STRIKE is
>name of column):
>
>.....SDATE...SPOT...EXPIRY...C10000...C11000...C12000...C13000
>2012-08-20...14971..DEC15....4473.....3893.....3332.....2858..
>2012-08-21...15330..DEC15....13.......21.......30.......46....
>
>Do you think this can actually be done?

Yes. Can be done.
I think.
It depends a bit on what situations can occur, and whether or not you
want to prevent rows from occuring multiple times.

>
>So I would basically like to SELECT SDATE, SPOT, EXPIRY and those 4
>columns (which are rows actually) FROM table WHERE EXPIRY="DEC15" AND
>TYPE="C"

My questions:

a) Do you _always_ have groups of exactly four rows for each
EXPIRY=value AND TYPE=value?

b) What would you want to have returned if rows were present like:
(982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(983, '2012-08-21', 15330, 'DEC15', 'C', 10000, 14),
(984, '2012-08-21', 15330, 'DEC15', 'C', 10000, 15),
OR
(982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(983, '2012-08-21', 15331, 'DEC15', 'C', 11000, 13),
(984, '2012-08-21', 15332, 'DEC15', 'C', 12000, 13),
OR EVEN
(982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(983, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(984, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
?

Depending on your answers, a solution could go into the direction of a
query with several subqueries (one for each additional STRIKE field
value):

SELECT
t1.sdate,
t1.spot,
t1.expiry,
t1.settlement AS c1,
(SELECT t2.settlement AS s2 FROM `OPT_FTSEMIB` t2
WHERE t2.expiry='DEC15' AND t2.type='C'
AND t2.id>t1.id AND t2.sdate=t1.sdate
ORDER BY t2.id LIMIT 1) AS c2,
[...etc...two more times]
FROM `OPT_FTSEMIB` t1
WHERE t1.expiry='DEC15' AND t1.type='C'
ORDER BY <whatever order you like to have>

[Neither tested nor optimized. Just a first thought.
And be prepared to receive questions about the normalization of
your data model from several denizens here in the group]

--
Erick

Larry

unread,

Oct 24, 2012, 9:15:38 AM10/24/12

to

"Erick T. Barkhuis" ha scritto nel messaggio
news:aepguj...@mid.individual.net...

> [Neither tested nor optimized. Just a first thought.
> And be prepared to receive questions about the normalization of
> your data model from several denizens here in the group]

Thanks!

I'll have a try at that as soon, in the meanwhile here is the complete
database:
https://dl.dropbox.com/sh/iir6laj5i0ojroz/f86HwNNYnG/sql/OPT_FTSEMIBMIBO.sql.zip?dl=1

> b) What would you want to have returned if rows were present like:
> (982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
> (983, '2012-08-21', 15330, 'DEC15', 'C', 10000, 14),
> (984, '2012-08-21', 15330, 'DEC15', 'C', 10000, 15),

STRIKE(10000) can have only one value for that EXPIRY(DEC15) in that very
day

Larry

unread,

Oct 25, 2012, 4:59:26 PM10/25/12

to

"Erick T. Barkhuis" <erick....@ardane.c.o.m> ha scritto nel messaggio
news:aepguj...@mid.individual.net...

> Yes. Can be done.
> I think.
> It depends a bit on what situations can occur, and whether or not you
> want to prevent rows from occuring multiple times.

Maybe I should explain my request a little better, take a look a this:

SELECT SDATE, SPOT, SETTLEMENT AS C10000
FROM OPT_FTSEMIB
WHERE EXPIRY = "DEC15"
AND TYPE = "C"
AND STRIKE =10000

It shows something like this:

.......SDATE...SPOT...C10000
2012-08-20...14971...4473
2012-08-21...15330...4605

and so forth...

Now, here is the same query with a different STRIKE value:

SELECT SDATE, SPOT, SETTLEMENT AS C11000
FROM OPT_FTSEMIB
WHERE EXPIRY = "DEC15"
AND TYPE = "C"
AND STRIKE =11000

it shows the following:

.......SDATE...SPOT...C11000
2012-08-20...14971...3893
2012-08-21...15330...4019

Now, what I would like is something like a multiple select where I get shown
this as a result:

.......SDATE...SPOT...C10000...C11000
2012-08-20...14971...4473........3893
2012-08-21...15330...4605........4019

Is that possible?

Thanks

Erick T. Barkhuis

unread,

Oct 26, 2012, 4:26:30 AM10/26/12

to

Larry:

>
>"Erick T. Barkhuis" <erick....@ardane.c.o.m> ha scritto nel
>messaggio news:aepguj...@mid.individual.net...
>
>>Yes. Can be done.
>>I think.
>>It depends a bit on what situations can occur, and whether or not
>>you want to prevent rows from occuring multiple times.

>.......SDATE...SPOT...C10000...C11000
>2012-08-20...14971...4473........3893
>2012-08-21...15330...4605........4019
>
>Is that possible?

Yes, it's what I described previously.
Have you tried my example with the subqueries?

Larry

unread,

Oct 26, 2012, 4:29:18 AM10/26/12

to

"Erick T. Barkhuis" <erick....@ardane.c.o.m> ha scritto nel messaggio

news:aeuvpm...@mid.individual.net...

> Yes, it's what I described previously.
> Have you tried my example with the subqueries?

Yep, it does not work properly..

Erick T. Barkhuis

unread,

Oct 26, 2012, 4:49:44 AM10/26/12

to

Larry:

Try bringing your car to the workshop, claiming that "it doesn't drive
properly". How, would you guess, are the chances that the mechanic will
stare at you, waiting for further clues?

--
Erick

Larry

unread,

Oct 26, 2012, 4:59:29 AM10/26/12

to

"Erick T. Barkhuis" <erick....@ardane.c.o.m> ha scritto nel messaggio

news:aev158...@mid.individual.net...

> Try bringing your car to the workshop, claiming that "it doesn't drive
> properly". How, would you guess, are the chances that the mechanic will
> stare at you, waiting for further clues?

I think maybe putting each single result into an associative array could do
that. Something like:

SELECT SETTLEMENT FROM OPT_FTSEMIB WHERE STRIKE=10000 AND TYPE="C" AND
EXPIRY="DEC15"
SELECT SETTLEMENT FROM OPT_FTSEMIB WHERE STRIKE=11000 AND TYPE="C" AND
EXPIRY="DEC15"
SELECT SETTLEMENT FROM OPT_FTSEMIB WHERE STRIKE=12000 AND TYPE="C" AND
EXPIRY="DEC15"
..ecc..(actually the range is from 10000 to 40000)

$array->{"2012-08-20"}->{SPOT}=SPOT
$array->{"2012-08-20"}->{C10000}=SETTLEMENT
$array->{"2012-08-20"}->{C11000}=SETTLEMENT
$array->{"2012-08-20"}->{C12000}=SETTLEMENT

$array->{"2012-08-21"}->{SPOT}=SPOT
$array->{"2012-08-21"}->{C10000}=SETTLEMENT
$array->{"2012-08-21"}->{C11000}=SETTLEMENT
$array->{"2012-08-21"}->{C12000}=SETTLEMENT

so that I can show like this:

SDATE....SPOT....C10000...C11000...C12000

Denis McMahon

unread,

Oct 27, 2012, 1:00:58 AM10/27/12

to

On Fri, 26 Oct 2012 10:59:29 +0200, Larry wrote:

> I think maybe putting each single result into an associative array could
> do that. Something like:

....

> $array->{"2012-08-20"}->{SPOT}=SPOT

Question - are you trying to achieve this solely in sql, or are you
calling the sql from another language, and if so, what language?

It seems to me that what you want to do is:

1) select distinct type, strike from the existing table
2) build a temporary table with sdate, spot, expiry, "type+strike"
columns for each distinct pairing of type and strike found
3) load the sdate, spot and expiry cols of the temporary table with the
distinct sdate, spot and expiry groupings from the original table
4) populate each of the "type+strike" columns of the temporary table,
using the relevant settlement values from the original table
5) pull your data from the temporary table

This is probably the point at which someone makes a comment about getting
your table design right to start with, if no-one's already said it!

Rgds

Denis McMahon