SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extracting several rows of information


Extracting several rows of information

Author
Message
craig.y.fraser
craig.y.fraser
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 6
Hi Guys

I am very new to SQL and I am busy using microsoft access to learn the syntax.

I am trying to write a query that selects multiple rows of a table and puts those into a single field.

I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.

"Starting Key Word"
*row data to select*
*row data to select*
*row data to select*
"Ending Key Word"

Those 3 rows are then put into 1 field.

I hope you will be able to assist.

Thanks.
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5200 Visits: 2692
please share sample data and expected output, and is this query for MSAccess or SQL Server?
craig.y.fraser
craig.y.fraser
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 6
twin.devil (1/31/2014)
please share sample data and expected output, and is this query for MSAccess or SQL Server?


Hi sorry, this is an Access Query. All the data that I have is from an OpenVMS Sysuaf.lis file which is completely unorganised, as you can see below, there is no real structure but there are key words which identify what we need to extract.

ID FullContent
446 Primary days: Mon Tue Wed Thu Fri
447 Secondary days: Sat Sun
448 No access restrictions
449 Expiration: (none) Pwdminimum: 8 Login Fails: 0
450 Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)
451 Last Login: (none) (interactive), (none) (non-interactive)
452 Maxjobs: 0 Fillm: 1024 Bytlm: 130000
453 Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0
454 Maxdetach: 0 BIOlm: 4096 JTquota: 8192
455 Prclm: 10 DIOlm: 4096 WSdef: 100000
456 Prio: 4 ASTlm: 250 WSquo: 200000
457 Queprio: 0 TQElm: 20 WSextent: 210000
458 CPU: (none) Enqlm: 2000 Pgflquo: 4000000
459 Authorized Privileges:
460 ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461 CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462 GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463 NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464 PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465 SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466 UPGRADE VOLPRO WORLD
467 Default Privileges:

Sample Data:

ID FullContent
459 Authorized Privileges:
460 ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461 CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462 GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463 NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464 PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465 SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466 UPGRADE VOLPRO WORLD
467 Default Privileges:

Output Expected:

"
ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CMKRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,GROUP,GRPNAM,GRPPRV,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER…etcc"

The delimiter can be anything.
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69540 Visits: 40917
not sure how much help this would be.
the CTE below is just so I have a table that represents the data you provided.
only the select is the part you'd want to look at.

i think this brackets the data you are asking for; it does identify the rows based on a beginning value and an ending value.
so do you need the subsequent rows as a single row of data?


;WITH MyCTE([ID],[FullContent])
AS
(
SELECT '446','Primary days: Mon Tue Wed Thu Fri' UNION ALL
SELECT '447','Secondary days: Sat Sun' UNION ALL
SELECT '448','No access restrictions' UNION ALL
SELECT '449','Expiration: (none) Pwdminimum: 8 Login Fails: 0' UNION ALL
SELECT '450','Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)' UNION ALL
SELECT '451','Last Login: (none) (interactive), (none) (non-interactive)' UNION ALL
SELECT '452','Maxjobs: 0 Fillm: 1024 Bytlm: 130000' UNION ALL
SELECT '453','Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0' UNION ALL
SELECT '454','Maxdetach: 0 BIOlm: 4096 JTquota: 8192' UNION ALL
SELECT '455','Prclm: 10 DIOlm: 4096 WSdef: 100000' UNION ALL
SELECT '456','Prio: 4 ASTlm: 250 WSquo: 200000' UNION ALL
SELECT '457','Queprio: 0 TQElm: 20 WSextent: 210000' UNION ALL
SELECT '458','CPU: (none) Enqlm: 2000 Pgflquo: 4000000' UNION ALL
SELECT '459','Authorized Privileges:' UNION ALL
SELECT '460','ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS' UNION ALL
SELECT '461','CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP' UNION ALL
SELECT '462','GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT' UNION ALL
SELECT '463','NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL' UNION ALL
SELECT '464','PRMMBX PSWAPM READALL SECURITY SETPRV SHARE' UNION ALL
SELECT '465','SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX' UNION ALL
SELECT '466','UPGRADE VOLPRO WORLD' UNION ALL
SELECT '467','Default Privileges:'
)
SELECT * FROM MyCTE
WHERE ID > (SELECT ID FROM MyCTE WHERE FullContent = 'Authorized Privileges:')
AND ID < (SELECT ID FROM MyCTE WHERE FullContent = 'Default Privileges:')



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
craig.y.fraser
craig.y.fraser
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 6
Lowell (1/31/2014)
not sure how much help this would be.
the CTE below is just so I have a table that represents the data you provided.
only the select is the part you'd want to look at.

i think this brackets the data you are asking for; it does identify the rows based on a beginning value and an ending value.
so do you need the subsequent rows as a single row of data?


;WITH MyCTE([ID],[FullContent])
AS
(
SELECT '446','Primary days: Mon Tue Wed Thu Fri' UNION ALL
SELECT '447','Secondary days: Sat Sun' UNION ALL
SELECT '448','No access restrictions' UNION ALL
SELECT '449','Expiration: (none) Pwdminimum: 8 Login Fails: 0' UNION ALL
SELECT '450','Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)' UNION ALL
SELECT '451','Last Login: (none) (interactive), (none) (non-interactive)' UNION ALL
SELECT '452','Maxjobs: 0 Fillm: 1024 Bytlm: 130000' UNION ALL
SELECT '453','Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0' UNION ALL
SELECT '454','Maxdetach: 0 BIOlm: 4096 JTquota: 8192' UNION ALL
SELECT '455','Prclm: 10 DIOlm: 4096 WSdef: 100000' UNION ALL
SELECT '456','Prio: 4 ASTlm: 250 WSquo: 200000' UNION ALL
SELECT '457','Queprio: 0 TQElm: 20 WSextent: 210000' UNION ALL
SELECT '458','CPU: (none) Enqlm: 2000 Pgflquo: 4000000' UNION ALL
SELECT '459','Authorized Privileges:' UNION ALL
SELECT '460','ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS' UNION ALL
SELECT '461','CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP' UNION ALL
SELECT '462','GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT' UNION ALL
SELECT '463','NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL' UNION ALL
SELECT '464','PRMMBX PSWAPM READALL SECURITY SETPRV SHARE' UNION ALL
SELECT '465','SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX' UNION ALL
SELECT '466','UPGRADE VOLPRO WORLD' UNION ALL
SELECT '467','Default Privileges:'
)
SELECT * FROM MyCTE
WHERE ID > (SELECT ID FROM MyCTE WHERE FullContent = 'Authorized Privileges:')
AND ID < (SELECT ID FROM MyCTE WHERE FullContent = 'Default Privileges:')



Hi Lowell

Thanks for the response and effort.

I get an error saying "At most one record can be returned by this subquery" and then nothing happens.....
Amit Raut
Amit Raut
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1217 Visits: 342
It's because either or both of the queries are returning more than one ID.

SELECT ID FROM MyCTE WHERE FullContent = 'Authorized Privileges:'
SELECT ID FROM MyCTE WHERE FullContent = 'Default Privileges:'

Do you have, in your data more than one records with FullContent = 'Authorized Privileges:' or FullContent = 'Default Privileges:'?

If yes, you must get only one ID from the above queries. Use distinct/max/min/ Top 1 as per your requirement.
craig.y.fraser
craig.y.fraser
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 6
Thanks Amit.

Yes there are 1780 users in the data, so each would have their own privileges.

Here is an extract of the file for 2 different users: >>>

*the words highlighted in blue is what we are trying to extract

Quote:
Username: ALANCPS Owner: PENSIONS
Account: PENSION UIC: [300,0] ([PENSIONS])
CLI: DCL Tables: DCLTABLES
Default: CPS_DEVICE:[CPS]
LGICMD: LOGIN
Flags: DisUser
Primary days: Mon Tue Wed Thu Fri
Secondary days: Sat Sun
No access restrictions
Expiration: (none) Pwdminimum: 8 Login Fails: 0
Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)
Last Login: (none) (interactive), (none) (non-interactive)
Maxjobs: 0 Fillm: 1024 Bytlm: 130000
Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0
Maxdetach: 0 BIOlm: 4096 JTquota: 8192
Prclm: 10 DIOlm: 4096 WSdef: 100000
Prio: 4 ASTlm: 250 WSquo: 200000
Queprio: 0 TQElm: 20 WSextent: 210000
CPU: (none) Enqlm: 2000 Pgflquo: 4000000
Authorized Privileges:
ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
UPGRADE VOLPRO WORLD
Default Privileges:
ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
UPGRADE VOLPRO WORLD


AND

Quote:
Username: AFFLNETC07 Owner: PENSIONS
Account: PENSION UIC: [300,0] ([PENSIONS])
CLI: DCL Tables: DCLTABLES
Default: CPS_DEVICE:[CPS]
LGICMD: LOGIN
Flags: DisCtlY Restricted DisUser
Primary days: Mon Tue Wed Thu Fri
Secondary days: Sat Sun
Primary 000000000011111111112222 Secondary 000000000011111111112222
Day Hours 012345678901234567890123 Day Hours 012345678901234567890123
Network: ----- No access ------ ----- No access ------
Batch: ##### Full access ###### ##### Full access ######
Local: ##### Full access ###### ##### Full access ######
Dialup: ----- No access ------ ----- No access ------
Remote: ##### Full access ###### ##### Full access ######
Expiration: (none) Pwdminimum: 8 Login Fails: 0
Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)
Last Login: (none) (interactive), (none) (non-interactive)
Maxjobs: 0 Fillm: 1024 Bytlm: 64000
Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0
Maxdetach: 0 BIOlm: 150 JTquota: 4096
Prclm: 2 DIOlm: 150 WSdef: 2000
Prio: 4 ASTlm: 250 WSquo: 2048
Queprio: 0 TQElm: 20 WSextent: 16384
CPU: (none) Enqlm: 2000 Pgflquo: 900000
Authorized Privileges:
NETMBX TMPMBX
Default Privileges:
NETMBX TMPMBX



As you can see not all users have the same number of privileges.

So basically this is my thought process: >>>

There are 1780 users in the text file.

For Authorised Privileges:
-> Read every line until textline = "Authorised Privileges"
-> Write every line after this to a single string file with a "," delimiter
-> Stop writing until textline = "Default Privileges"

Expected output:

String 1: ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CMKRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,GROUP,GRPNAM,GRPPRV,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER,PFNMAP...etc...,WORLD

String 2:
NETMBX,TMPMBX
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search