split and query

[simian336]

Hey,

I have not found a good way to do this yet. I am hoping you guys might have a solution. It's 3rd party and not my design and I can't change it.

I have a table with an Id in one field and related records in a second field.

Related records can be... Child records start with C, Linked records start with just a number and Parent records start with a P. You could have all 3 in one field.

id relatedid
10098 14574 C16322
3312 C3261
33067 33071 C33113 P33180
33072 C33073 C33273
33058 33066
7903 P7898
30837 P30838 C30839

I need to a query to use in a report showing parents and childred. So I need to break it down like...

id type relatedid
10098 14574
10098 C 16322
3312 C 3261
33067 33071
33067 C 33113
33067 P 33180
33072 C 33073
33072 C 33273
33058 33066
7903 P 7898
30837 P 30838
30837 C 30839

Then join the master and subs in one query so I can report it.

Thanks for the help

Simi

 

[simian336]

PS for some reason the compatibility level is set to 70 so I need a Sql Server 2000 solution. Or one that can be performed from anouther database.

Simi

 

[SQLBill]

Compatibility Level of 70 is SQL Server 7.0 not SQL Server 2000 (level 80).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[drlex]

I'd coded an answer before the subsequent post about compatibility; perhaps swap in temporary tables or duplicate the first query in place of common table expressions?
Can't take credit for the SP; found on a website a while back.

CREATE FUNCTION [dbo].[SplitString]
(
      @TheString varchar(MAX),
      @Delimiter varchar(5)
) 
RETURNS @RtnValue Table
(
      ID int identity(1,1),
      Segment varchar(100) COLLATE DATABASE_DEFAULT
)
AS 
BEGIN
 
      IF @TheString = '' OR @TheString IS NULL OR @Delimiter IS NULL
            RETURN
      WHILE (CHARINDEX(@Delimiter, @TheString) > 0)
      BEGIN
            INSERT INTO @RtnValue (Segment)
            SELECT
                  Segment = RTRIM(LTRIM(SUBSTRING(@TheString, 1, CHARINDEX(@Delimiter, @TheString) -1)))
            SET @TheString = LTRIM(SUBSTRING(@TheString, CHARINDEX(@Delimiter, @TheString) + LEN(@Delimiter),LEN(@TheString)))
      END
      INSERT INTO @RtnValue (Segment)
      SELECT Segment = RTRIM(LTRIM(@TheString))
      RETURN
END
GO
 
 
; with cte as
(
SELECT '10098 14574 C16322' as info
UNION ALL SELECT '3312 C3261'
UNION ALL SELECT '33067 33071 C33113 P33180'
UNION ALL SELECT '33072 C33073 C33273'
UNION ALL SELECT '33058 33066'
UNION ALL SELECT '7903 P7898'
UNION ALL SELECT '30837 P30838 C30839'
)
, cte2 as (
select cte.*, split.* from cte
cross apply dbo.SplitString(cte.info, ' ') split
)
 
select cte2.Segment as ID,
case when cte3.Segment like '[CP]%' then left(cte3.Segment,1) else '' end as [type],
cte3.Segment as relatedID
from cte2 inner join cte2 as cte3
on cte2.info = cte3.info and cte2.ID = 1 and cte3.ID > 1

soi là, soi carré