How to query using derived table and multi CTEs...

Mike Eghtebas
Mike Eghtebas used Ask the Experts™
on
Having temp table #t contain:
Product  ProdType
A            x
B            x
B            xx
C            x
D            x
D            xx
E            x

Question: Using derived table and also CTEs (2nd solution), How to produce result like:
ProdType  ProdType
B            x
B            xx
D            x
D            xx

These solutions basically ignore products with single ProdType?

T-SQL to build #t:
create table #t(Product char(1), ProdType varchar(2));
Insert into #t(Product, ProdType) Values
('A', 'x')
, ('B', 'x')
, ('B', 'xx')
, ('C', 'x')
, ('D', 'x')
, ('D', 'xx')
, ('E', 'x');

Select * From #t;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
Derived table solution:
Select Product, ProdType
from (
  select *, count(*) over (partition by Product) cnt from #t
) d where cnt > 1;

Solution using multiple CTEs:
With d
as
(
Select row_number()  over(partition by Product order by ProdType) As rn
, Product
From #t),
e
as
(
Select #t.Product, ProdType, d.rn
From #t inner join d on d.Product = #t.Product
Where d.rn >=2)
Select e.Product, e.ProdType
From e;
Mike EghtebasDatabase and Application Developer

Author

Commented:
Note to experts, This question was added to be referenced by an article to build temp table #t.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial