| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
06-16-10, 23:02
|
|
Registered User
|
|
Join Date: Jun 2010
Posts: 1
|
|
query required
|
|
i need a query which processes input like
KEY ATTRIBUTE1 ATTRIBUTE2
K1 A11
K1 A12
K1 A21
K1 A22
K1 A23
and the output should be like
Output
KEY ATTRIBUTE1 ATTRIBUTE2
K1 A11 A21
K1 A12 A22
K1 A23
Some more details on the Data:
a) At least one of the attributes would always be present.
b) Any combination and number of occurrences of the attributes (Attribute1 and Attribute2) is possible.
c) There would be multiple values in the Key column (i.e. K1, K2, etc).
d) There is no correlation between the values of Attribute1 and Attribute2.
e) We need to bring all non-space and non-null values for every column in ascending order.
f) Though primary audience is DB2, we can use any other RDBMS.
|
Last edited by siddhartha_tiwari; 06-16-10 at 23:07.
|
06-17-10, 03:51
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Although syntax of VALUES clause used in the common table expression(input) may be valid only on DB2 for LUW,
it will be easy to modify it to conform with your DBMS.
Code:
------------------------------ Commands Entered ------------------------------
WITH
input(key , attribute1 , attribute2) AS (
VALUES
('K1' , 'A11' , '')
, ('K1' , 'A12' , '')
, ('K1' , '' , 'A21')
, ('K1' , CAST(NULL AS VARCHAR(1)) , 'A22')
, ('K1' , '' , 'A23')
, ('K2' , 'A21' , '')
, ('K3' , '' , 'A31')
, ('K3' , '' , 'A32')
)
SELECT COALESCE(s1.key , s2.key) AS key
, attribute1
, attribute2
FROM (SELECT key , attribute1
, ROW_NUMBER()
OVER(PARTITION BY key
ORDER BY attribute1
) rn
FROM input
WHERE attribute1 IS NOT NULL
AND attribute1 <> ''
) AS s1
FULL OUTER JOIN
(SELECT key , attribute2
, ROW_NUMBER()
OVER(PARTITION BY key
ORDER BY attribute2
) rn
FROM input
WHERE attribute2 IS NOT NULL
AND attribute2 <> ''
) AS s2
ON s2.key = s1.key
AND s2.rn = s1.rn
ORDER BY
key
, COALESCE(s1.rn , s2.rn)
;
------------------------------------------------------------------------------
KEY ATTRIBUTE1 ATTRIBUTE2
--- ---------- ----------
K1 A11 A21
K1 A12 A22
K1 - A23
K2 A21 -
K3 - A31
K3 - A32
6 record(s) selected.
|
|
06-17-10, 04:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
The predicates "attribute1 IS NOT NULL" and "attribute2 IS NOT NULL" are not neccesary,
bacause if attribute1 was NULL then the predicate "attribute1 <> '' " would be unknown and the row would not be selected.
|
|
06-19-10, 23:10
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
INNER JOIN may be enough.
Code:
------------------------------ Commands Entered ------------------------------
WITH
input(key , attribute1 , attribute2) AS (
VALUES
('K1' , 'A11' , '')
, ('K1' , 'A12' , '')
, ('K1' , '' , 'A21')
, ('K1' , CAST(NULL AS VARCHAR(1)) , 'A22')
, ('K1' , CAST(NULL AS VARCHAR(1)) , 'A23')
, ('K2' , 'B11' , CAST(NULL AS VARCHAR(1)))
, ('K2' , 'B12' , '')
, ('K3' , '' , 'C21')
)
SELECT s1.key
, attribute1
, attribute2
FROM (SELECT key
, attribute1
, ROW_NUMBER()
OVER(PARTITION BY key
ORDER BY NULLIF(attribute1 , '')
) AS rn
FROM input
) AS s1
INNER JOIN
(SELECT key
, attribute2
, ROW_NUMBER()
OVER(PARTITION BY key
ORDER BY NULLIF(attribute2 , '')
) AS rn
FROM input
) s2
ON s2.key = s1.key
AND s2.rn = s1.rn
WHERE attribute1 <> ''
OR attribute2 <> ''
ORDER BY
s1.key , s1.rn
;
------------------------------------------------------------------------------
KEY ATTRIBUTE1 ATTRIBUTE2
--- ---------- ----------
K1 A11 A21
K1 A12 A22
K1 A23
K2 B11 -
K2 B12
K3 C21
6 record(s) selected.
Blanks and NULLs will be chosen arbitralily.
If you want blanks for all null or blank values,
you can use COALESCE(attribute <n> , '') AS attribute <n> in final SELECT list.
If you want NULLs for all null or blank values,
you can use NULLIF(attribute <n> , '') AS attribute <n> in final SELECT list.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
query required
