如何在sql server中连接两列 [英] how to concatenate two columns 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想要连接Batchid和Cid并获得输出如下

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

使用上面的查询怎么样我连接Batchid和Cid。

from using above query how can i concatenate the Batchid and Cid.

推荐答案

使用查询

Use Query

With CTE(stud_name,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 stud_name,BatchID,Cid, BatchID+'/'+ Cid as result from CTE

如果您的列BatchID和Cid不是nvarchar类型，则将其强制转换为nvarchar，例如

If your column BatchID and Cid are not of nvarchar type the cast it to nvarchar like

Cast(BatchID as nvarchar)+'/'+Cast(Cid as nvarchar) as result

试试这个

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

这篇关于如何在sql server中连接两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！