create conditional view table

[r***@gmail.com]

I have an SQL table qith fields QTY1, QTY2, QTY3.
For crosstab records I will like to create a VIEW table with the fields:
CALL_ID, QTY if QTY1>0
CALL_ID, QTY if QTY2>0
CALL_ID, QTY if QTY3>0

in other words the view may have only one record or if may have 3 depending on the fields QTY1, 2, 3 being populated.

Is this possible and if so how?


I love the concept of a VIEW table because is done at the backend and involves no programming.

Thanks,
Raul Rego
***@gmail.com

[Erland Sommarskog]

Post by r***@gmail.com
I have an SQL table qith fields QTY1, QTY2, QTY3.
CALL_ID, QTY if QTY1>0
CALL_ID, QTY if QTY2>0
CALL_ID, QTY if QTY3>0
in other words the view may have only one record or if may have 3
depending on the fields QTY1, 2, 3 being populated.
Is this possible and if so how?
I love the concept of a VIEW table because is done at the backend and
involves no programming.

Sounds likely you are into unpivoting:

SELECT Call_ID,
CASE n WHEN 1 THEN QTY1 WHEN 2 THEN QTY2 WHEN 3 QTY 3 END AS qty
FROM tbl
CROSS APPLY (VALUES(1), (2), (3)) AS n(n)
WHERE qty > 0

[r***@gmail.com]

Post by Erland Sommarskog

Post by r***@gmail.com
I have an SQL table qith fields QTY1, QTY2, QTY3.
CALL_ID, QTY if QTY1>0
CALL_ID, QTY if QTY2>0
CALL_ID, QTY if QTY3>0
in other words the view may have only one record or if may have 3
depending on the fields QTY1, 2, 3 being populated.
Is this possible and if so how?
I love the concept of a VIEW table because is done at the backend and
involves no programming.

SELECT Call_ID,
CASE n WHEN 1 THEN QTY1 WHEN 2 THEN QTY2 WHEN 3 QTY 3 END AS qty
FROM tbl
CROSS APPLY (VALUES(1), (2), (3)) AS n(n)
WHERE qty > 0
--

I am syntax lost. can you please explain the syntax of the CASE statement and also the APPLY.

many thanks in advance,
Raul

[Erland Sommarskog]

Post by r***@gmail.com

Post by Erland Sommarskog
SELECT Call_ID,
CASE n WHEN 1 THEN QTY1 WHEN 2 THEN QTY2 WHEN 3 QTY 3 END AS qty
FROM tbl
CROSS APPLY (VALUES(1), (2), (3)) AS n(n)
WHERE qty > 0

I am syntax lost. can you please explain the syntax of the CASE
statement and also the APPLY.

The APPLY operator is like a JOIN, but the right side can refer the
left side. This permits you call a table-valued function with parameters
from some other table. Here I only use the CROSS APPLY to generate three
rows with the values 1, 2 and 3. Thus for each Call_ID there is

Call_ID QTY1 QTY2 QTY3 1
Call_ID QTY1 QTY2 QTY3 2
Call_ID QTY1 QTY2 QTY3 3

The CASE then picks one quantity at a time.