SELECT AppNo,
[1] AS AppName1,
[2] AS AppName2,
[3] AS AppName3,
[4] AS AppName4,
[5] AS AppName5
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY AppNo ORDER BY AppNo) AS Seq,*
FROM Table
)t
PIVOT(MAX(AppName) FOR Seq IN ([1],[2],[3],[4],[5]))p

AppNo       AppName1   AppName2   AppName3  AppName4  AppName5

-------     --------   ---------  --------- --------- --------

100         Lawrence   Smith      Raja      Harsha    Fedal

quote:

---

Originally posted by shinelawrence

thanks for ur reply... It's working but i need only one row... Here i got sepearate columns but same 5 rows... i need one row only...Just tell the solution...


Thanks In Advanced

Lawce

---

SELECT ApplicationNo_FK,
[1] AS AppName1,
[2] AS AppName2,
[3] AS AppName3,
[4] AS AppName4,
[5] AS AppName5
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ApplicationNo_FK ORDER BY ApplicationNo_FK) AS Seq,	ApplicationNo_FK,	 ApplicantName
FROM LCA_Doclist_detail
)t
PIVOT(MAX(ApplicantName) FOR Seq IN ([1],[2],[3],[4],[5]))p