x

Coallesce Opportunity - Report Generation

We are trying to put together a comma delimited list for a report that lists our Managers and their respective stores (e.g. Jim 3,4,5,6,33,22,56)

The manager is of type varchar (50) and the store_number is of type smallint

since we need to put the store_number into a string we cast the store_number as a varchar

The below query puts the store numbers in a row but duplicates numbers and I need to incorporate the managers name in there (RVP_Name)

DECLARE @storenum varchar(max) 
Select 
@storenum = Coalesce(@storenum+',','') + cast(store_number as varchar(max)) 
From vw_territory t 
select @storenum 

Resulting in: 4,4,1,1,1,1,1,7,7,8,8,11,11,4,4,4,4,4,9,9,6,6,6,6,6,13,13,13,14.........................

Any assistance with this would be helpful

more ▼

asked Jun 04, 2010 at 05:28 PM in Default

Dave Myers gravatar image

Dave Myers
123 15 15 16

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I would break it up into two steps. First, I would get the managers and their stores together, then I would concatenate that together.

For a single select statement it would look like:

;with cte1 as (
    select
        RVP_name + 
        stuff((select ', ' + cast(store_number as varchar)
                from vw_territory t2
                where t2.RVP_name = t1.RVP_name 
                for xml path('')),1,1,'') as storenums
    from vw_territory t1
    group by RVP_name
        )

select
    stuff((select ', ' + storenums
            from cte1
            for xml path('')),1,1,'') as storenums

Now, as to the numbers being duplicated, this indicates that those numbers are being duplicated in your view. If they should not be (and they would not be if the relationship between managers and stores was 1-1), then you may want to examine that view and see if perhaps you have a join that is joining more than it should or if perhaps there are redundant entries in one of the base tables.

Of course, if you just want to get rid of the duplicates in the list, you can use "distinct" in the inner select in the CTE.

Edit: As a general reference you may want to read over this article: http://www.sqlservercentral.com/articles/Test+Data/61572/ from Jeff Moden about concatenation.

more ▼

answered Jun 04, 2010 at 07:12 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

try this:

USE AdventureWorks GO DECLARE @custno VARCHAR(max) SET @custno = ',' SELECT @custno = CAST(CustomerID AS VARCHAR(100))+ ','+ @custno FROM Sales.Store PRINT @custno 
more ▼

answered Jun 04, 2010 at 06:03 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x369
x94
x20

asked: Jun 04, 2010 at 05:28 PM

Seen: 1280 times

Last Updated: Sep 23, 2010 at 03:47 AM