PLSQL or SSRS, How to select having all values in a group?

oracle, plsql, reporting-services, sql, ssrs-2008

Solution

The standard approach would be something like

SELECT id, name, value
  FROM table1 a
 WHERE name IN (SELECT name
                  FROM table1 b
                 WHERE b.value in (x,y)
                 GROUP BY name
                HAVING COUNT(distinct value) = 2)

That would require that you determine how many values are in the list so that you can use a 2 in the `HAVING` clause if there are 2 elements, a 5 if there are 5 elements, etc. You could also use analytic functions

SELECT id, name, value
  FROM (SELECT id,
               name,
               value,
               count(distinct value) over (partition by name) cnt
          FROM table1 t1
         WHERE t1.value in (x,y))
 WHERE cnt = 2

Problem

I have a table like this. ``` ID NAME VALUE ______________ 1 A X 2 A Y 3 A Z 4 B X 5 B Y 6 C X 7 C Z 8 D Z 9 E X ``` And the query: ``` SELECT * FROM TABLE1 T WHERE T.VALUE IN (X,Z) ``` This query gives me ``` ID NAME VALUE ______________ 1 A X 3 A Z 4 B X 6 C X 7 C Z 8 D Z 9 E X ``` But i want to see all values of names which have all params. So, only A and C have both X and Z values, and my desired result is: ``` ID NAME VALUE ______________ 1 A X 2 A Y 3 A Z 6 C X 7 C Z ``` How can I get the desired result? No matter with sql or with reporting service. Maybe "GROUP BY ..... HAVING" clause will help, but I'm not sure. By the way I dont know how many params will be in the list. I realy appreciate any help.

Original source