Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get summary of column values from a result set Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 1:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
good morning everyone,

I have a procedure taking some input parameters that are used for a select statement. After returning the select statement I need the distinct values of 2 columns as comma seperated list.
The only solution seems to be that I do the select 3 times: once for the result and once for each column I want the distinct values for.
This summary is needed by the reporting tool calling the procedure for showing it to the user.
Is there a better solution? Here's the (very simplified) example:

-- This is my base table
declare @CustomerOffices table (
OfficeId int,
CustomerName varchar(100),
PostalCode char(4)
);

insert into @CustomerOffices ( OfficeId, CustomerName, PostalCode ) values
( 1, 'Cust1', '6020' )
,( 3, 'Cust5', '8010' )
,( 4, 'Cust5', '8010' )
,( 5, 'Cust3', '6020' )
,( 6, 'Cust3', '5020' )
,( 7, 'Cust2', '6020' )
,( 8, 'Cust2', '1030' )


-- this is part of an sql procedure, taking the OfficeId as input
declare @FromOfficeId int,
@ToOfficeId int;

-- We want to have OfficeId 4 to 7
set @FromOfficeId = 4;
set @ToOfficeId = 7;

-- This is our result
select OfficeId, CustomerName, PostalCode
from @CustomerOffices
where OfficeId between @FromOfficeId and @ToOfficeId;


Now I want to have all distinct CustomerNames and all distinct PostalCodes of the result above.
declare @SummaryCustomerNumbers varchar(max) = '',
@SummaryPostalCodes varchar(max) = '';

select @SummaryCustomerNumbers += s.CustomerName + ','
from (
select distinct CustomerName
from @CustomerOffices
where OfficeId between @FromOfficeId and @ToOfficeId
) as s
order by s.CustomerName;

select @SummaryPostalCodes += s.PostalCode + ','
from (
select distinct PostalCode
from @CustomerOffices
where OfficeId between @FromOfficeId and @ToOfficeId
) as s
order by s.PostalCode

-- Remove last komma
set @SummaryCustomerNumbers = left( @SummaryCustomerNumbers, len( @SummaryCustomerNumbers ) - 1 )
set @SummaryPostalCodes = left( @SummaryPostalCodes, len( @SummaryPostalCodes ) - 1 )

select 'Summary' as Summary,
@SummaryCustomerNumbers as CustomerNumbers,
@SummaryPostalCodes as PostalCodes;


This is a quite complicated way and, as I said, I have to do the same select 3 times. As the original select is done on a large table the procedure needs quite a lot time to do this.
I already tried to use the compute clause but it only works for aggregate functions.

For getting the whole thing faster I could save the original result in a temp table and get the distinct values from the temp table. But that's not very elegant, isn't it?
Thank's for any help.
Post #1437722
Posted Tuesday, April 2, 2013 2:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:38 AM
Points: 1,678, Visits: 19,553
Good job of posting sample data + results. Thanks.


SELECT 'Summary' as Summary,
STUFF((SELECT ',' + CustomerName AS "text()"
FROM @CustomerOffices
WHERE OfficeId BETWEEN @FromOfficeId and @ToOfficeId
GROUP BY CustomerName
ORDER BY CustomerName
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS CustomerNumbers,
STUFF((SELECT ',' + PostalCode AS "text()"
FROM @CustomerOffices
WHERE OfficeId BETWEEN @FromOfficeId and @ToOfficeId
GROUP BY PostalCode
ORDER BY PostalCode
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS PostalCodes;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1437738
Posted Tuesday, April 2, 2013 2:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
Thank's for your reply.
It looks much more compact than the way I did. But it won't be much faster as the where-conditition has still to be executed twice on the @Customers-table. I hoped to get rid off it.
Post #1437752
Posted Tuesday, April 2, 2013 3:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:38 AM
Points: 1,678, Visits: 19,553
You can do this instead, although I don't think it will make much difference

WITH Results AS (
SELECT CustomerName, PostalCode
FROM @CustomerOffices
WHERE OfficeId BETWEEN @FromOfficeId AND @ToOfficeId)
SELECT 'Summary' as Summary,
STUFF((SELECT ',' + CustomerName AS "text()"
FROM Results
GROUP BY CustomerName
ORDER BY CustomerName
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS CustomerNumbers,
STUFF((SELECT ',' + PostalCode AS "text()"
FROM Results
GROUP BY PostalCode
ORDER BY PostalCode
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS PostalCodes;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1437762
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse