quote:

---

Originally posted by byomjan

rows of a table

STUDENT101 101
ROLLN101 101
MARKS101 78
CLASS101 A1
END101 101
UNIVERSITY KK
STUDENT105 105
ROLLN105 105
MARKS105 100
CLASS105 A1
END105 105
XXX XXX
SERIAL 1.01



I want to loop repeatitive groups i.e from STUDENT*** Untill it hits it's corresponding END*** with the use of a cursor.

Can you please advise.



Byomjan....

---

SELECT MAX(CASE WHEN Col LIKE 'STUDENT%' THEN Val ELSE NULL END) AS Student,
MAX(CASE WHEN Col LIKE 'ROLLN%' THEN Val ELSE NULL END) AS RollNo,
MAX(CASE WHEN Col LIKE 'MARKS%' THEN Val ELSE NULL END) AS Marks,
MAX(CASE WHEN Col LIKE 'CLASS%' THEN Val ELSE NULL END) AS Class,
MAX(CASE WHEN Col LIKE 'END%' THEN Val ELSE NULL END) AS [End]
FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(Col,PATINDEX('%[0-9]%',Col)-1) ORDER BY SUBSTRING(Col,PATINDEX('%[0-9]%',Col)+1,LEN(Col))*1) AS Seq,Col,Val
FROM
(
SELECT 'STUDENT101' AS Col,'101' AS Val  UNION ALL
SELECT 'ROLLN101',	'101'  UNION ALL
SELECT 'MARKS101',	'78'  UNION ALL
SELECT 'CLASS101',	'A1'  UNION ALL
SELECT 'END101',	 '101'  UNION ALL
SELECT 'UNIVERSITY',	'KK'	  UNION ALL
SELECT 'STUDENT105',	'105'  UNION ALL
SELECT 'ROLLN105',	'105'  UNION ALL
SELECT 'MARKS105',	'100'  UNION ALL
SELECT 'CLASS105',	'A1'  UNION ALL
SELECT 'END105',	 '105'  UNION ALL
SELECT 'XXX',	 'XXX'  UNION ALL
SELECT 'SERIAL',	 '1.01'
)t
WHERE Col LIKE '%[0-9]%'
)r
GROUP BY Seq

output
-------------------------------------------
Student	RollNo	Marks	Class	End
101	101	78	A1	101
105	105	100	A1	105