Data reformatting question

Question

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
???

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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: 1004414 More actions · Answer 4

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: 1004414 More actions · Answer 5

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: 1004414 More actions · Answer 6

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 · Answer 7

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: 1004414 More actions · Answer 8

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)