Pivot Split Column

• yrstruly

  Hall of Fame

  Points: 3477

  More actions

  March 17, 2022 at 8:38 am

  #4001133

  Please assist. I would like to pivot a table, see ddl below. Also, split a column into five columns like this:

  From

  

  To

  

   

  /****** Script for SelectTopNRows command from SSMS  ******/
  SELECT TOP (1000) [Advertiser]
        ,[Campaign]
        ,[Feed_2_Unique_ID_]
        ,[Feed_2_Reporting_label]
        ,[Date]
        ,[Campaign ID]
        ,[DV360 Line Item ID]
        ,[DV360 Line Item]
        ,[Impressions_]
        ,[Clicks]
        ,[Click Rate]
        ,[Active View  Viewable Impressions]
        ,[Active View  Measurable Impressions]
        ,[Active View  Eligible Impressions]
        ,[Total Conversions]
        ,[Expansions]
        ,[Expansion Time]
        ,[Average Expansion Time]
        ,[Interaction Rate]
        ,[Average Interaction Time]
        ,[Total Interactions]
        ,[Rich Media Impressions]
        ,[changeJourney-j1-slide1  Counters]
        ,[changeJourney-j2-slide1  Counters]
        ,[changeJourney-j3-slide1  Counters]
        ,[closeText-j1-slide1  Counters]
        ,[closeText-j1-slide2  Counters]
        ,[closeText-j1-slide3  Counters]
        ,[closeText-j1-slide4  Counters]
        ,[closeText-j2-slide1  Counters]
        ,[closeText-j2-slide2  Counters]
        ,[closeText-j2-slide3  Counters]
        ,[closeText-j2-slide4  Counters]
        ,[closeText-j3-slide1  Counters]
        ,[closeText-j3-slide2  Counters]
        ,[closeText-j3-slide3  Counters]
        ,[expandText-j1-slide1  Counters]
        ,[expandText-j1-slide2  Counters]
        ,[expandText-j1-slide3  Counters]
        ,[expandText-j1-slide4  Counters]
        ,[expandText-j2-slide1  Counters]
        ,[expandText-j2-slide2  Counters]
        ,[expandText-j2-slide3  Counters]
        ,[expandText-j2-slide4  Counters]
        ,[expandText-j3-slide1  Counters]
        ,[expandText-j3-slide2  Counters]
        ,[expandText-j3-slide3  Counters]
        ,[HTML5_Manual_Close  Counters]
        ,[next-j1-slide1  Counters]
        ,[next-j1-slide2  Counters]
        ,[next-j1-slide3  Counters]
        ,[next-j1-slide4  Counters]
        ,[next-j2-slide1  Counters]
        ,[next-j2-slide2  Counters]
        ,[next-j2-slide3  Counters]
        ,[next-j2-slide4  Counters]
        ,[next-j3-slide1  Counters]
        ,[next-j3-slide2  Counters]
        ,[next-j3-slide3  Counters]
        ,[prev-j1-slide1  Counters]
        ,[prev-j1-slide2  Counters]
        ,[prev-j1-slide3  Counters]
        ,[prev-j1-slide4  Counters]
        ,[prev-j2-slide1  Counters]
        ,[prev-j2-slide2  Counters]
        ,[prev-j2-slide3  Counters]
        ,[prev-j2-slide4  Counters]
        ,[prev-j3-slide1  Counters]
        ,[prev-j3-slide2  Counters]
        ,[prev-j3-slide3  Counters]
        ,[readMore-j1-slide1  Counters]
        ,[readMore-j1-slide2  Counters]
        ,[readMore-j1-slide3  Counters]
        ,[readMore-j1-slide4  Counters]
        ,[readMore-j2-slide1  Counters]
        ,[readMore-j2-slide2  Counters]
        ,[readMore-j2-slide3  Counters]
        ,[readMore-j2-slide4  Counters]
        ,[readMore-j3-slide1  Counters]
        ,[readMore-j3-slide2  Counters]
        ,[readMore-j3-slide3  Counters]
        ,[replayVideo_j1_slide2  Counters]
        ,[replayVideo_j1_slide4  Counters]
        ,[replayVideo_j2_slide2  Counters]
        ,[watchVideo-j1-slide1  Counters]
        ,[watchVideo-j1-slide2  Counters]
        ,[watchVideo-j1-slide3  Counters]
        ,[watchVideo-j1-slide4  Counters]
        ,[watchVideo-j2-slide1  Counters]
        ,[watchVideo-j2-slide2  Counters]
        ,[watchVideo-j2-slide3  Counters]
        ,[watchVideo-j2-slide4  Counters]
        ,[watchVideo-j3-slide1  Counters]
        ,[watchVideo-j3-slide2  Counters]
        ,[watchVideo-j3-slide3  Counters]
        ,[Global cta  Exits]
        ,[HTML5_Expanded_Clickthrough  Exits]
        ,[logo  Exits]
        ,[urlOpen-j1-slide1  Exits]
        ,[urlOpen-j1-slide2  Exits]
        ,[urlOpen-j1-slide3  Exits]
        ,[urlOpen-j1-slide4  Exits]
        ,[urlOpen-j2-slide1  Exits]
        ,[urlOpen-j2-slide2  Exits]
        ,[urlOpen-j2-slide3  Exits]
        ,[urlOpen-j2-slide4  Exits]
        ,[urlOpen-j3-slide1  Exits]
        ,[urlOpen-j3-slide2  Exits]
        ,[urlOpen-j3-slide3  Exits]
        ,[Timer-ExpandAd  Average Time]
        ,[Timer-ExpandAd  Timers]
        ,[Timer-Journey-j1-slide1  Average Time]
        ,[Timer-Journey-j1-slide1  Timers]
        ,[Timer-Journey-j1-slide2  Average Time]
        ,[Timer-Journey-j1-slide2  Timers]
        ,[Timer-Journey-j1-slide3  Average Time]
        ,[Timer-Journey-j1-slide3  Timers]
        ,[Timer-Journey-j1-slide4  Average Time]
        ,[Timer-Journey-j1-slide4  Timers]
        ,[Timer-Journey-j2-slide1  Average Time]
        ,[Timer-Journey-j2-slide1  Timers]
        ,[Timer-Journey-j2-slide2  Average Time]
        ,[Timer-Journey-j2-slide2  Timers]
        ,[Timer-Journey-j2-slide3  Average Time]
        ,[Timer-Journey-j2-slide3  Timers]
        ,[Timer-Journey-j2-slide4  Average Time]
        ,[Timer-Journey-j2-slide4  Timers]
        ,[Timer-Journey-j3-slide1  Average Time]
        ,[Timer-Journey-j3-slide1  Timers]
        ,[Timer-Journey-j3-slide2  Average Time]
        ,[Timer-Journey-j3-slide2  Timers]
        ,[Timer-Journey-j3-slide3  Average Time]
        ,[Timer-Journey-j3-slide3  Timers]
        ,[Timer-Journey-j3-slide4  Average Time]
        ,[Timer-Journey-j3-slide4  Timers]
        ,[Timer-readMore-j1-slide1  Average Time]
        ,[Timer-readMore-j1-slide1  Timers]
        ,[Timer-readMore-j1-slide2  Average Time]
        ,[Timer-readMore-j1-slide2  Timers]
        ,[Timer-readMore-j1-slide3  Average Time]
        ,[Timer-readMore-j1-slide3  Timers]
        ,[Timer-readMore-j1-slide4  Average Time]
        ,[Timer-readMore-j1-slide4  Timers]
        ,[Timer-readMore-j2-slide1  Average Time]
        ,[Timer-readMore-j2-slide1  Timers]
        ,[Timer-readMore-j2-slide2  Average Time]
        ,[Timer-readMore-j2-slide2  Timers]
        ,[Timer-readMore-j2-slide3  Average Time]
        ,[Timer-readMore-j2-slide3  Timers]
        ,[Timer-readMore-j2-slide4  Average Time]
        ,[Timer-readMore-j2-slide4  Timers]
        ,[Timer-readMore-j3-slide1  Average Time]
        ,[Timer-readMore-j3-slide1  Timers]
        ,[Timer-readMore-j3-slide2  Average Time]
        ,[Timer-readMore-j3-slide2  Timers]
        ,[Timer-readMore-j3-slide3  Average Time]
        ,[Timer-readMore-j3-slide3  Timers]
        ,[Timer-watchVideo-j1-slide1  Average Time]
        ,[Timer-watchVideo-j1-slide1  Timers]
        ,[Timer-watchVideo-j1-slide2  Average Time]
        ,[Timer-watchVideo-j1-slide2  Timers]
        ,[Timer-watchVideo-j1-slide3  Average Time]
        ,[Timer-watchVideo-j1-slide3  Timers]
        ,[Timer-watchVideo-j1-slide4  Average Time]
        ,[Timer-watchVideo-j1-slide4  Timers]
        ,[Timer-watchVideo-j2-slide1  Average Time]
        ,[Timer-watchVideo-j2-slide1  Timers]
        ,[Timer-watchVideo-j2-slide2  Average Time]
        ,[Timer-watchVideo-j2-slide2  Timers]
        ,[Timer-watchVideo-j2-slide3  Average Time]
        ,[Timer-watchVideo-j2-slide3  Timers]
        ,[Timer-watchVideo-j2-slide4  Average Time]
        ,[Timer-watchVideo-j2-slide4  Timers]
        ,[Timer-watchVideo-j3-slide1  Average Time]
        ,[Timer-watchVideo-j3-slide1  Timers]
        ,[Timer-watchVideo-j3-slide2  Average Time]
        ,[Timer-watchVideo-j3-slide2  Timers]
        ,[Timer-watchVideo-j3-slide3  Average Time]
        ,[Timer-watchVideo-j3-slide3  Timers]
        ,[closeText-j3-slide4  Counters]
        ,[expandText-j3-slide4  Counters]
        ,[HTML5_Expanded_Clickthrough  Counters_]
        ,[HTML5_Expanded_Clickthrough  Counters]
        ,[HTML5_Expanded_Clickthrough  Counters%]
        ,[Feed_2_Unique_ID]
        ,[Impressions]
        ,[next-j3-slide4  Counters]
        ,[readMore-j3-slide4  Counters]
        ,[replayVideo_j1_slide1  Counters]
        ,[replayVideo_j1_slide3  Counters]
        ,[replayVideo_j2_slide1  Counters]
        ,[replayVideo_j2_slide3  Counters]
        ,[replayVideo_j2_slide4  Counters]
        ,[replayVideo_j3_slide1  Counters]
        ,[replayVideo_j3_slide2  Counters]
        ,[replayVideo_j3_slide3  Counters]
        ,[replayVideo_j3_slide4  Counters]
        ,[watchVideo-j3-slide4  Counters]
        ,[urlOpen-j3-slide4  Exits]
        ,[Timer-readMore-j3-slide4  Average Time]
        ,[Timer-readMore-j3-slide4  Timers]
        ,[Timer-watchVideo-j3-slide4  Average Time]
        ,[Timer-watchVideo-j3-slide4  Timers]
    FROM [TMI_Marketing_DataModel].[dbo].[CM_DATA]

• Thom A

  SSC Guru

  Points: 99137

  More actions

  March 17, 2022 at 9:03 am

  #4001150

  Duplicate post of https://www.sqlservercentral.com/forums/topic/pivot-split-column-2, which is a duplicate post of https://www.sqlservercentral.com/forums/topic/pivot-split-column.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk