COALESCE - 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

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2004-03-24 : 14:20:48

I think the best way to solve my problem is to use the COALESCE function, but I'm not sure, so I'll spell out exactly what I'm trying to do here.

I have two tables.

tblSegments
-----------
segmentDirections nvarchar(100)
slotID int

tblSlots
-----------
slotID int
slotDesc nvarchar(1000)

I need to take the directions for each of the segments in a particular slot and concatenate them with a dash or comma in between them, and update the slotDesc for each appropriate slot. Searching around for functions that might help, I saw COALESCE, but I'm not exactly sure how to get it to do what I want.

Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 14:25:48

To concatenate columns, use +:

SELECT Column1 + '-' + Column2
FROM Table1

Tara

Nick
Posting Yak Master

155 Posts

Posted - 2004-03-24 : 14:29:25

quote:
Originally posted by tduggan

To concatenate columns, use +:

SELECT Column1 + '-' + Column2
FROM Table1

Tara

I understand that, but I'm trying to concatenate the same column across multiple records.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 14:30:26

I think you want this...

http://www.sqlteam.com/item.asp?ItemID=2368

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 14:31:37

quote:
Originally posted by tduggan

To concatenate columns, use +:

SELECT Column1 + '-' + Column2
FROM Table1

Tara

You been in the margarita mix already?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 14:31:41

Please provide DDL (CREATE TABLE statements for both tables), DML (INSERT INTO statements for sample data), and expected result set using sample data.

It is easier for us to picture what you want when we can see it. In order to help you out, we need the information in a form that can be copied into Query Analyzer.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 14:34:01

quote:
Originally posted by X002548

quote:
Originally posted by tduggan

To concatenate columns, use +:

SELECT Column1 + '-' + Column2
FROM Table1

Tara

You been in the margarita mix already?

Brett

8-)

It is hard to imagine what a poster wants without seeing data. Concatenation leads me to believe + is needed. Seeing the link that you posted, I now understand what Nick originally wanted. It's just hard to see it without the data.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 14:38:47

quote:
Originally posted by tduggan
It is hard to imagine what a poster wants without seeing data. Concatenation leads me to believe + is needed. Seeing the link that you posted, I now understand what Nick originally wanted. It's just hard to see it without the data.

Tara

No doubt....everytime I see someone post DDL, DML, sample data and expected results, I'm like, damn, that's a GREAT Post..

Still think "directions" should be on the home page and ASK SQL Team page...

Brett

8-)

Nick
Posting Yak Master

155 Posts

Posted - 2004-03-24 : 14:43:46

quote:
Originally posted by X002548

I think you want this...

http://www.sqlteam.com/item.asp?ItemID=2368

Brett

8-)

I also saw that. Let me give you some SQL to create the tables and add some sample data.


CREATE TABLE [tblSegments] (
	[segmentID] [int] IDENTITY (10001, 1) NOT NULL ,
	[slotID] [int] NULL ,
	[segmentDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

CREATE TABLE [tblSlots] (
	[slotID] [int] IDENTITY (10001, 1) NOT NULL ,
	[slotDesc] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) 

INSERT INTO tblSlots (slotDesc) VALUES ('')
INSERT INTO tblSlots (slotDesc) VALUES ('')

INSERT INTO tblSegments (slotID, segmentDirections)
VALUES (10001, 'Leave Burger King')
INSERT INTO tblSegments (slotID, segmentDirections)
VALUES (10001, 'Turn East On State St.')
INSERT INTO tblSegments (slotID, segmentDirections)
VALUES (10001, 'Continue On State St.')
INSERT INTO tblSegments (slotID, segmentDirections)
VALUES (10001, 'Pass The McDonalds')
INSERT INTO tblSegments (slotID, segmentDirections)
VALUES (10002, 'Go Straight Until 4th St.')
INSERT INTO tblSegments (slotID, segmentDirections)
VALUES (10002, 'Go East To Main St.')

Basically what I need is a query that will combine all of the directions for each individual segment in a slot and put that value in slotDesc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 14:45:24

What is the expected result set using your sample data?

Tara

Nick
Posting Yak Master

155 Posts

Posted - 2004-03-24 : 14:49:08

tblSlots

slotID slotDesc
------------------
10001 'Leave Burger King - Turn East On State St. - Continue On State St. - Pass The McDonalds'
10002 'Go Straight Until 4th St. - Go East To Main St.'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 15:02:45

[code]

SET NOCOUNT ON

CREATE TABLE [tblSegments] (
[segmentID] [int] IDENTITY (10001, 1) NOT NULL ,
[slotID] [int] NULL ,
[segmentDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

CREATE TABLE [tblSlots] (
[slotID] [int] IDENTITY (10001, 1) NOT NULL ,
[slotDesc] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
)

INSERT INTO tblSlots (slotDesc) VALUES ('')
INSERT INTO tblSlots (slotDesc) VALUES ('')

INSERT INTO tblSegments (slotID, segmentDesc)
VALUES (10001, 'Leave Burger King')
INSERT INTO tblSegments (slotID, segmentDesc)
VALUES (10001, 'Turn East On State St.')
INSERT INTO tblSegments (slotID, segmentDesc)
VALUES (10001, 'Continue On State St.')
INSERT INTO tblSegments (slotID, segmentDesc)
VALUES (10001, 'Pass The McDonalds')
INSERT INTO tblSegments (slotID, segmentDesc)
VALUES (10002, 'Go Straight Until 4th St.')
INSERT INTO tblSegments (slotID, segmentDesc)
VALUES (10002, 'Go East To Main St.')

DECLARE @slotDesc varchar(100)

SELECT @slotDesc = COALESCE(@slotDesc + ', ', '') +
CAST(segmentDesc AS varchar(7000))
FROM tblSegments
WHERE slotID = 10001

UPDATE tblSlots
SET slotDesc = @slotDesc
WHERE slotID = 10001

SELECT *
FROM tblSlots
WHERE slotID = 10001

DROP TABLE tblSegments
DROP TABLE tblSlots

[/code]

Do you really need a tblSlots table though? Can't you just use the result set like a table:

DECLARE @slotDesc varchar(100)
DECLARE @slotID INT

SET @slotID = 10001

SELECT @slotDesc = COALESCE(@slotDesc + ', ', '') +
CAST(segmentDesc AS varchar(7000))
FROM tblSegments
WHERE slotID = @slotID

SELECT @slotID, @slotDesc

Tara

Nick
Posting Yak Master

155 Posts

Posted - 2004-03-24 : 15:07:09

Well I'd really like to update the slots table for every record. Something like 40,000 slots made up of about 100,000 segments.

I'm trying to avoid using cursors for it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-24 : 15:13:47

Tara's almost got it.

just wrap her code in a UDF and off you go:


CREATE FUNCTION Directions(@SlotID int)
  RETURNS varchar(8000)
AS
  BEGIN
  DECLARE @slotDesc varchar(8000)
  SELECT @slotDesc = COALESCE(@slotDesc + ', ', '') + 
    CAST(segmentDesc AS varchar(7000))
  FROM tblSegments 
  WHERE slotID = @slotID
  ORDER BY SegmentID

  RETURN (@SlotDesc)
END

and then you just say


select SlotID, SlotDesc, dbo.Directions(SlotID) as Directions
from tblSlots

Simple as that ! Watch out for the 8000 character limit, though ....

There might be a syntax error in there but that's how you can do it ... thanks to Byrmol for the UDF idea ...

- Jeff

Nick
Posting Yak Master

155 Posts

Posted - 2004-03-24 : 15:17:35

Ingenius. Never would have thought of the UDF. Thanks everyone for the help.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 15:20:30

quote:
Originally posted by Nick

Ingenius.

Not to be confused with inbread...

That's Jeff and Tara for you....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources