Re: Reformat Group membership table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi
CREATE TABLE #t(c1 VARCHAR(20),c2 VARCHAR(100))

INSERT INTO #t VALUES ('Group1','User1,User2,User3')

INSERT INTO #t VALUES ('Group2','User4,User5')

INSERT INTO #t VALUES ('Group3','User6,User7,User8')



SELECT IDENTITY(INT) "n" INTO Numbers

FROM sysobjects s1

CROSS JOIN sysobjects s2

GO

SELECT c1,SUBSTRING(c2, n, CHARINDEX(',', c2 + ',', n) - n)

from numbers,#t where substring(','+c2,n,1)=','

AND n < LEN(c2) + 1





"reformat table" <reformat table@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:14AC1E72-4D6F-4371-9F38-0CD878034B04@xxxxxxxxxxxxxxxx
Environment is SQL 2000 SP4

I have a 3rd party application which exports ActiveDirectory group
membership to a SQL table. Each day the export process creates a table
with
the current date listed in the table name. The table is formatted as
shown
below with two columns:

Group1 User1%CR%User2%CR%User3
Group2 User4%CR%User5
Group3 User6%CR%User7%CR%User8

*I've inserted '%CR% where there is a carriage return.

The table has the group name in one column and the users names in the
second
column separated by carriage return.

I would like to run SQL queries that returns a result set which has all
the
users of a group... with each user on in a separate row. The only way I
believe I can do this effectively is to reformat the table to a format
something like the table shown below:

Group1 User1
Group1 User2
Group1 User3
Group2 User4
Group2 User5
Group3 User6
....

Then do some query like:
Select * From groupTable Where Group='Group2'

Results:
Group2 User4
Group3 User5

I'm looking for suggestions on how I can accomplish my SQL query...
whether
it's by reformatting the table and then running a query to determine the
group membership or maybe someone has a super secret query that will get
the
information I'm looking for. The other key to this is that I need a
solution
that is easy to implment since this group export occurs every day.

Your help is appreciated!
Brian


.

Relevant Pages

  • Reformat Group membership table
    ... Environment is SQL 2000 SP4 ... believe I can do this effectively is to reformat the table to a format ... I'm looking for suggestions on how I can accomplish my SQL query... ... group membership or maybe someone has a super secret query that will get the ...
    (microsoft.public.sqlserver.programming)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)