Get summary of column values from a result set
-
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 tabledeclare @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.
-
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
-
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.
-
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply