T-SQL help urgent

Question

hi I have a table 

SELECT * from TABLE 

ID    PID

1     155|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50|51

2      48|49|50|52|56|57|58|59|60

3      155|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50|51

4      05|106|107|108|109|110|111|112|113|114|115|116|117|118|119|120|121|122|123|124|125|126|127|128|129|130|131|132|133|134|135|136

I have the o/p above 

I want the o/p to be 

ID    PID

1     155

 1     34

1      35

1       36

1       37

1      38

1      39 

1     40 

1      41 

and 

2    48

2  49

2   50

and so on 

can any one please provide a T-SQL script to do this ..

Thanks

Answer 1

You can do this with any of the many split functions out there:

Select	t.ID
		,s.Item
From	myTable t
Cross Apply dbo.fncSplit(t.PID, '|') s

Answer 2

Here you go:

;WITH MyTABLE
AS
(
 SELECT 1 AS ID, '155|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50|51' As PID UNION ALL
 SELECT 2, '48|49|50|52|56|57|58|59|60' UNION ALL
 SELECT 3, '155|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50|51' UNION ALL
 SELECT 4, '05|106|107|108|109|110|111|112|113|114|115|116|117|118|119|120|121|122|123|124|125|126|127|128|129|130|131|132|133|134|135|136'
)
,
CTE2 
As
(
SELECT *,
 CAST('<row>' + REPLACE(PID,'|', '</row><row>') + '</row>' AS XML)Col2
FROM MyTABLE
)

SELECT  ID, CAST(Col.query('./text()') AS VARCHAR)
FROM 
CTE2 O
CROSS APPLY
Col2.nodes('/row')
As Tab(Col)

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 3

Thank you very much 

I used this function u mentioned and i applied yiour script it wokrs fine ...thanks a lot

http://www.tactek.com/2011/02/improved-fnsplit-function-for-sql-server/

Answer 4

Please mark the post as answer so that other people with the same question can easily find the answer.

Thank you.

Answer 5

i did now ...Thanks