CODEWITHSUNDEEP
sqlsql-server
lookslikeanevo
lookslikeanevo

Reputation: 575

SQL Server 2008 - pivoting data

Need help pivoting some data

SELECT 
   [contact_list_name] as 'Table'
   ,[ov_number_dialed] as 'Number Dialed'
   ,convert(date, [time_of_contact], 101) as 'Date'
   ,convert(time, dateadd(hour, -7,[time_of_contact]), 108) as 'Time'
   ,agent_full_name as 'Agent'
   ,[response_status] as 'Disp'
FROM 
   [meldb].[dbo].[cl_contact_event]

which gives me results like this:

enter image description here

I want to pivot out or show the results so that the disp is in a line like

enter image description here

Any thoughts?

Upvotes: 0

Views: 45

Answers (1)

sgeddes
sgeddes

Reputation: 62861

Given your comments, an easy way to pivot is to use max with case:

select 
    [contact_list_name] as 'Table'
    , [ov_number_dialed] as 'Number Dialed'
    , convert(date, [time_of_contact], 101) as 'Date'
    , convert(time, dateadd(hour, -7,[time_of_contact]), 108) as 'Time'
    , agent_full_name as 'Agent'
    , max(case when [response_status] = 'DAM' then response_status end) as 'Disp1_DAM'
    , max(case when [response_status] = 'DNA' then response_status end) as 'Disp2_DNA'
    ...
FROM [meldb].[dbo].[cl_contact_event]
GROUP BY
    [contact_list_name] 
    , [ov_number_dialed] 
    , convert(date, [time_of_contact], 101) 
    , convert(time, dateadd(hour, -7,[time_of_contact]), 108) 
    , agent_full_name

Upvotes: 1

Related Questions