NickMalloy
asked on
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,YE ARSACQUIRE D |CAR,MODEL,DATE_ACQUIRED,Y EARSACQUIR ED |
CAR,MODEL,DATE_ACQUIRED,YE ARSACQUIRE D
CAR,MODEL,DATE_ACQUIRED,YE
CAR,MODEL,DATE_ACQUIRED,YE
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 = '')
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.