Data reformatting question

• mmdmurphy

  Grasshopper

  Points: 21

  More actions

  December 22, 2009 at 8:26 am

  #136145

  How can I convert the following sample rows (from a table called Staging that gets reloaded every night. Yes, the double ampersand is the delimiter..). Please rule out changing the way the data is retrieved, I cannot get permission to change that...

  NAORABRABobCONNECT&&FF_SPEC&&PMSADMIN&&FF_STD_FULL&&APMENU_ADMIN_ROLE

  NAORAZAAWKSYSDBA&&CTXAPP&&CONNECT&&RESOURCE&&JAVASYSPRIV&&JAVAUSERPRIV&&xxx&yyy&zzz (the point being you don't know how many entries there might be)

  To this (in a new table, called Auditing)

  NAORABRABobCONNECT

  NAORABRABobFF_SPEC

  NAORABRABobPMSADMIN

  NAORABRABobFF_STD_FULL

  NAORABRABobAPMENU_ADMIN_ROLE

  NAORAZAAWKSYSDBA

  NAORAZAAWKSYSCTXAPP

  NAORAZAAWKSYSCONNECT

  NAORAZAAWKSYSRESOURCE

  NAORAZAAWKSYSJAVASYSPRIV

  NAORAZAAWKSYSJAVAUSERPRIV

  ???

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  December 22, 2009 at 9:43 am

  #1095480

  mmdmurphy (12/22/2009)

  ---

  How can I convert the following sample rows (from a table called Staging that gets reloaded every night. Yes, the double ampersand is the delimiter..). Please rule out changing the way the data is retrieved, I cannot get permission to change that...

  NAORABRABobCONNECT&&FF_SPEC&&PMSADMIN&&FF_STD_FULL&&APMENU_ADMIN_ROLE

  NAORAZAAWKSYSDBA&&CTXAPP&&CONNECT&&RESOURCE&&JAVASYSPRIV&&JAVAUSERPRIV&&xxx&yyy&zzz (the point being you don't know how many entries there might be)

  To this (in a new table, called Auditing)

  NAORABRABobCONNECT

  NAORABRABobFF_SPEC

  NAORABRABobPMSADMIN

  NAORABRABobFF_STD_FULL

  NAORABRABobAPMENU_ADMIN_ROLE

  NAORAZAAWKSYSDBA

  NAORAZAAWKSYSCTXAPP

  NAORAZAAWKSYSCONNECT

  NAORAZAAWKSYSRESOURCE

  NAORAZAAWKSYSJAVASYSPRIV

  NAORAZAAWKSYSJAVAUSERPRIV

  ???

  Please confirm that the delimiter between NAORABRA and Bob is a tab and that the delimiter between Bob and CONNECT is also a tab.

  Also, I couldn't give you an Analysis Services solution if my life depended on it... but this is a piece of proverbial cake in T-SQL especially if you have the 3 columns already loaded into a table (3 columns of original data... dept, name, and long delimited string).

  We also need to know where the data currently resides... in a table or it's still stuck in a file or in a table as one big string that includes all 3 "columns".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• GSquared

  SSC Guru

  Points: 260824

  More actions

  December 22, 2009 at 9:44 am

  #1095483

  Here's how I'd do it in T-SQL, based on the strings provided.

  if object_id(N'tempdb..#T') is not null

  drop table #T;

  create table #T (

  ID int identity primary key,

  Val1 varchar(max));

  insert into #T (Val1)

  select 'NAORABRA Bob CONNECT&&FF_SPEC&&PMSADMIN&&FF_STD_FULL&&APMENU_ADMIN_ROLE' union all

  select 'NAORAZAA WKSYS DBA&&CTXAPP&&CONNECT&&RESOURCE&&JAVASYSPRIV&&JAVAUSERPRIV&&xxx&yyy&zzz';

  ;with

  Split1 as

  (select

  left(Val1, charindex(' ', Val1, charindex(' ', Val1, 0)+1)) as Val1Prefix,

  replace(right(Val1, len(Val1)-charindex(' ', Val1, charindex(' ', Val1, 0)+1)), '&&', '|') as Val1Suffix

  from #T)

  select Val1Prefix, Val1Parsed

  from Split1

  cross apply

  (select substring(Val1Suffix + '|', Number, charindex('|', Val1Suffix + '|', Number) - Number) Val1Parsed

  from dbo.Numbers

  where Number <= len(Val1Suffix)

  and substring('|' + Val1Suffix, Number, 1) = '|' ) Parser;

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 22, 2009 at 10:06 am

  #1095510

  Gosh that was quick. Here's my belated tardy effort:

  -- Create a sample tally table

  SELECT IDENTITY(int,1,1) as number

  INTO #Numbers

  from syscolumns

  -- Create a sample data table

  DROP TABLE #Sample

  CREATE TABLE #Sample (SampleValue VARCHAR(300))

  INSERT INTO #Sample (SampleValue)

  SELECT 'NAORABRA Bob CONNECT&&FF_SPEC&&PMSADMIN&&FF_STD_FULL&&APMENU_ADMIN_ROLE' UNION

  SELECT 'NAORAZAA WKSYS DBA&&CTXAPP&&CONNECT&&RESOURCE&&JAVASYSPRIV&&JAVAUSERPRIV&&xxx&yyy&zzz'

  -- Create a query which shows lots of workings in it to help user

  -- understand how it works!

  SELECT [Source].[Name of thingy],

  [DelimiterPosition] = n.number+2,

  SUBSTRING([Source].SampleValue, n.number+2, 15),

  [Length to next DelimiterPosition] = CHARINDEX ('&&', [Source].SampleValue, n.number+2) - (n.number+2),

  [Child of thingy] = SUBSTRING([Source].SampleValue, n.number+2,

  CASE WHEN CHARINDEX ('&&', [Source].SampleValue, n.number+2) > 0

  THEN CHARINDEX ('&&', [Source].SampleValue, n.number+2) - (n.number+2)

  ELSE LEN([Source].SampleValue) - n.number+2

  END)

  FROM (

  SELECT SampleValue,

  RHS_of_Name = MAX(n1.number),

  [Name of thingy] = LEFT(SampleValue, MAX(n1.number))

  FROM #Sample s, #Numbers n1

  WHERE SUBSTRING(SampleValue, n1.number,1) = ' '

  AND n1.number <= LEN(SampleValue)

  GROUP BY SampleValue

  ) [Source], #Numbers n

  WHERE SUBSTRING([Source].SampleValue, n.number,2) = '&&'

  AND n.number <= LEN([Source].SampleValue)

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  December 22, 2009 at 12:47 pm

  #1095611

  If you quote the original post, I believe you'll find that there are tabs in the data... not spaces.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  December 22, 2009 at 12:49 pm

  #1095613

  And nice job to you both.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  December 22, 2009 at 1:56 pm

  #1095678

  Murphy,

  The solutions posted by Gus and Chris both use an ancillary "helper" table known as a "Tally" or "Numbers" table. Despite the different names, they are identical in nature. The both consist of a single column of incrementing integers from 1 (or 0) up to some number.

  Please see the following article for how such a thing works to replace loops in a very high performance, set based fashion.

  http://www.sqlservercentral.com/articles/T-SQL/62867/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• mmdmurphy

  Grasshopper

  Points: 21

  More actions

  December 22, 2009 at 3:17 pm

  #1095731

  Well, at the risk of getting stoned, I didn't understand these answers that much.

  I found the critical lines in the visual basic script that gets the data, and changed a copy of it to output the way I wanted. Then I made a copy of the existing SSIS process, changed a few lines there, and am done.

  So, not that I am ungrateful, I just didn't "get it"

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  December 22, 2009 at 8:06 pm

  #1095777

  mmdmurphy (12/22/2009)

  ---

  Well, at the risk of getting stoned, I didn't understand these answers that much.

  I found the critical lines in the visual basic script that gets the data, and changed a copy of it to output the way I wanted. Then I made a copy of the existing SSIS process, changed a few lines there, and am done.

  So, not that I am ungrateful, I just didn't "get it"

  That's unfortunate because it's a fundamental exercise in SQL. Did you read the article I gave the link for?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)