CODEWITHSUNDEEP
sqlsql-server
Sid Kwakkel
Sid Kwakkel

Reputation: 799

SQL Server query to roll up data

I have the following SQL statement. It joins three tables: Person, Deliverable, and DeliverableActions

select 
    p.first_name, p. last_name, d.title, da.type 
from 
    Deliverable d
right join 
    Person p on d.person_responsible_id = p.id
right join 
    DeliverableAction da on da.DeliverableID = d.id
where 
    d.date_deadline >= @startDate and
    d.date_deadline <= @endDate
order by 
    d.title

The result is the following:

first_name | last_name   | title        | type
-----------+-------------+--------------+------
Joe        | Kewl        | My_Report_1  | 2
Joe        | Kewl        | My_Report_1  | 3
Joe        | Kewl        | My_Report_1  | 1
Sly        | Foxx        | Other_Rep_1  | 1
Sly        | Foxx        | Other_Rep_1  | 2

My goal result is to get the following table:

first_name | last_name  | title        | type_1 | type_2 | type_3 | type_4
-----------+------------+--------------+--------+--------+--------+---------
Joe        | Kewl       | My_report_1  | 1      | 1      | 1      | 0
Sly        | Foxx       | Other_Rep_1  | 1      | 1      | 0      | 0

Unfortunately I don't know what term to describe what I'm doing. I've searched 'grouping' and 'aggregation', but I'm left without an answer so I am putting it to the community. Thank you in advance for your help.

Upvotes: 0

Views: 372

Answers (3)

radar
radar

Reputation: 13425

you can use case based aggregation or you can also use pivot 

select p.first_name, 
       p. last_name, 
       d.title, 
       sum(case when da.type = 1 then 1 else 0 end) as type_1,
       sum(case when da.type = 2 then 1 else 0 end) as type_2,
       sum(case when da.type = 3 then 1 else 0 end) as type_3,
       sum(case when da.type = 4 then 1 else 0 end) as type_4,
    from Deliverable d
    right join Person p on d.person_responsible_id = p.id
    right join DeliverableAction da on da.DeliverableID = d.id
    where d.date_deadline >= @startDate and
          d.date_deadline <= @endDate
    group by p.first_name, p.last_name, d.title

Upvotes: 1

Vishal Seth
Vishal Seth

Reputation: 5048

You're looking for PIVOT

If you're using SQL Server 2008+, it has pivot function as described at http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

Basically, you write something like (sorry, I just pasted example from the quoted link but that should give you some idea):

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Upvotes: 0

Zee
Zee

Reputation: 840

select
first_name, last_name, title,
sum(case when type = 1 then 1 else 0 end) as type_1
from 
(
select p.first_name, p. last_name, d.title, da.type from Deliverable d
right join Person p on d.person_responsible_id = p.id
right join DeliverableAction da on da.DeliverableID = d.id
where d.date_deadline >= @startDate and
      d.date_deadline <= @endDate
 ) as a
 group by first_name, last_name, title

Upvotes: 0

Related Questions