Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get summary of column values from a result set


Get summary of column values from a result set

Author
Message
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
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.
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2091 Visits: 22787
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;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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




WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
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.
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2091 Visits: 22787
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;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search