How do I merge two rows only if there is the TagWidgetIds are same but the tagValues are different?

i have stored procedure generates following output.

the query generates output stored procedure follows:

select tat.[agent-id],                 tat.[agent-guid],                 tat.[agent-name]  [agentname],                 tw.tagwidgetid,                 tw.tagwidgetname,                 t.tagid,                 core.fninitialcapitalization(t.displayname) tagname,                 tt.tagtypeid,                 tt.tagtypename,                 tdt.tagdatatypeid,                 tdt.tagdatatype,                 tu.tagunitid,                 tu.tagunit,                 --case                  --     when itv.tagindex null 0                 --     else itv.tagindex                 --end               [tagindex],                 isnull(itv.tagindex, 0) [tagindex],                 --case                  --     when itv.valueindex null 0                 --     else itv.valueindex                 --end               [valueindex],                 isnull(itv.valueindex, 0) [valueindex],                 --case                  --     when itv.[value] null 'n/a'                 --     else itv.[value]                 --end               [tagvalue]                  isnull( itv.[value], 'n/a') [tagvalue]            @tmpagenttags tat                 left outer join tag.itemtagvalue itv                      on  itv.fktagid = tat.[tag-id]                      , itv.fkitemid = tat.[agent-id]                 inner join tag.tag t                      on  t.tagid = tat.[tag-id]                 inner join tag.tagwidget tw                      on  tw.tagwidgetid = t.fktagwidgetid                 inner join tag.tagtype tt                      on  tt.tagtypeid = t.fktagtypeid                 inner join tag.tagdatatype tdt                      on  tdt.tagdatatypeid = t.fktagdatatypeid                 inner join tag.tagunit tu                      on  tu.tagunitid = t.fktagunitid                 inner join item.item                      on i.pkitemid = tat.[agent-id]          i.fkitemtypeid not in (5,8)

how can join output rows if tagwidegetids same tagvalues, tagindex , valueindex columns differnt? please advice.

---

mayooran99

the example gave, these rows in different tables. asking is, when retrieving records same table, , if assume 2 rows of table result. 2 rows have 3 columns in common , 4th column different. want output 2 rows combined one, differing 4th column values put comma separated values in row returned now. please tell me how this.

---

mayooran99

use example

declare @table table(sno int,sname varchar(20),scity varchar(20)) insert @table values(1,'richard','dallas'),(1,'richard','dallas'),(1,'richard','houston') ,(2,'will','dallas'),(2,'will','dallas'),(3,'doug','houston')  select distinct sno ,sname, stuff((select distinct ','+scity @table t1 t1.sno=t2.sno , t1.sname=t2.sname  xml path('')),1,1,'') scity @table t2

---

hope helps!!

SQL Server  >  Getting started with SQL Server