show multiple values in single textbox comma sep

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

Development Tools

Reporting Services Development

show multiple values in single textbox comma sep

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2008-01-02 : 15:52:44

I have a field called "Owners", and it's a child to an "Activities" table.

An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.

I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.

I'm kinda stuck on how to do this.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 09:06:35

I think you can bring the ownerids as a comma seperated string for each activity from db table itself. Refer to link below for the various methods:-

[url]http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx[/url]

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2008-01-04 : 16:30:24

I'm trying to use a UDF (we're on SQL Server 2005), and not getting it 100%.

The structure is Activities to Owners to SAN.

Activities can have 1 to many Owners, and the owners has a number (kina like SSN) linking to SAN which has their name.

USE DB

GO

CREATE FUNCTION dbo.ConcatOwners4(@ActID CHAR(50))

RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN ''
THEN SAN.Authority
ELSE @Output + ', ' + SAN.Authority

END

FROM Activity Left Outer JOIN
Owner ON Activity.ActivityID = Owner.ActivityID Left outer JOIN
SAN ON Owner.OwnerZNumber = SAN.Authority

WHERE Activity.ActivityID = @ActID
ORDER BY Authority
RETURN @Output
END
GO

****************

...and run this:
SELECT DISTINCT ActivityID, ActivityDesc, dbo.ConcatOwners4(ActivityID) AS Expr1
FROM Activity

...I get nothing for the Owners names (Authority)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-05 : 02:47:25

You can do this without UDF. Try:-

SELECT DISTINCT ActivityID, ActivityDesc,LEFT(OwnerList.l,LEN(OwnerList.l)-1)) AS 'Expr1'
FROM Activity a
OUTER APPLY(SELECT SAN.Authority +','as text()
            FROM Owner 
            INNER JOIN SAN 
            ON Owner.OwnerZNumber = SAN.Authority
            WHERE Owner.ActivityID =a.ActivityID
            FOR XML PATH(''))OwnerList(l)

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2008-01-07 : 15:17:26

Hi!

This gives me NULL for EXPR1.

I just tested the query again, and there are names to the activities.

Thanks for the suggestion!

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2008-01-08 : 13:18:21

This did the trick:

CREATE FUNCTION dbo.ConcatOwners4(@ActID CHAR(50))

RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @Output VARCHAR(8000)

SELECT @Output = SUBSTRING(
(SELECT ',' + SAN.Authority AS "text()"
FROM Activity
Left Outer JOIN Owner ON Activity.ActivityID = Owner.ActivityID
Left outer JOIN SAN ON Owner.OwnerZNumber = SAN.Authority
WHERE Activity.ActivityID = @ActID
ORDER BY Authority
FOR XML PATH('')) ,2,8000)

RETURN @Output
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources