[RESOLVED] COALESCE script - 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

General SQL Server Forums

New to SQL Server Programming

[RESOLVED] COALESCE script

Author

Topic

obsoletedude
Starting Member

31 Posts

Posted - 2007-08-13 : 13:47:37

I have a table NUMBERS in which I have Column1 and Column2. Column1 will always be a unique number, yet Column2 may have duplicates.

What I would like to do is return all the data in Columns 1 and 2, yet have Column1 return as a comma seperated value.

Column1 Column2
1 15
2 16
3 16
4 17

Return:
Column1 Column2
1 15
2,3 16
4 17

Any ideas will be great!

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 13:54:33

(1) Write a function that takes in value in column1 and returns a comma separated list of corresponding column2 values. (If you cant, search in these forums )
(2) SELECT Min(column1) , dbo.fnGetCol2Values (column1)
FROM YourTables
Group by Column2

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:01:30

check this link out to create that function

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Ashley Rhodes

obsoletedude
Starting Member

31 Posts

Posted - 2007-08-13 : 15:19:39

Here's my function:

CREATE FUNCTION dbo.GetReferenceID
(
@FK_ReferenceID VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @r VARCHAR(8000)
SELECT @r = ISNULL(@r+',', '')
+ FK_ReferenceID
FROM dbo.integervalues
WHERE FK_ReferenceID = @FK_ReferenceID
RETURN @r
END
GO

SELECT
FK_ReferenceID,
dbo.getReferenceID(FK_ReferenceID)
FROM
(
SELECT
FK_ReferenceID
FROM
integervalues
) i
GO

drop function getreferenceid

Here's my error:
Conversion failed when converting the varchar value '1,' to data type int.

Sorry, but I am kinda new at this and learning as I go. Thanks for the help.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 15:23:45

can you post your table names and column names which you used to create the function along with data types

Ashley Rhodes

obsoletedude
Starting Member

31 Posts

Posted - 2007-08-13 : 15:29:12

the table name is Integervalues

Column 1 is FK_ReferenceID (which will have unique values)
Column 2 is Value which may have duplicates

What I'm trying to do is get Column 1 to be comma seperated to show all the FK_ReferenceID's that have the same Value. And also see the Value column.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 15:40:58

in your select

SELECT
Column2 ------FK_ReferenceID,
dbo.getReferenceID(FK_ReferenceID)
FROM
(
SELECT
FK_ReferenceID
FROM
integervalues
) i
GO

should u not use column2 instead of FK_ReferenceID as column2 is the one which has duplicate values

Ashley Rhodes

obsoletedude
Starting Member

31 Posts

Posted - 2007-08-13 : 16:07:08

Hi Ashley,

still getting that Conversion failed when converting the varchar value '1,' to data type int. Shouldn't it be converting to a varchar?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 16:10:31

This

quote:
Originally posted by obsoletedude
SELECT @r = ISNULL(@r+',', '')
+ FK_ReferenceID
FROM dbo.integervalues
WHERE FK_ReferenceID = @FK_ReferenceID

would be


SELECT @r = ISNULL(@r+',', '') 
+ convert(Varchar,FK_ReferenceID )
FROM dbo.integervalues 
WHERE FK_ReferenceID = @FK_ReferenceID

Notice the bold part.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 16:12:18

do something like

select convert(varchar(10), dbo.GetReferenceID(FK_ReferenceID)) from integervalues

I don't get that error.

Ashley Rhodes

obsoletedude
Starting Member

31 Posts

Posted - 2007-08-13 : 16:53:32

OK, first, let me just say, you guys are awesome. I am amazed at this and love trying to learn it.

So, onto my problem. Everything seems to be going great, except, well, I'll paste some of my results below and maybe someone can tell me where my problem lies.

232.000000 1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216,1216
232.000000 1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217,1217
232.000000 1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218,1218
232.000000 19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114,19114
232.000000 19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115,19115
232.000000 19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116,19116

the 232.000000 is the Value and the other numbers are the referenceID. But instead of this, I was hoping to get something more like :

232.000000 1216,1217,1218,19114,19115,19116

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 17:36:43

[code]
Create FUNCTION dbo.GetReferenceID ( @FK_ReferenceID VARCHAR(32) )
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @r VARCHAR(8000)
SELECT @r = ISNULL(@r+',', '')
+ convert(Varchar,Column1 )
FROM dbo.integervalues
WHERE Column2 = @FK_ReferenceID
RETURN @r
END
GO

create table dbo.integervalues (Column1 int, Column2 int)
insert into integervalues
select 1, 15 union all
select 2, 16 union all
select 3, 16 union all
select 4, 17

Select dbo.GetReferenceID(c2) , c2
from (
select min(column1) c1, column2 c2
from integervalues
group by column2) a

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-14 : 01:41:16

Also, if you want to show the concatenated data in the front end application, you can do it there simply by using while loop

Madhivanan

Failing to plan is Planning to fail

obsoletedude
Starting Member

31 Posts

Posted - 2007-08-14 : 11:04:52

dinakar,

Not sure what your last post meant. I already have the integervalues table, so I cannot create a new one.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 12:24:30

quote:
Originally posted by obsoletedude

dinakar,

Not sure what your last post meant. I already have the integervalues table, so I cannot create a new one.

ITs a complete script for creating tables, function, and the query that shows the data you want with the data you provided in your first post.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources