Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Combining Rows on Complicated Query

Author  Topic  edwardv Starting Member 1 Post Posted - 2010-12-04 : 18:11:43 I have a fairly complicated query that I run to create an XML with the information that I need. I would like to add one more column to the query that combines several of the rows into a single column. col1 col2 col3 col4 col5 (date) (street#) (Road) (RoadType) (street#Road,Roadtype) I am tried COALESCE and had some errors I am not sure where to go from here. Any help would be appreciated. Select i.INCDATE "INC/Date", t.IncDesc "INC/Description", "INC/Address" = round(i.STRNO,-2), "INC/Prefix" = STUFF((SELECT ' ' + convert(varchar(max), pnt.NAME) From Inc i inner join STREETS s on I.STREETCITY = S.ID inner join COMPASSPOINT pnt on s.PREFIXDIR = pnt.ID Where i.Id = a.IncId and s.PREFIXDIR <> 0 for xml path('')), 1, 1, ''), "INC/Street" = STUFF((SELECT ' ' + convert(varchar(max), s.STRNAME) From Inc i inner join STREETS s on I.STREETCITY = S.ID Where i.Id = a.IncId for xml path('')), 1, 1, ''), "INC/Type" = STUFF((SELECT ' ' + convert(varchar(max), st.NAME) From Inc i inner join STREETS s on I.STREETCITY = S.ID inner join STREETTYPE st on s.STREETTYPE = st.ID Where i.Id = a.IncId for xml path('')), 1, 1, ''), "INC/Suffix" = STUFF((SELECT ' ' + convert(varchar(max), pnt.NAME) From Inc i inner join STREETS s on I.STREETCITY = S.ID inner join COMPASSPOINT pnt on s.SUFFIXDIR = pnt.ID Where i.Id = a.IncId and s.SUFFIXDIR <> 0 for xml path('')), 1, 1, ''), "INC/CPrefix" = STUFF((SELECT ' ' + convert(varchar(max), pnt.NAME) From Inc i inner join STREETS s on I.CROSSSTREET = S.ID inner join COMPASSPOINT pnt on s.PREFIXDIR = pnt.ID Where i.Id = a.IncId and s.PREFIXDIR <> 0 for xml path('')), 1, 1, ''), "INC/CrossStreet" = STUFF((SELECT ' ' + convert(varchar(max), s.STRNAME) From Inc i inner join STREETS s on I.CROSSSTREET = S.ID Where i.Id = a.IncId for xml path('')), 1, 1, ''), "INC/CType" = STUFF((SELECT ' ' + convert(varchar(max), st.NAME) From Inc i inner join STREETS s on I.CROSSSTREET = S.ID inner join STREETTYPE st on s.STREETTYPE = st.ID Where i.Id = a.IncId for xml path('')), 1, 1, ''), "INC/CSuffix" = STUFF((SELECT ' ' + convert(varchar(max), pnt.NAME) From Inc i inner join STREETS s on I.CROSSSTREET = S.ID inner join COMPASSPOINT pnt on s.SUFFIXDIR = pnt.ID Where i.Id = a.IncId and s.SUFFIXDIR <> 0 for xml path('')), 1, 1, ''), "INC/Apparatus" = STUFF((SELECT ',' + convert(varchar(max), n.Unit) FROM IncApp a inner join App n on a.AppId = n.Id Where i.Id = a.IncId FOR XML PATH('')), 1, 1, ''), "INC/City" = STUFF((SELECT ' ' + convert(varchar(max), dpt.NAME) From Inc i inner join DEPT dpt on I.DeptId = dpt.ID Where i.Id = a.IncId for xml path('')), 1, 1, '') from Inc i inner join IncApp a on i.Id = a.IncId inner join IncType t on i.IncType = t.Id inner join IncApp on I.Id = IncApp.IncId inner join App on IncApp.AppId = App.Id where i.INCDATE > cast(convert(varchar(8),getdate()-1,1) as datetime) and I.DeptId = 2439760 and (T.Id = 3372106 or t.Id = 3372107 or t.Id = 3372105 or t.Id = 3372229 or t.Id = 3372231 or t.Id = 3372234 or t.Id = 3404038 or T.Id = 2511070 or T.Id = 2511100 or T.Id = 2511075 or T.Id = 4672982 or T.Id = 2511077 or T.Id = 3372232 or T.Id = 2511092 or T.Id = 2511076 or T.Id = 2511078 or T.Id = 2511079 or T.Id = 3404103 or T.Id = 2511081 or T.Id = 2511082 or T.Id = 2511083 or T.Id = 2511085 or T.Id = 6300413 or T.Id = 3610429 or T.Id = 2511087 or T.Id = 3371591 or T.Id = 2511074) group by i.Id,a.IncId, i.INCDATE, t.IncDesc, i.STRNO order by i.INCDATE DESC