Extracting several rows of information

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting several rows of information

Author

Topic

craigf12
Starting Member

1 Post

Posted - 2014-01-31 : 09:42:15

Hi Guys

I have the following problem.

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 string with a comma delimiter.

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.

Example below: >>>

The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-03 : 05:14:25

If you're asking for t-sql query use this


SELECT STUFF((SELECT ',' + REPLACE(FullContent,' ',',')
FROM Table
WHERE ID > 459
ORDER BY ID
FOR XML PATH('')),1,1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources