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) 
@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

avatar image

Dave Myers
123 15 15 18

(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

avatar image

15.6k 22 55 38

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

try this:

USE AdventureWorks
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

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jun 04, 2010 at 05:28 PM

Seen: 1433 times

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

Copyright 2018 Redgate Software. Privacy Policy