COALESCE and Distinct - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

COALESCE and Distinct

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-08 : 16:54:43

I am working on a piece of embedded sql, and I need your help on part of it.
I want to use COALESCE to Build Comma-Delimited String, but at the same time the data from the column has duplicate values, and they want to eliminate them at the same time.

So far, I have tried

SELECT COALESCE ( DISTINCT Color ) FROM MyTble
and other combinations, but none of them work.

Thanks in advance for any help!

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-11-08 : 17:04:41

DECLARE @ColorList varchar(1000)

SELECT @ColorList = COALESCE(@ColorList + ', ', '') +
CAST(a.ColorID As varchar(10))
FROM (SELECT DISTINCT Color FROM MyTable) a

Dustin Michaels

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-08 : 17:06:21

Declare @myColors varchar(1000)

Select insull(@myColors+',','')+ convert(varchar,color) From (Select distinct color from myTable) A

EDIT:

Corey

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-08 : 17:10:57

Thanks folks!

Just to make sure, can I use declare variable in embedded sql string?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-08 : 17:15:38

as long as you declare and use it in the dynamic sql

Corey

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-08 : 17:24:54

I still need a little more syntax help.
For the two examples,

the first one got cause on a.colorID part, I changed to a.color, like
DECLARE @ColorList varchar(1000)
SELECT @ColorList = COALESCE(@ColorList + ', ', '')+
CAST(a.color As varchar(10)
FROM (SELECT DISTINCT Color FROM stsJobGangingColors) a

It said err on from and a.

The 2nd one failed on isnull function.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-11-08 : 17:54:25

quote:
Originally posted by Hommer

I still need a little more syntax help.
For the two examples,

the first one got cause on a.colorID part, I changed to a.color, like
DECLARE @ColorList varchar(1000)
SELECT @ColorList = COALESCE(@ColorList + ', ', '')+
CAST(a.color As varchar(10)
FROM (SELECT DISTINCT Color FROM stsJobGangingColors) a

It said err on from and a.

The 2nd one failed on isnull function.

Your missing the ending parenthesis from the cast function.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-09 : 12:36:20

Ok, I think I am getting there, but I'm still not there.

I have both the master select and this sub select with coalesce and distinct succesfully runing at their own in EM.

However, when I inserted the sub into the master, I got syntax errors on "near '=' " in the sub select and near keyword 'from' after the sub select.

I tried to add a or as col Alias at the end of the sub, neither worked.

Here is the simplified version.

DECLARE @ColorList varchar(1000)
SELECT col1, col2,

(SELECT @ColorList = COALESCE(@ColorList + ', ', '')+
CAST(color As varchar(100))
From (SELECT DISTINCT Color FROM stsJobGangingColors join stsJobGanging
on stsJobGangingColors.GangNumber = stsJobGanging.GangNumber )

from
( stsLocMaster INNER JOIN stsJobGanging ON stsLocMaster.LocID = stsJobGanging.CurrentLoc )
LEFT OUTER JOIN stsGangGroup ON stsJobGanging.GroupNumber = stsGangGroup.GroupNumber

WHERE
ORDER BY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources