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)
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
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:
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.
answered Jun 04 '10 at 07:12 PM
answered Jun 04 '10 at 06:03 PM