Posted by rcamarda on 06/27/07 11:22

Hello,
I am loading data from our MS Active Directory into our data
warehouse. (check out Mircosofts's Logparser, it can pull data from
ADS, server event logs and more. It can also create text files or load
directly to SQL. Its free and a pretty useful tool)
There is a field that contains the direct reports of a manager. The
direct report users are delimited by a pipe symbol.
I want to breakup the field into multple rows. There can be none, one
or many direct report users in this field.
<disclaimer>
This is a snippet of an example. This is only an example. I know that
I have not defined PK nor indexes. My focus is how to solve a problem
of parsing a field that has multple values into multple rows.
</disclaimer>
Thanks for any help in advance.
Rob

CREATE TABLE "dbo"."F_ADS_MANAGERS"
(
"MANAGER_KEY" VARCHAR(255) NULL,
"DIRECT_REPORTS_CN" VARCHAR(255) NULL
);

INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)
VALUES ('CN=Marilette, 'CN=Robert
D,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Robert
Camarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Michelle
C,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia
B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')

I want to end up with 5 rows, 1 row for each user that is seprated by
the PIPE symbol.
CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Robert
Camarda,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Lee K,OU=TechnologyGroup

[Back to original message]