Sean Lange (12/18/2012)
Here is an example using the DelimitedSplit8K function that you can find at the link provided, or you can follow the link in my signature for splitting strings.
;with cte (Col1) as
(
select '/Adhoc/CRM/Complaints report/Report on owner level' union all
select '/Adhoc/Finance/General Ledger/Spend Analysis report'
)
select *
from cte
cross apply dbo.DelimitedSplit8K(right(Col1, len(Col1) -1), '/')
Notice how I posted data in an easily consumable format. You should consider that on future posts.
Also, I noticed you are using the NOLOCK hint. Are you aware of the challenges that hint brings to the table? It will help your code run faster but it comes at a cost. Most people say "I don't care about dirty reads". This hint has far greater implications than just dirty reads. You can missing or even duplicate information. Here are a couple of articles that explains this hint in greater detail.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Thanks a alot for the example. It really did help me, this is how my qeury look like:
SELECT(SELECT C.Path AS Path, C.Name AS ReportName
, CASE WHEN C.Hidden = 1 THEN 'True'
ELSE 'False' END AS ReportHide
, COALESCE(C.Description, '<none>') AS ReportDesc
, UC.UserName AS ReportCreatedBy, C.CreationDate AS ObjectCreatedDate
, UM.UserName AS ReportModifiedBy, C.ModifiedDate AS ObjectModifedDate
, D.*
FROM dbo.Catalog C
INNER JOIN dbo.Users UC WITH(NOLOCK)
ON C.CreatedByID = UC.UserID
INNER JOIN dbo.Users UM WITH(NOLOCK)
ON C.ModifiedByID = UM.UserID
CROSS APPLY Ceyenne_JDE.dbo.DelimitedSplit8K(C.Path, '/') D
WHERE 1=1
AND C.Type = 2
--AND C.Path = '/Adhoc/CRM/20121024 CRM complaints report on owner level'
) Q
PIVOT ( MAX(Item)
FOR ItemNumber IN ([2], [3], [4], [5], [6], [7], [8])
) AS pvt
)