Showing particular row data as column-CodePudding

My table is as follows:

Date	Code	Price	MA5	MA20
2022-01-01	APPLE	1000	1080	1090
2022-01-02	APPLE	1100	1084	1100
2022-01-03	APPLE	1200	1090	1100
2022-01-01	MICROSOFT	7	9	10
2022-01-02	MICROSOFT	7.5	8	9.5
2022-01-03	MICROSOFT	8	8.5	9
...	...	...	...	...
2022-01-01	NASDAQ	14400	15600	16700
2022-01-02	NASDAQ	14500	15200	16100
2022-01-03	NASDAQ	14600	15000	16000

I'm currently saving NASDAQ values and stock data on the same table using MariaDB.

However, I want to show NASDAQ's MA values as new column fields into rest of the field, as NASDAQ_MA5, NASDAQ_MA20.

My question is, how do I select nasdaq's MA5 and MA20 values and put it as the values according to the matching dates? My desired output is as follows:

Date	Code	Price	MA5	MA20	NASDAQ_MA5	NASDAQ_MA20
2022-01-01	APPLE	1000	1080	1090	15600	16700
2022-01-02	APPLE	1100	1084	1100	15200	16100
2022-01-03	APPLE	1200	1090	1100	15000	16000
2022-01-01	MICROSOFT	7	9	10	15600	16700
2022-01-02	MICROSOFT	7.5	8	9.5	15200	16100
2022-01-03	MICROSOFT	8	8.5	9	15000	16000

I've been trying the following:

        SELECT *, 
        (PARTITION BY DATE, case when (code='NASDAQ') then MA5 else NULL end) as 'NASDAQ_MA5',
        (PARTITION BY DATE, case when (code='NASDAQ') then MA20 else NULL end) as 'NASDAQ_MA20'
        FROM TABLE

Your help will be very appreciated.

CodePudding user response：

You need to join the two distinct sets of data. It probably makes sense to define a CTE to keep the definitions clear, then join with your main table - either an inner join if there's always a corresponding date or left join if there might not be.

This assumes there's only a single nasdaq code for each date, if that's not the case you can aggregate in the CTE as required.

with nasdaq as (
    select date, MA5 NASDAQ_MA5, MA20 NASDAQ_MA20
    from t
    where code = 'NASDAQ'
)
select t.*, n.NASDAQ_MA5, n.NASDAQ_MA20
from t
left join nasdaq n on n.date=t.date
where t.code != 'NASDAQ';

CodePudding user response：

Im sure theres a more efficient way of doing this, but heres a way to solve your issue using 2 subqueries:

SELECT t1.*, (SELECT t2.MA5
              FROM tableA t2
              WHERE t1.date = t2.date
              AND code = 'NASDAQ') as NASDAQ_MA5,
              (SELECT t2.MA20
              FROM tableA t2
              WHERE t1.date = t2.date
              AND code = 'NASDAQ') as NASDAQ_MA20
FROM tableA t1
WHERE code != 'NASDAQ'

Try it out here.

CodePudding user response：

You can do a self join on date column and separating the data by CODE = 'NASDAQ'. Here is a way of doing this:

select a.*, b.ma5 as NASDAQ_MA5, b.ma20 as NASDAQ_MA20 from table1 a
left outer join (select date, ma5,ma20 from table1 where code = 'NASDAQ') b
on a.date = b.date where a.code <> 'NASDAQ'