SQL organise a table into summarised view

问题:

I currently have a sql table that looks similar to this

 +---------+--------------+------------+
| Company | Contact Type | Start Time |
+---------+--------------+------------+
| x       | Call         | 01/01/2016 |
| x       | Call         | 02/01/2016 |
| x       | Meeting      | 02/01/2016 |
| x       | Email        | 03/01/2016 |
| y       | Meeting      | 01/01/2016 |
| y       | Email        | 01/02/2016 |
| y       | Call         | 02/02/2016 |
| z       | Call         | 09/01/2016 |
| z       | Call         | 24/01/2016 |
| z       | Meeting      | 10/01/2016 |
| z       | Meeting      | 06/01/2016 |
+---------+--------------+------------+

What I would like to produce is a table that shows the most recent contact type in the following format:

+---------+------------+------------+------------+
| Company |    Call    |  Meeting   |   Email    |
+---------+------------+------------+------------+
| x       | 02/01/2016 | 02/01/2016 | 03/01/2016 |
| y       | 02/02/2016 | 01/01/2016 | 01/02/2016 |
| z       | 24/01/2016 | 10/01/2016 | Null       |
+---------+------------+------------+------------+

Now i could probably make it look like this in SSRS and convert the dataset into a cross tab matrix report but I'd like to produce this in SQL. I have also managed to produce this by using a multitude of sub-queries but this doesn't seem like an efficient way of doing it and the real table in question has millions of rows and more contact types than call, meeting and email. So the question is, what is the most efficient way of producing the second table. For sake of argument lets say to extract table 1 the sql code is:

SELECT Company, [Contact Type], [Start Time] From Db.dbo.History

回答1:

You can also do like that: (Used a PIVOT)

-- Your Sample
WITH List AS
(
    SELECT 'x' AS Company, 'Call'    AS ContactType, '01/01/2016' AS StartTime UNION ALL
    SELECT 'x' AS Company, 'Call'    AS ContactType, '02/01/2016' AS StartTime UNION ALL
    SELECT 'x' AS Company, 'Meeting' AS ContactType, '02/01/2016' AS StartTime UNION ALL
    SELECT 'x' AS Company, 'Email'   AS ContactType, '03/01/2016' AS StartTime UNION ALL
    SELECT 'y' AS Company, 'Meeting' AS ContactType, '01/01/2016' AS StartTime UNION ALL
    SELECT 'y' AS Company, 'Email'   AS ContactType, '01/02/2016' AS StartTime UNION ALL
    SELECT 'y' AS Company, 'Call'    AS ContactType, '02/02/2016' AS StartTime UNION ALL
    SELECT 'z' AS Company, 'Call'    AS ContactType, '09/01/2016' AS StartTime UNION ALL
    SELECT 'z' AS Company, 'Call'    AS ContactType, '24/01/2016' AS StartTime UNION ALL
    SELECT 'z' AS Company, 'Meeting' AS ContactType, '10/01/2016' AS StartTime UNION ALL
    SELECT 'z' AS Company, 'Meeting' AS ContactType, '06/01/2016' AS StartTime 
)


SELECT Company, Call, Meeting, Email
FROM
(
  SELECT StartTime, ContactType, Company
    FROM List
) L
PIVOT
(
  MAX(StartTime)
  FOR ContactType IN (Call, Meeting, Email)
) CT

My result

回答2:

Do a GROUP BY. Use case expressions to do conditional aggregation:

SELECT Company,
       max(case when [Contact Type] = 'Call' then [Start Time] end) [Call],
       max(case when [Contact Type] = 'Meeting' then [Start Time] end) [Meeting],
       max(case when [Contact Type] = 'Email' then [Start Time] end) [Email]
From Db.dbo.History
group by Company

回答3:

You can use CASE here:

SELECT Company
    ,CASE [Contact Type] WHEN 'Call' THEN  MAX([Start Time]) END AS [Call]
    ,CASE [Contact Type] WHEN 'Meeting' THEN  MAX([Start Time]) END AS  [Meeting]
    ,CASE [Contact Type] WHEN 'Email' THEN  MAX([Start Time]) END AS    [Email]
From Db.dbo.History
GROUP BY Company