Pivot

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #CALL_LOG_STAGING (
  ELID_KEY varchar(20) NOT NULL,
  CALL_DT datetime NOT NULL, 
  CALL_DISP_CD varchar(20),
  SUB_DISP_CD varchar(20)
)
;

INSERT INTO #CALL_LOG_STAGING
    (ELID_KEY, CALL_DT, CALL_DISP_CD, SUB_DISP_CD)
VALUES
    ('UXXXXX6', '2016-06-27', 'App in progress', NULL),
    ('UXXXXX6', '2016-06-27', 'App in progress', NULL),
    ('UXXXXX6', '2016-06-27', 'Shopping', NULL),
    ('UXXXXX6', '2018-02-05', 'App in progress', NULL),
    ('UXXXXX6', '2017-11-30', 'App in progress', NULL),
    ('UXXXXX6', '2017-07-13', 'App in progress', NULL),
    ('UXXXXX6', '2017-02-01', 'App in progress', NULL),
    ('UXXXXX7', '2018-01-15', 'Transfer', 'Customer Service')
;

select 
  K.ELID_KEY,
  K.CALL_DT,
  count(AIP.ELID_KEY) as 'App in progress',
  count(S.ELID_KEY) as 'Shopping',
  count(T.ELID_KEY) as 'Transfer'  
from #CALL_LOG_STAGING as K
left join #CALL_LOG_STAGING as AIP on K.ELID_KEY = AIP.ELID_KEY and AIP.CALL_DISP_CD = 'App in progress'
left join #CALL_LOG_STAGING as S on K.ELID_KEY = S.ELID_KEY and S.CALL_DISP_CD = 'Shopping'
left join #CALL_LOG_STAGING as T on K.ELID_KEY = T.ELID_KEY and T.CALL_DISP_CD = 'Transfer'
group by K.ELID_KEY, K.CALL_DT
;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results