SQL join on columns of same table

Question

Have this table (top row is column names):

security    quote_type      price
sec_1       bid             3.4
sec_1       ask             3.6
sec_2       bid             5.2
sec_2       ask             5.4
sec_3       bid             2.3
sec_4       ask             7.8

Need a query to achieve these results:

security    bid     ask
sec_1       3.4     3.6
sec_2       5.2     5.4
sec_3       2.3     null
sec_4       null    7.8

Using SQL Server.

Thanks for any help!

For just one pair of bid, ask per security you can use conditional aggregation to get the expected result. You can find plenty of similar cases here in SO. — Giorgos Betsos
– Giorgos Betsos, Commented Dec 2, 2017 at 16:21

Gordon Linoff · Accepted Answer · 2017-12-02 16:34:35Z

2

You can also just use conditional aggregation or pivot:

select security,
       max(case when quote_type = 'bid' then price end) as bid,
       max(case when quote_type = 'ask' then price end) as ask
from t
group by security;

answered Dec 2, 2017 at 16:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2017-12-02 15:51:51Z

0

You could join between two queries on the table:

SELECT          a.security, bid, ask
FROM            (SELECT security, price AS bid
                 FROM   mytable
                 WHERE  quote_type = 'bid') a
FULL OUTER JOIN (SELECT security, price AS ask
                 FROM   mytable
                 WHERE  quote_type = 'ask') b ON a.security = b.security

answered Dec 2, 2017 at 15:51

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Collectives™ on Stack Overflow

SQL join on columns of same table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related