regarding the results of query

hi all,
i have a table matter which has following fields:
MatterName
MatterNumber
BuisnessArea

--
the primary key is MatterNumber which is unique, MatterName can
repeat
number of times and BuisnessArea too.


I have a requirement to select unique MatterName always, if a
MatterName has more than one MatterNumber associated with it, then
display blank for MatterNumber and Buisness area else display the
unique values


how can i possibly achieve this?


--
Eg:
MatterName MatterNumber BuisnessArea
Ekta 1 Marketing
Diya 3 Finance
Ekta 2 Finance
Diya 4 Finance
Diya 5 Marketing
Lina 8 Marketing


Should display the following:
Ekta '' ''
Diya '' ''
Lina 8 Marketing


(i.e. unique MatterName always with blank BuisnessArea and blank
MatterNumber if more than one matternumber is present for a
particular
MatterName)


Is it possible to achieve the above result with single sql
statement ?

On 8 Sep, 16:58, Ekta wrote:
> hi all,
> i have a table matter which has following fields:
> MatterName
> MatterNumber
> BuisnessArea
>
> --
> the primary key is MatterNumber which is unique, MatterName can
> repeat
> number of times and BuisnessArea too.
>
> I have a requirement to select unique MatterName always, if a
> MatterName has more than one MatterNumber associated with it, then
> display blank for MatterNumber and Buisness area else display the
> unique values
>
> how can i possibly achieve this?
>
> --
> Eg:
> MatterName MatterNumber BuisnessArea
> Ekta 1 Marketing
> Diya 3 Finance
> Ekta 2 Finance
> Diya 4 Finance
> Diya 5 Marketing
> Lina 8 Marketing
>
> Should display the following:
> Ekta '' ''
> Diya '' ''
> Lina 8 Marketing
>
> *(i.e. unique MatterName always with blank BuisnessArea and blank
> MatterNumber if more than one matternumber is present for a
> particular
> MatterName)
>
> Is it possible to achieve the above result with single sql
> statement ?

I can think of a number of ways. Here is one sketch:

with T (MatterName, MatterNumber, BuisnessArea) as (
values
('Ekta', 1 ,'Marketing'),
('Diya', 3 ,'Finance'),
('Ekta', 2 ,'Finance'),
('Diya', 4 ,'Finance'),
('Diya', 5 ,'Marketing'),
('Lina', 8 ,'Marketing')
), U as (
select MatterName, MatterNumber, BuisnessArea,
rownumber() over (partition by MatterName
order by MatterNumber, BuisnessArea) as rn
from T
) select MatterName,
case when rn = 1 then char(MatterNumber) else '' end as
MatterNumber,
case when rn = 1 then BuisnessArea else '' end as
BuisnessArea
from U U1
where rn = (select max(rn) from U U2
where U1.MatterName = U2.MatterName);

MATTERNAME MATTERNUMBER BUISNESSAREA
---------- ------------ ------------
Diya
Ekta
Lina 8 Marketing


/Lennart

Another query:
WITH T (MatterName, MatterNumber, BuisnessArea) AS (
VALUES
('Ekta', 1 , 'Marketing'),
('Diya', 3 , 'Finance'),
('Ekta', 2 , 'Finance'),
('Diya', 4 , 'Finance'),
('Diya', 5 , 'Marketing'),
('Lina', 8 , 'Marketing')
)
SELECT MatterName
, CASE MIN(MatterNumber)
WHEN MAX(MatterNumber) THEN
CHAR( MIN(MatterNumber) )
ELSE ''
END AS MatterNumber
, CASE MIN(BuisnessArea)
WHEN MAX(BuisnessArea) THEN
MIN(BuisnessArea)
ELSE ''
END AS BuisnessArea
FROM t
GROUP BY
MatterName
;