Concatenate multiple row into single row

Question

SELECT RESVID, VEHTYPE FROM (SELECT dbo.T_VEH_VEHRESERVATION.RESVID, TABLEVEHICLE.VEHTYPE, TABLEVEHICLE.VEHREGID FROM (SELECT dbo.T_VEH_VEHCATEGORY.VEHBRANDNAME + N' ' + dbo.T_VEH_VEHCLASS.VEHCLASSNAME AS VEHTYPE, dbo.T_VEH_VEHREGISTRATION.VEHREGID FROM dbo.T_VEH_VEHCATEGORY WHERE (dbo.T_VEH_VEHREGISTRATION.DELIND = 0) ) AS TABLEVEHICLE INNER JOIN dbo.T_VEH_VEHRESERVATION ON TABLEVEHICLE.VEHREGID = dbo.T_VEH_VEHRESERVATION.VEHREGID ) GROUP BY dbo.T_VEH_VEHRESERVATION.RESVID ) AS TABLERESV

enter image description here

I would like to concatenate the RESVID: 33 (duplicate row) into 1.

This is just an example, of course what I Want is every duplicate row with this situation will be solved.

I try the XML PATH function, but does not work at all, and I have no idea what goes wrong, i will be appreciate if any help me on my code, and give me explanation on that.

My desire result:

RESVID | VEHTYPE ------------------- 33 | Proton Economic, Toyota Mid-size SUV

and i have only one table on my view

enter image description here

Thanks

Amit Singh · Accepted Answer · 2013-06-18 03:54:02Z

2

Try like this....

Select Distinct RESVID ,Stuff((Select ','+VEHTYPE from TabelName A where A.RESVID =B.RESVID For Xml Path('')),1,1,'') as VEHTYPE from TabelName B

edited Jun 18, 2013 at 3:54

answered Jun 18, 2013 at 3:40

Amit Singh

8,12922 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

taekiewzz Over a year ago

Hi there, thank for your reply, this method i have tried, but always get sql execution error. Mostly having problems near 'where'. What cause this error? Thanks

taekiewzz Over a year ago

SELECT RESVID, STUFF(( SELECT ', ' + VEHTYPE FROM TABLERESV A WHERE (A.VEHREGID = B.VEHREGID) FOR XML PATH('')), 1,2, '') AS VEHTYPE FROM TABLERESV B

Amit Singh Over a year ago

try A.RESVID= B.RESVID like this....here sql fiddle demo...sqlfiddle.com/#!3/f53e4/3

taekiewzz Over a year ago

let us continue this discussion in chat

Amit Singh Over a year ago

i can not chat i m in office

|

Nithesh Narayanan · Accepted Answer · 2013-06-18 04:37:28Z

Try with STUFF()

SELECT RESVID , STUFF(( SELECT ','+ VEHTYPE FROM TABLERESV b WHERE a.RESVID = b.RESVID FOR XML PATH('') ) ,1,1,'') AS VEHTYPE FROM TABLERESV a GROUP BY a.RESVID

Ahmed · Accepted Answer · 2013-06-18 06:17:14Z

trY this:

 SELECT B.resvid, STUFF( (SELECT ',' + A.vehtype FROM TABLE_NAME A WHERE A.resvid=B.resvid FOR XML PATH('')),1,1,'') AS CSV FROM TABLE_NAME B GROUP BY B.resvid

Collectives™ on Stack Overflow

Concatenate multiple row into single row

3 Answers 3

11 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

Comments

Related