Pivot Split Column

Question

Post reply

Pivot Split Column

yrstruly

Hall of Fame

Points: 3475

March 17, 2022 at 8:38 am

#4001131

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]

Viewing 4 posts - 1 through 3 (of 3 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Assist with what? You've posted this in the SSIS forum, so are you looking to pivot the data using SSIS? Where did you get stuck? Posting the topic 3 times won't give us more understanding where you got to when all 3 posts omit that information.

Explain the logic here. Give us some information.

Thom~

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

ReluctantPhilosopher Valued Member Points: 70 More actions · Answer 2

Looks like an SQL question. Suggest you post in the appropriate forum, trim down your request, provide some sample input and output data that someone can just run to see the issue.

Thom A SSC Guru Points: 99130 More actions · Answer 3

ReluctantPhilosopher wrote:

Looks like an SQL question. Suggest you post in the appropriate forum, trim down your request, provide some sample input and output data that someone can just run to see the issue.

Just to confuse every one, they also posted it a 4th time in the 2017 forum when they posted it 3 times in here too. /sigh

Thom~

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