Is it possible ??

• Ford Fairlane

  SSCertifiable

  Points: 7664

  More actions

  July 5, 2012 at 1:05 am

  #263268

  I have a table as such

  CREATE TABLE [dbo].[DAuditNew] (

  [EventID] bigint IDENTITY(1, 1) NOT NULL,

  [AuditID] int NOT NULL,

  [AuditStr] nvarchar(32) NOT NULL,

  [AuditDate] datetime NOT NULL,

  [DataID] int NULL,

  [SubType] int NULL,

  [UserID] int NULL,

  [PerformerID] int NOT NULL,

  [ValueKey] nvarchar(255) NULL,

  [Value1] ntext NULL,

  [Value2] ntext NULL,

  [ApplicationID] nvarchar(255) NULL)

  ON [PRIMARY]

  TEXTIMAGE_ON [PRIMARY]

  WITH (DATA_COMPRESSION = NONE);

  Now, I need to do a slect with a group by and this is where it gets interesting, the column I want to groupby, Value1 output looks like this:

  XXX XXXX:Test Indexing:04 Environment and Approvals:00 Remote Operations Centre:01 Application:02 MDP:figures for MDP:Carrooda ANEF.pdf

  With in the row the first 7 charachters are allways the same ie XXX XXXX: everything after that changes. It is basically a folder structure.

  I want to know if it is possible to group by XXX XXX:XXX XXX: so the first two values ending in :

  ANY ideas or solutions would be greatly appreciated.

  Hope this helps...

  Ford Fairlane
  Rock and Roll Detective

  

• Gullimeel

  Hall of Fame

  Points: 3871

  More actions

  July 5, 2012 at 1:17 am

  #1508945

  I want to know if it is possible to group by XXX XXX:XXX XXX: so the first two values ending in :

  I am not sure I got it. Are you want to group by such that the first folder after first 7 character is not involved in the grouping e.g. in this case Test Indexing 04.

  GulliMeel

  Finding top n Worst Performing queries[/url]
  Improve the performance of Merge Join(special case)
  How to Post Performance Problem -Gail Shaw[/url]

• rhythmk

  SSCertifiable

  Points: 7271

  More actions

  July 5, 2012 at 1:25 am

  #1508947

  Ford Fairlane (7/5/2012)

  ---

  I have a table as such

  CREATE TABLE [dbo].[DAuditNew] (

  [EventID] bigint IDENTITY(1, 1) NOT NULL,

  [AuditID] int NOT NULL,

  [AuditStr] nvarchar(32) NOT NULL,

  [AuditDate] datetime NOT NULL,

  [DataID] int NULL,

  [SubType] int NULL,

  [UserID] int NULL,

  [PerformerID] int NOT NULL,

  [ValueKey] nvarchar(255) NULL,

  [Value1] ntext NULL,

  [Value2] ntext NULL,

  [ApplicationID] nvarchar(255) NULL)

  ON [PRIMARY]

  TEXTIMAGE_ON [PRIMARY]

  WITH (DATA_COMPRESSION = NONE);

  Now, I need to do a slect with a group by and this is where it gets interesting, the column I want to groupby, Value1 output looks like this:

  XXX XXXX:Test Indexing:04 Environment and Approvals:00 Remote Operations Centre:01 Application:02 MDP:figures for MDP:Carrooda ANEF.pdf

  With in the row the first 7 charachters are allways the same ie XXX XXXX: everything after that changes. It is basically a folder structure.

  I want to know if it is possible to group by XXX XXX:XXX XXX: so the first two values ending in :

  ANY ideas or solutions would be greatly appreciated.

  Please provide sample data with expected output.

  --rhythmk
  ------------------------------------------------------------------
  To post your question use below link
  
  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
  🙂

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 5, 2012 at 2:10 am

  #1508966

  Does this help?

  DROP TABLE #Temp

  SELECT Value1 = CAST(Value1 AS ntext)

  INTO #Temp

  FROM (

  SELECT Value1 = 'XXX XXXX:Test Indexing:04 Environment and Approvals:00 Remote Operations Centre:01 Application:02 MDP:figures for MDP:Carrooda ANEF.pdf '

  ) d

  SELECT

  t.Value1,

  Leftpart = LEFT(Value1VC,p1.n)

  FROM #Temp t

  CROSS APPLY(SELECT Value1VC = CAST(Value1 AS NVARCHAR(MAX))) c -- ntext does not support CHARINDEX

  CROSS APPLY(SELECT CHARINDEX(':',c.Value1VC,CHARINDEX(':',c.Value1VC,0)+1)) p1 (n)

  ^{“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

• Ford Fairlane

  SSCertifiable

  Points: 7664

  More actions

  July 5, 2012 at 2:24 am

  #1508975

  Sample Data:

  XXX XXXX:16 RHP PMC:11 Document Control:03 Documents for Review:03 Supplier Reviews:RHIO-005620 BGC:100RH-1000-CI-PRO-1001_A.pdf

  XXX XXXX:16 RHP PMC:11 Document Control:03 Documents for Review:03 Supplier Reviews:RHIO-005620 BGC:100RH-1000-CI-PRO-1002_A.pdf

  XXX XXXX:03 Commercial:02 Procurement:07 Purchase Orders:Major Purchase Orders:ROYEWP-R066 - Locomotives:07 NOA:02 Letter of Regret:locomotive letter.pdf

  XXX XXXX:02 Health & Safety:08 HSEHC Documentation:13 Reporting:Monday Incident Report (Craig):2012:WE 120514 - MON Weekly Report.docx

  XXX XXXX:10 Information Management:03 Program Delivery:01 PMO:IT Projects - Project Controls:Reports:10 IT Program Exceptions Report:IM Exceptions Report 14-May-12.pptx

  XXX XXXX:16 RHP PMC:11 Document Control:03 Documents for Review:03 Supplier Reviews:RHIO-005620 BGC:RHIO-005620.pdf

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:03 FMG:01 Correspondence:01 Incoming:PTHMSPSM01_HKK50275_4188_001.pdf

  XXX XXXX:04 Human Resources:HR Operations:Functional Operations:Employee Files:XXX XXXX PERMANENT EMPLOYEES:Mulcahy

  XXX XXXX:04 Human Resources:HR Operations:Functional Operations:Employee Files:XXX XXXX PERMANENT EMPLOYEES:Mulcahy

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:03 FMG:01 Correspondence:01 Incoming:PTHMSPSM01_HKK50275_4189_001.pdf

  XXX XXXX:16 RHP PMC:03 HR:Recruitment:CVs:Daniel Dezentje Worleyparsons Project Director 070512.pdf

  XXX XXXX:16 RHP PMC:03 HR:Recruitment:CVs:Wilson_Barry May 2012.doc

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0913 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:11 Document Control:02 Documents Issued for Use & Contract:04 Registers for Information:XXX XXXX Document Register 120514.xlsx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0916 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:02 HSEHC:16 Environment and Heritage:03 Contractor Management:Weed Hygiene:Day's Contracting:img04798.pdf

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0911 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:WD_DR_2012.05.11.docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:HYDRO_Costing_RHIO_Rail_WS_Bores_2012_05_13.xlsx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0919 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0917 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0914 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:EW_DR_2012.05.13.docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:Rail_WS_SummaryTable_13.05.2012.xlsx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:HYDRO_Costing_Rail_WS_Bores_WD_2012.05.14.xlsx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:WR_2012.05.13.docx

  Expected Sort

  XXX XXXX02 Health & Safety

  XXX XXXX03 Commercial

  XXX XXXX04 Human Resources

  XXX XXXX04 Human Resources

  XXX XXXX10 Information Management

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  Hope this helps...

  Ford Fairlane
  Rock and Roll Detective

  

• Ford Fairlane

  SSCertifiable

  Points: 7664

  More actions

  July 5, 2012 at 2:30 am

  #1508978

  ChrisM@Work (7/5/2012)

  ---

  Does this help?

  DROP TABLE #Temp

  SELECT Value1 = CAST(Value1 AS ntext)

  INTO #Temp

  FROM (

  SELECT Value1 = 'XXX XXXX:Test Indexing:04 Environment and Approvals:00 Remote Operations Centre:01 Application:02 MDP:figures for MDP:Carrooda ANEF.pdf '

  ) d

  SELECT

  t.Value1,

  Leftpart = LEFT(Value1VC,p1.n)

  FROM #Temp t

  CROSS APPLY(SELECT Value1VC = CAST(Value1 AS NVARCHAR(MAX))) c -- ntext does not support CHARINDEX

  CROSS APPLY(SELECT CHARINDEX(':',c.Value1VC,CHARINDEX(':',c.Value1VC,0)+1)) p1 (n)

  Perfect !!! Your a champ thanks !!

  Hope this helps...

  Ford Fairlane
  Rock and Roll Detective

  

• rhythmk

  SSCertifiable

  Points: 7271

  More actions

  July 5, 2012 at 3:11 am

  #1508993

  Ford Fairlane (7/5/2012)

  ---

  Sample Data:

  XXX XXXX:16 RHP PMC:11 Document Control:03 Documents for Review:03 Supplier Reviews:RHIO-005620 BGC:100RH-1000-CI-PRO-1001_A.pdf

  XXX XXXX:16 RHP PMC:11 Document Control:03 Documents for Review:03 Supplier Reviews:RHIO-005620 BGC:100RH-1000-CI-PRO-1002_A.pdf

  XXX XXXX:03 Commercial:02 Procurement:07 Purchase Orders:Major Purchase Orders:ROYEWP-R066 - Locomotives:07 NOA:02 Letter of Regret:locomotive letter.pdf

  XXX XXXX:02 Health & Safety:08 HSEHC Documentation:13 Reporting:Monday Incident Report (Craig):2012:WE 120514 - MON Weekly Report.docx

  XXX XXXX:10 Information Management:03 Program Delivery:01 PMO:IT Projects - Project Controls:Reports:10 IT Program Exceptions Report:IM Exceptions Report 14-May-12.pptx

  XXX XXXX:16 RHP PMC:11 Document Control:03 Documents for Review:03 Supplier Reviews:RHIO-005620 BGC:RHIO-005620.pdf

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:03 FMG:01 Correspondence:01 Incoming:PTHMSPSM01_HKK50275_4188_001.pdf

  XXX XXXX:04 Human Resources:HR Operations:Functional Operations:Employee Files:XXX XXXX PERMANENT EMPLOYEES:Mulcahy

  XXX XXXX:04 Human Resources:HR Operations:Functional Operations:Employee Files:XXX XXXX PERMANENT EMPLOYEES:Mulcahy

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:03 FMG:01 Correspondence:01 Incoming:PTHMSPSM01_HKK50275_4189_001.pdf

  XXX XXXX:16 RHP PMC:03 HR:Recruitment:CVs:Daniel Dezentje Worleyparsons Project Director 070512.pdf

  XXX XXXX:16 RHP PMC:03 HR:Recruitment:CVs:Wilson_Barry May 2012.doc

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0913 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:11 Document Control:02 Documents Issued for Use & Contract:04 Registers for Information:XXX XXXX Document Register 120514.xlsx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0916 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:02 HSEHC:16 Environment and Heritage:03 Contractor Management:Weed Hygiene:Day's Contracting:img04798.pdf

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0911 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:WD_DR_2012.05.11.docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:HYDRO_Costing_RHIO_Rail_WS_Bores_2012_05_13.xlsx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0919 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0917 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:08 Rail:02 Administration:01 Contractors:07 Other Contractors:Outgoing:Native:100RH-LET-3000-0914 Package 2 - Tropical Cyclone Warning - Maintenance of Records (15-Mar-2012).docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:EW_DR_2012.05.13.docx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:Rail_WS_SummaryTable_13.05.2012.xlsx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:HYDRO_Costing_Rail_WS_Bores_WD_2012.05.14.xlsx

  XXX XXXX:16 RHP PMC:12 Construction:PMC Construction:Construction:Water Bore Drilling:Daily Reports:WR_2012.05.13.docx

  Expected Sort

  XXX XXXX02 Health & Safety

  XXX XXXX03 Commercial

  XXX XXXX04 Human Resources

  XXX XXXX04 Human Resources

  XXX XXXX10 Information Management

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  XXX XXXX16 RHP PMC

  How about this..

  Declare @STR varchar(max)

  set @STR = 'XXX XXXX:03 Commercial:02 Procurement:07 Purchase Orders:Major Purchase Orders:ROYEWP-R066 - Locomotives:07 NOA:02 Letter of Regret:locomotive letter.pdf'

  SELECT 'XXX XXXX ' + SUBSTRING(SUBSTRING(@str,10,LEN(@str)),1,CHARINDEX(':',SUBSTRING(@str,10,LEN(@str)))-1)

  --rhythmk
  ------------------------------------------------------------------
  To post your question use below link
  
  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
  🙂

• Gullimeel

  Hall of Fame

  Points: 3871

  More actions

  July 5, 2012 at 3:21 am

  #1508996

  -- ntext does not support CHARINDEX

  But it could very well use the patindex 🙂

  GulliMeel

  Finding top n Worst Performing queries[/url]
  Improve the performance of Merge Join(special case)
  How to Post Performance Problem -Gail Shaw[/url]

• Ford Fairlane

  SSCertifiable

  Points: 7664

  More actions

  July 5, 2012 at 3:23 am

  #1508997

  Not bad but i need to loop through all the values returned not just one by one..... but thank you.

  Hope this helps...

  Ford Fairlane
  Rock and Roll Detective

  

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 5, 2012 at 3:28 am

  #1509002

  Gullimeel (7/5/2012)

  ---

  -- ntext does not support CHARINDEX

  But it could very well use the patindex 🙂

  The third parameter of CHARINDEX makes it very useful for trivial string splitting. How would you use PATINDEX? Can you show with an example?

  ^{“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

• Gullimeel

  Hall of Fame

  Points: 3871

  More actions

  July 5, 2012 at 3:38 am

  #1509004

  Gullimeel (7/5/2012)

  --------------------------------------------------------------------------------

  -- ntext does not support CHARINDEX

  But it could very well use the patindex

  The third parameter of CHARINDEX makes it very useful for trivial string splitting. How would you use PATINDEX? Can you show with an example?

  My answer was just related to this line

  -- ntext does not support CHARINDEX

  and not with whole post.

  Patindex doesnt support 3rd param.But workarounds are always there..

  GulliMeel

  Finding top n Worst Performing queries[/url]
  Improve the performance of Merge Join(special case)
  How to Post Performance Problem -Gail Shaw[/url]