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')
;

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @cols2 AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = 
  STUFF((SELECT distinct ',' + QUOTENAME(c.CALL_DISP_CD) 
         FROM #CALL_LOG_STAGING AS c
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols2 = 
  STUFF((SELECT distinct ',' + QUOTENAME(c.SUB_DISP_CD) 
         FROM #CALL_LOG_STAGING AS c
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 
  '
  SELECT ELID_KEY, ' + @cols + @cols2 +
  'from 
  (
    select ELID_KEY, CALL_DT, CALL_DISP_CD, SUB_DISP_CD
    from #CALL_LOG_STAGING
  ) x
  pivot 
  (
    Count(CALL_DISP_CD)
    for CALL_DISP_CD in (' + @cols + ')
  ) p1
  pivot 
  (
    Count(SUB_DISP_CD)
    for SUB_DISP_CD in (' + @cols2 + ')
  ) p2
  '


execute(@query)

Enter Parameters

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