Coallesce Opportunity - Report Generation

Question

question

Dave Myers asked Jun 4, '10

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

t-sql query reporting concatenation

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-04T19:12:10Z

TimothyAWiseman answered Jun 4, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-04T18:03:33Z

Fatherjack answered Jun 4, '10

try this:

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question