SQL subquery syntax assistance

Question

I want to get specific values from a query in one database in separate columns.

If I run the following query:

select  ticker 
        ,calendardate 
        ,marketcap
        ,dimension
from [Investment Data 1].dbo.sf1a
where 
        ticker = 'aapl' 
    and dimension in ('ary','mry')
    and calendardate >='2013-01-01'
order by dimension, calendardate;

I get the following result:

ticker  calendardate    marketcap   dimension
AAPL    2013-12-31  472272497794    ARY
AAPL    2014-12-31  616453338265    ARY
AAPL    2015-12-31  664969711644    ARY
AAPL    2013-12-31  438576934018    MRY
AAPL    2014-12-31  603277600250    MRY
AAPL    2015-12-31  654159234917    MRY

I want a query that will return the following result:

ticker  calendardate    "ARY Marketcap" "MRY Marketcap"
AAPL    2013-12-31      472272497794    438576934018    
AAPL    2014-12-31      616453338265    603277600250    
AAPL    2015-12-31      664969711644    654159234917    

How can I do this?

Thanks for your time. Wised.

Answer 1

Using a case expression allows you to perform conditional aggregates and this has the effect of a pivot from rows to columns

select  ticker 
        ,calendardate 
        ,max(case when dimension = 'ary' then marketcap end) as aryMarketcap
        ,max(case when dimension = 'mry' then marketcap end) as mryMarketcap
from [Investment Data 1].dbo.sf1a
where 
        ticker = 'aapl' 
    and dimension in ('ary','mry')
    and calendardate >='2013-01-01'
group by  ticker 
        ,calendardate 
order by dimension, calendardate;

alternative is to use a the pivot operator

select
      ticker, calendardate, [ary], [mry]
from (select 
            * 
       from table1) p
pivot (
    max(marketcap)
    for dimension IN ([ary],[mry])
    ) pvt

If you have many dimension that you want to pivot into column headings, you may need to use dynamic sql to achieve the final result.