-1

Snippet of price table

stockid typeabbrev currabrev debtorno price branchcode startdate  enddate
AC100BL CL         USD                0.2200           2015-07-28 0000-00-00
AC100BL DE         USD                0.3500           2016-07-29 0000-00-00
AC100BL EX         USD                0.3500           2016-07-29 0000-00-00
AC100BL FL         USD                0.4000           2015-07-28 0000-00-00
AC100BL RT         USD                0.4700           2016-07-29 0000-00-00
AC100BL SP         USD                0.3100           2015-07-28 0000-00-00
AC100BL ST         USD                0.2400           2015-07-28 0000-00-00
AC100BL TR         USD                0.4200           2016-07-29 0000-00-00

So, I have experience with MySQL but this is beyond me.

what you see above is a snippet from my price table from my database.

Right now there is a stockid row for every different typeabbrev.

My question is: How can I make a new table where each row is a different stockid and each row has all the different typeabbrev as fields per stockid. So the new table would end up like this:

stockid CL DE EX
AC100BL 0.2200 0.3500 0.3500          
BC19BLF 0.1100 0.1300 0.1400           
CC10GBL 0.3900 0.4900 0.3500

Sorry if i didnt explain this well.

Basically I would like stockid to be the primary key and for the fields to be all the existing ones plus extra ones for the other typeabrrev.

Thank you in advance for any advice you could offer me.

aderry
  • 157
  • 3
  • 11
  • if i could make the stockid the primary key it would then be able to join it to my other tables. – aderry Jun 16 '17 at 13:13
  • See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jun 16 '17 at 13:18
  • @aderry Can you add the example output? – Darshan Mehta Jun 16 '17 at 13:28
  • I don't see why you would want to create another table. The table you are showing looks fine. Its primary key should be stockid + typeabbrev of course. So what is it you cannot do with this table that you could do with the other? It seems you have a good database design with a normalized table, but want a worse table design instead. – Thorsten Kettner Jun 16 '17 at 13:29
  • @DarshanMehta i have added sample output. thank you for the suggestion. – aderry Jun 16 '17 at 13:45
  • @ThorstenKettner I would like to use the new table to echo the stockid plus the different types of prices (typeabbrev) on a php website. and the code i have right now shows them in separate rows instead of on one. – aderry Jun 16 '17 at 13:49
  • Then use PHP to loop through the records. One doesn't use SQL for this. – Thorsten Kettner Jun 16 '17 at 13:54
  • This is called a pivot table and this question has been asked and answered here on SO several times. The linked duplicate topic describes both static and dynamic pivoting in MySQL. However, pls note that it may be more efficient to do such transformation within the application code, as opposed to sql. – Shadow Jun 16 '17 at 14:10
  • @Shadow thanks, just haven't heard of those before so didn't know what to search for. – aderry Jun 16 '17 at 16:46

1 Answers1

0

You can do it with pivot queries, e.g.:

SELECT s.stockid,
 (SELECT price FROM table WHERE stockid = s.stockid AND typeabbrev = 'CL') AS 'CL',
 (SELECT price FROM table WHERE stockid = s.stockid AND typeabbrev = 'DE') AS 'DE',
 (SELECT price FROM table WHERE stockid = s.stockid AND typeabbrev = 'FX') AS 'FX'
FROM table;
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102