Post reply

Pivot Split Column

  • March 17, 2022 at 8:38 am

    #4001132

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

    From

    split

    To

    split2

     

    /****** 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]

  • March 17, 2022 at 9:02 am

    #4001149

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply