using COALESCE to combine multiple columns into a single column.

I have a very large query that currently contains many subqueries. One of my subqueries returns several rows. I want to take each row and combine it into one column. I also want to grab several columns per row and combine them. So I'm looking for my data to appear in the columns like this. The current query below is what I was going to use if I put the data in its own table, but then I found the command COALESCE and wondered how I could accomplish what I want without creating a new table. ec.OWNER = '' would have an owner from the main query to tie together.

CAR,MODEL,DATE_ACQUIRED,YEARSACQUIRED |CAR,MODEL,DATE_ACQUIRED,YEARSACQUIRED |
CAR,MODEL,DATE_ACQUIRED,YEARSACQUIRED

select ec.CAR, ec.MODEL, ec.DATE_ACQUIRED,
(select DATEDIFF(YEAR,max(DATE_ACQUIRED),GETDATE()) from PROD.dbo.EMPCODES where CAR=ec.CAR AND TYPE='CE' and EMP_APP = 0 and MODEL=ec.MODEL) AS YEARSACQUIRED
from PROD.dbo.EMPCODES ec where TYPE='CE' and EMP_APP = 0 and MODEL in ('TY','FD', 'AU', 'CV')
and ec.OWNER = '')

Open in new window

NickMalloyAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Here is a working example of the STUFF command:
declare @Test TABLE
(
    ID int identity(1,1),
    Field1 varchar(10),
    Field2 varchar(10)
)

insert into @Test (field1, field2)
values ('AAA', 'Bob')

insert into @Test (field1, field2)
values ('AAA', 'John')

insert into @Test (field1, field2)
values ('AAA', 'Frank')

insert into @Test (field1, field2)
values ('BBB', 'Jan')

insert into @Test (field1, field2)
values ('BBB', 'Cat')

insert into @Test (field1, field2)
values ('BBB', 'Dana')

insert into @Test (field1, field2)
values ('CCC', 'London')

insert into @Test (field1, field2)
values ('CCC', 'New York')

insert into @Test (field1, field2)
values ('CCC', 'Paris')

SELECT field1, STUFF((SELECT ',' + Field2 FROM @Test a where a.field1 = b.field1 ORDER BY Field1 FOR XML PATH('')), 1, 1, '')
from @Test b
group by field1

Open in new window

0
 
Shaun KlineLead Software EngineerCommented:
COALESCE is an expanded version of ISNULL. What you describe can be done using the STUFF command with XML, if you are running on a server that is SQL 2005 and greater.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.