how to get this out put

hi all

this is my quary

SELECT RFVDM_CODE,RFVAL_REFNO,DESCRIPTION
FROM REFERENCEVALUES
GROUP BY RFVDM_CODE,RFVAL_REFNO,DESCRIPTION
ORDER BY RFVDM_CODE

this quary provde a out put like

RFVDM_CODE RFVAL_REFNO DESCRIPTION
ACTDF 2004485 Caseload
ACTDF 2004486 Assessment
ACTDF 2004487 Outpatient appointment
ACTDF 2004488 Contact
ACTDF 2004620 Not Specified
ACTIV 7 Not Specified
ACTIV 217 Book Outpatient Appointment.
ACTPE 8 Not Specified
ACTPE 221 Contact
ACTPE 222 Care Plan Review

i need to get this out put like this

RFVDM_CODE RFVAL_REFNO DESCRIPTION
ACTDF 2004485 Caseload

2004486 Assessment

2004487 Outpatient appointment

2004488 Contact

2004620 Not Specified

ACTIV 7 Not Specified

217 Book Outpatient Appointment.

ACTPE 8 Not Specified

221 Contact

222 Care Plan Review

can i do this

any idea

thanx

its not possible in any single statement. Yes you can do it in SQL Server but not advisable. These kind of formating should be the responsiblity of FE. Do it in FE and its much more flexible.

Madhu

|||

Hi,

Try this one:

select case num when 1 then RFVDM_CODE else '' end RFVDM_CODE, RFVAL_REFNO, DESCRIPTION

from

(select *,

ROW_NUMBER() OVER(Partition By RFVDM_CODE ORDER BY RFVDM_CODE asc) num

from ReferenceValues) s

Although i agree that it should be done on the front end, becuase it's a user layout thing, this table actually has a diferent meaning than the one with the repeated values.

Regards,

Gert-Jan

|||

Gert solution is possible only in SQL Server 2005. mind it.. Thought there are method ,i strongly feel that this should be handled at the FE.

Madhu

|||

Hi thank you.

that is working finely. if there any duplicate values then how we sort out that from this quary.

regards
Nirangasql