1

I have below query where i am trying to achieve using pivot. Is there any way i can define a sub query inside IN clause in pivot. I tried PIVOT XML but i am not getting expected output

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

how can i define a sub query in the IN clause 

is there any possibility for ---FOR product_id IN (select distinct product_id from orders).

I tried PIVOT XML but it was displaying xml code which is not expected output.

Is there any way to achieve ?

Ravi
  • 793
  • 3
  • 16
  • 29
  • 1
    No, you have to use dynamic SQL, or an XML pivot - but then as you've seen to you have to extract the data from the XML somehow. But what will consume the output? If the number of columns in the output aren't known then it's probably only going to be readable by a human, not another process. This sort of thing might be better left to a presentation/reporting layer, rather than being done in the database. – Alex Poole Sep 05 '19 at 18:18
  • @AlexPoole. Thanks Alex for the reply. I am trying not to hard code (10,20,30) because in future some may get newly added. So instead of those those values can i use a select sql ? – Ravi Sep 06 '19 at 02:16
  • 1
    @MTO. I hope this question is not duplicate because it has solution with XML , and i already mentioned in my question that i m not expecting any XML output. – Ravi Sep 06 '19 at 02:19
  • The duplicate shows the only ways you can do this, The accepted answer uses XML but look at the other answers too. You cannot use a query inside the `in()` clause in plain SQL as you were hoping, it has to be done dynamically. – Alex Poole Sep 06 '19 at 07:07

0 Answers0