Convert different dates in date column to rows in SQL Server

Question

I want to display different dates as columns from LOGDATE column.

Here are the details

My query:

select
    logdate,
    service_type, 
    service_name, 
    sum(entry_fee + repair_fee) as fees
from 
    myTable
where 
    logdate >= '2019-11-01'
    and logdate <= '2019-11-03'
group by
    logdate,
    service_type, 
    service_name

This query returns this result:

LOGDATE     SERVICE_TYPE  SERVICE_NAME  FEES
---------------------------------------------
2019-11-01  Engine        Car           150
2019-11-01  Window        Truck         200
2019-11-01  Trunk         Jeep          100
2019-11-02  Engine        Car            50
2019-11-02  Window        Truck          90
2019-11-02  Trunk         Jeep           70
2019-11-03  Engine        Car           300
2019-11-03  Window        Truck          80
2019-11-03  Trunk         Jeep           30

Expected output format:

SERVICE_TYPE  SERVICE_NAME  2019-11-01 2019-11-02  2019-11-03
--------------------------------------------------------------
Engine        Car           150        50          300
Window        Truck         200        90           80
Trunk         Jeep          100        70           30

I have multiple types of SERVICE_TYPE and SERVICE_NAME, not just the three sown in the sample.

How can I get my expected output? Any help would be appreciated.

marc_s · Accepted Answer · 2019-11-15 05:18:18Z

2

You can do conditional aggregation to pivot the data over a fixed list of logdates:

select
    service_type, 
    service_name,
    sum(case when logdate = '2019-11-01' then entry_fee + repair_fee end) [2019-11-01],
    sum(case when logdate = '2019-11-02' then entry_fee + repair_fee end) [2019-11-02],
    sum(case when logdate = '2019-11-03' then entry_fee + repair_fee end) [2019-11-03]
from 
    mytable 
where 
    logdate >= '2019-11-01' and logdate <= '2019-11-03'
group by 
    service_type, service_name

You can also use SQL Server's PIVOT feature:

select
    service_type, 
    service_name,
    [2019-11-01],
    [2019-11-02],
    [2019-11-03]
from 
    mytable
where 
    logdate >= '2019-11-01' and logdate <= '2019-11-03'
pivot 
    (sum(entry_fee + repair_fee)
     for logdate in ([2019-11-01], [2019-11-02], [2019-11-03])
    )

edited Nov 15, 2019 at 5:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 14, 2019 at 22:22

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

zealous Over a year ago

What if I have more then 15 different dates then I need to write 15 different sum() statements, is there a way to handle this ? Just curious to know.

GMB Over a year ago

@zealous: indeed, you need to repeat the sum() expressions, that's how conditional aggregation works. For something more generic, you would need dynamic SQL (build a sql query as a string, then execute it), but that's a whole different beast.

GMB Over a year ago

Welcome @zealous!

GMB Over a year ago

@zealous: yes you can. I updated my answer with a query for that.

Collectives™ on Stack Overflow

Convert different dates in date column to rows in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related