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

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

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
Mike EghtebasConnect With a Mentor Database and Application DeveloperAuthor 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
Select row_number()  over(partition by Product order by ProdType) As rn
, Product
From #t),
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 DeveloperAuthor Commented:
Note to experts, This question was added to be referenced by an article to build temp table #t.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.