Using COALESCE/CSV in a Select? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Using COALESCE/CSV in a Select?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-01-30 : 19:20:36

Hello all,

I'm trying to use the technique found in this article Using COALESCE to Build Comma-Delimited String in a SELECT statement and was wondering????

1. Is this possible

And

2. If it si, how do I do it.

Here is the code that I have so far. If you need more explaination on the data or tables let me know.

SET NOCOUNT ON

-- Create a temp table hold the results
-- for later paging
CREATE TABLE #TempSearch (
AID int NOT NULL PRIMARY KEY ,
FName nvarchar(25) NOT NULL ,
LName nvarchar(25) NOT NULL ,
Email nvarchar(75) NOT NULL ,
CompanyName nvarchar(75) NOT NULL ,
City nvarchar(50) NOT NULL ,
StateName varchar(25) NOT NULL ,
StateAbbrev varchar(2) NOT NULL ,
Country varchar(50) NOT NULL ,
ZipCode nvarchar(20) NOT NULL ,
AIM nvarchar(75) NOT NULL ,
Yahoo nvarchar(75) NOT NULL ,
ICQ nvarchar(75) NOT NULL ,
Description nvarchar(255) NOT NULL ,
Credits nvarchar(255) NOT NULL ,
SortOrder nvarchar(125) NOT NULL ,
Categories varchar(1000) NOT NULL DEFAULT (''),
CatIDs varchar(1000) NOT NULL DEFAULT (''),
SearchRank int NOT NULL DEFAULT (0)
)

DECLARE @CategoryList varchar(1000)

INSERT INTO #TempSearch ( AID, FName, LName, Email, CompanyName, City, StateName, StateAbbrev, Country, ZipCode, AIM, Yahoo, ICQ, Description, Credits, SortOrder )

SELECT a.ID AID, a.FName FName, a.LName LName, a.Email1 Email, a.CompanyName CompanyName, a.City City, s.StateName StateName, s.Abbrv StateAbbrev, c.CountryName Country, a.ZipCode ZipCode, a.AIM AIM, a.Yahoo Yahoo, a.ICQ ICQ, a.Description Description, a.Credits Credits, LTRIM((a.CompanyName + ' ' + a.LName + ' ' + a.FName)) SortOrder

-- ## Here is the coalesce code I'm trying to insert
-- ## into this select. It is supposed to generate a list
-- ## of categories that each person is listed under
-- ## into the Categories column of the #TempSearch Table.
( SELECT @CategoryList = COALESCE(@CategoryList + ', ', '') + cat.CatNAme
FROM IPG_LISTINGS l
JOIN IPG_CATEGORIES cat ON cat.CatID = l.LCatID
WHERE a.ID = l.LAccountID
SELECT @CategoryList ) Categories

FROM IPG_ACCOUNTS a
JOIN IPG_STATES s ON s.STID = a.STID
JOIN IPG_COUNTRIES c ON c.CTID = a.CTID
WHERE a.Active = 1
AND a.SystemBlock = 0

SELECT * FROM #TempSearch

DROP TABLE #TempSearch

SET NOCOUNT OFF

Any help would be appreciated.

Thanks,

JB

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-30 : 19:45:37

It only works with a string variable.
You can probably do this by creating a udf to build the entry in a string variable and return it for the select.
udf's are very slow though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-01-30 : 19:53:51

Hi Nigel,

Does this mean it can't be doen in a select.

I'm trying to take multiple rows, turn them into a csv and use that values in a select.

Can you do a udf in SQL 7. That's what I'm working with.

I trying to avoid a cursor with an update if I can.

Thanks,

JB

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-30 : 20:01:52

Is this what you're looking for?
http://www.sqlteam.com/item.asp?ItemID=11021

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-01-30 : 20:28:38

Hi Valter,

I'm going to play with that for a while and see if it is what I need. I will let you know what happens.

JB

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-31 : 07:34:38

quote:

udf's are very slow though.

Not sure about UDF's being slow in this case.

Take a look at byromol's function, and Jay's comments... the UDF technique is actually the easiest to use, and also the most efficient according to Jay's testing. And, of course, it is easily used in any SELECT statment.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-01-31 : 09:21:13

I'm not sure if I'm missing the Point, but can't you write something like:

SELECT '"'+strCol1+'"'
+Convert(Varchar(25),intCol2)
+',"'+Convert(Varchar(25),dteCol3,121)+'"'
ect
FROM TABLE

This uses quoted identifiers for Character and Date Fields and non identified for numerics...

Hope this is what you're looking for

Happy Hunting

Brett

8-)

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-01-31 : 14:19:24

Hi All,

It was getting late last night but to simply things, this is briefly what I want to do with one SELECT Statement.

CREATE TABLE #Names (
NameID int IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
Names varchar(25) NOT NULL ,
)
Go

INSERT INTO #Names (Names)
VALUES ('Joe Smith')
INSERT INTO #Names (Names)
VALUES ('Jane Doe')
INSERT INTO #Names (Names)
VALUES ('Bart Simpson')
Go

CREATE TABLE #Categories (
CatID int IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
NameID int NOT NULL ,
CategoryName varchar(50) NOT NULL
)
Go

INSERT INTO #Categories (NameID, CategoryName)
VALUES (1, 'Big Trucks')
INSERT INTO #Categories (NameID, CategoryName)
VALUES (1, 'Medium Trucks')
INSERT INTO #Categories (NameID, CategoryName)
VALUES (1, 'Small Trucks')
Go

INSERT INTO #Categories (NameID, CategoryName)
VALUES (2, 'Big Trucks')
Go

INSERT INTO #Categories (NameID, CategoryName)
VALUES (3, 'Medium Trucks')
INSERT INTO #Categories (NameID, CategoryName)
VALUES (3, 'Small Trucks')
Go

SELECT n.Names --??? CSV of #categories for n.Names??? As Categories
FROM #Names n

DROP TABLE #Names
DROP TABLE #Categories

I would like my results to look like this.

Names Categories
---------------------------
Joe Smith Big Trucks, Medium Trucks, Small Trucks
Jane Doe Big Trucks
Bart Simpson Medium Trucks, Small Trucks

Is there away to do this without using multiple temp tables an then updating multiple times.

JB

We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump.

Edited by - JBelthoff on 01/31/2003 14:57:29

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-31 : 15:20:18

Again, take a look at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

Byrmol's solution is exactly what you are looking for.

- Jeff

JBelthoff
Posting Yak Master

173 Posts

Posted - 2003-01-31 : 18:46:45

I'm sure that would be the way to go if I was was on a SQL 2000 system. I only have SQL7

JB

-- We know a man called Mr. Gump. Mr. Gump has a seven hump Wump. So... if you like to go Bump! Bump! just jump on the hump of the Wump of Gump. --

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources