Trouble Combining Rows to One Field

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

Trouble Combining Rows to One Field

Author

Topic

conedm
Starting Member

33 Posts

Posted - 2010-03-18 : 12:03:21

To start off I am a .NET programmer still learning SQL Server 2005
I have a function that does the work for me...
CREATE FUNCTION [DBO].[INDSOFTVERSONSAMELINE](@Id decimal,@Delimiter varchar(10))
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @OnSameLine varchar(max)
Set @OnSameLine=''
SELECT @OnSameLine = @OnSameLine + coalesce (CSRESPROF.DBO.INDICATORS.SOFT_GUI,'')+@Delimiter
FROM CSRESPROF.DBO.INDICATORS
WHERE (CSRESPROF.DBO.INDICATORS.LOC = @Id)
set @OnSameLine = substring(@OnSameLine,1,len(@OnSameLine)-1)
RETURN @OnSameLine
END

...But the prolem is displaying the results.
I have a table with location information by SITE number
TABLE (SITES)
SITE
1
2
3
4 etc.
I have a table of RESOURCES
TABLE (RESOURCES)
ID
1
2
3 etc.
the column I need from RESOURCES is SOFT_GUI
my select statement that calls the function lists all the sites but it adds the resources for each sites as a seperate row
currently I only have 3 used resources in the RESOURCES table
my result set shows all three resources for every site.
here is my SQL statement...
SELECT DISTINCT Sites.SITE, dbo.INDSOFTVERSONSAMELINE(CSRESPROF.dbo.INDICATORS.LOC, ', ') AS SOFT_VERS
FROM Sites LEFT OUTER JOIN
CSRESPROF.dbo.INDICATORS ON CSRESPROF.dbo.INDICATORS.LOC IS NOT NULL

it returns...
1 3.8,3.9
1 3.7
2 3.8,3.9
2 3.7
3 3.8,3.9
3 3.7
etc.
...I need all sites listed regardless if they have a resource assigned to it and I need the correct resource listed with the correct site.
Can someone please help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:08:03

why you dont have any join condition specified for left join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

conedm
Starting Member

33 Posts

Posted - 2010-03-18 : 12:16:13

Thank you for replying
Because I get an error that says "Invalid length paramete passed to the substring"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:17:21

what? show your original query plzz

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

conedm
Starting Member

33 Posts

Posted - 2010-03-18 : 12:20:18

SELECT DISTINCT Sites.SITE, dbo.INDSOFTVERSONSAMELINE(CSRESPROF.dbo.INDICATORS.LOC, ', ') AS SOFT_VERS
FROM Sites LEFT OUTER JOIN
CSRESPROF.dbo.INDICATORS ON CSRESPROF.dbo.INDICATORS.LOC = Sites.SITE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:22:46

where are you using SUBSTRINg here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

conedm
Starting Member

33 Posts

Posted - 2010-03-18 : 12:27:25

I'm Not that I can tell. The function is the only thing that does and I get the error even if I eliminate any substring

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:31:44

what does INDSOFTVERSONSAMELINE function do? are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

conedm
Starting Member

33 Posts

Posted - 2010-03-18 : 12:36:27

Yes I am using 2005
It takes the site number, SITES.SITE, (that was passed to it) and concatenates all the RESOURCES.SOFT_GUI fields seperated by a comma, that are assigned to that site

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:41:51

[code]
SELECT DISTINCT Sites.SITE, STUFF((SELECT ','+RESOURCES.SOFT_GUI FROM RESOURCES WHERE related column=CSRESPROF.dbo.INDICATORS.LOC FOR XML PATH('')),1,1,'') AS SOFT_VERS
FROM Sites
LEFT OUTER JOIN
CSRESPROF.dbo.INDICATORS
ON CSRESPROF.dbo.INDICATORS.LOC = Sites.SITE
[/code]

related column is column by which RESOURCES is related to CSRESPROF.dbo.INDICATORS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

conedm
Starting Member

33 Posts

Posted - 2010-03-18 : 12:51:33

That did it!!! Thank you so much!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:54:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources