Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

using COALESCE to combine multiple columns into a single column.

Posted on 2014-01-09
2
Medium Priority
?
337 Views
Last Modified: 2014-01-09
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

0
Comment
Question by:NickMalloy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39768605
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
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 39768611
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question