Hi Saeed,
Assuming ur table name as "tableTest" .
if u want course details in one row use this
-----------course------------
---coursid=1 coursename=x---
with temp as
(SELECT ('coursid = '+convert(varchar,coursid)+' coursename = '+coursename) as course,coursid, teachername,stage,startdate, row_number() over(partition by coursid order by coursid) rn
FROM tableTest as b)
SELECT Case WHEN rn>1 THEN '' ELSE course END as course, teachername,stage,startdate,coursid
from temp
order by coursid,stage,teachername,startdate ;
if u want course details in two rows use this
-----------course------------
--- coursid=1 ----
--- coursename=x ----
with temp as
(SELECT ('coursid = '+convert(varchar,coursid)) as course1,+'coursename = '+coursename as course,coursid, teachername,stage,startdate, row_number() over(partition by coursid order by coursid) rn
FROM tableTest as b)
SELECT Case WHEN rn>1 THEN course ELSE course1 END as course, teachername,stage,startdate,coursid
from temp
union
SELECT course,null as teachername,null as stage,null as startdate,coursid
from temp group by coursid,course
having count( coursid)=1
order by coursid,course desc ,stage
good luck ;-)