How to transpose min/max value to columns in SQL Server using Partition?

Question

I want to get the MIN and MAX from a certain values and put them in columns beside each other. Here's my query but I don't know how to transpose the values...

SELECT *
    , MIN([CID]) OVER (PARTITION BY [TID] ORDER BY [TID]) MinID
    , MAX([CID]) OVER (PARTITION BY [TID] ORDER BY [TID]) MaxID

Given:

TID       CID DATE
123456789 1   01JAN
123456789 2   02JAN
123456789 3   03JAN
123456789 4   04JAN

Result:

TID       CID DATE   MIN MAX DATEMIN DATEMAX
123456789 1   01JAN  1   4   01JAN   04JAN

Answer 1

Isn't simple aggregation good enough here?

select 
    tid,
    min(cid) min_cid,
    max(cid) max_cid,
    min(date) min_date,
    max(date) max_date
from mytable
group by tid

Or, if the cids and dates are not changing accordingly, you can use conditional aggregation:

select
    tid,
    max(case when rn_asc  = 1 then cid end) cid_at_min_date,
    max(case when rn_desc = 1 then cid end) cid_at_max_date,
    min(date) min_date,
    max(date) max_date

from (
    select 
        t.*,
        row_number() over(partition by tid order by cdate asc ) rn_asc,
        row_number() over(partition by tid order by cdate desc) rn_desc
    from mytable t
) t
where 1 in (rn_asc, rn_desc)
group by tid

This orders records by cdate, and gives you the cids that correspond to the minimum and maximum date. You can easily adapt the query if you want things the other way around (basically, switch cid and cdate).