Discussion:
create conditional view table
(too old to reply)
r***@gmail.com
2014-04-02 19:01:16 UTC
Permalink
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
2014-04-02 21:21:54 UTC
Permalink
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
--
Erland Sommarskog, Stockholm, ***@sommarskog.se
r***@gmail.com
2014-04-04 10:39:13 UTC
Permalink
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
2014-04-04 19:09:09 UTC
Permalink
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.
--
Erland Sommarskog, Stockholm, ***@sommarskog.se
Loading...