You must Sign In to post a response.
  • Category: SQL Server

    How to concatenate two fields in sql server

    My query as follows

    select a.stud_name,
    BatchID = (select eid from BATCHID where bid = 'B10458' and b_activ = 'A'),
    Cid = (select top 1 certificate_no from certificate_detail where stud_id = a.stud_id and active = 'A' and crsname = 'RPSCRB')
    from student a, course_registration b, batch_course_registration c where
    a.stud_id=b.stud_id and b.cr_bill_no=c.cr_bill_no
    and c.bcr_batch_id= 'B10458'
    and b.cr_active='A' and a.stud_active<>'D'

    When i run the above query output as follows

    stud_name Batchid Cid
    Ram RPSCRB/B01 1


    i want to concatenate the Batchid and Cid and get the output as follows

    stud_name Batchid Cid Result
    Ram RPSCRB/B01 1 RPSCRB/B01/1


    from using above query how can i concatenate the Batchid and Cid.
  • #763233
    Try this


    ;WITH TEMPCTE(StudentName,BatchID,CID)
    AS
    (
    select a.stud_name,
    BatchID = (select eid from BATCHID where bid = 'B10458' and b_activ = 'A'),
    Cid = (select top 1 certificate_no from certificate_detail where stud_id = a.stud_id and active = 'A' and crsname = 'RPSCRB')
    from student a, course_registration b, batch_course_registration c
    where a.stud_id=b.stud_id and b.cr_bill_no=c.cr_bill_no
    and c.bcr_batch_id= 'B10458'
    and b.cr_active='A' and a.stud_active<>'D'
    )

    SELECT StudentName,BatchID,CID, Convert(varchar(20),BatchID)+'/'+Convert(varchar(10),CID)
    FROM TEMPCTE

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #763241
    select stud_name,BatchID + Cid
    from
    (
    select a.stud_name,
    BatchID = (select eid from BATCHID where bid = 'B10458' and b_activ = 'A'),
    Cid = (select top 1 certificate_no from certificate_detail where stud_id = a.stud_id and active = 'A' and crsname = 'RPSCRB')
    from student a, course_registration b, batch_course_registration c where
    a.stud_id=b.stud_id and b.cr_bill_no=c.cr_bill_no
    and c.bcr_batch_id= 'B10458'
    and b.cr_active='A' and a.stud_active<>'D'
    )


    cheer!!

  • #763295
    Hello,

    Please try with the following SQL query :-

    select stud_name, Batchid, Cid, Convert(varchar(20),Batchid) + '/' + Convert(varchar(20),Cid) from
    (select a.stud_name,
    BatchID = (select eid from BATCHID where bid = 'B10458' and b_activ = 'A'),
    Cid = (select top 1 certificate_no from certificate_detail where stud_id = a.stud_id and active = 'A' and crsname = 'RPSCRB')
    from student a, course_registration b, batch_course_registration c where
    a.stud_id=b.stud_id and b.cr_bill_no=c.cr_bill_no
    and c.bcr_batch_id= 'B10458'
    and b.cr_active='A' and a.stud_active<>'D') as tbl


    Thanks


Sign In to post your comments