unique values

W

wimsan

Hi there,

i have a table that has duplicate values (maximum 2) for the field AWK_ID
which is OK because 1 AWK_ID can refer to different products.

i would like to create 1 records for every AWK_ID with information of both
products it refers to

table
awk_id product
1 a
2 b
2 c
3 a
4 a
4 c

the output should look like this

awk_id product1 product2
1 a
2 b c
3 a
4 a c

how can i achieve this ?

regards
Wimsan
 
G

giorgio rancati

Hi wimsan,

----
SELECT awk_id,
Min(product) AS product1,
Iif(Max(product)=Min(product),NULL,Max(product)) AS product2
FROM

GROUP BY awk_id;
 
P

peregenem

wimsan said:
table
awk_id product
1 a
2 b
2 c
3 a
4 a
4 c

the output should look like this

awk_id product1 product2
1 a
2 b c
3 a
4 a c
Click to expand...

The below will scale i.e. three or more columns:

CREATE TABLE MyTable (
awk_id INTEGER NOT NULL,
product CHAR(1) NOT NULL,
CHECK(product LIKE '[a-z]'),
UNIQUE(product, awk_id)
)
;
INSERT INTO MyTable VALUES (1, 'a')
;
INSERT INTO MyTable VALUES (2, 'b')
;
INSERT INTO MyTable VALUES (2, 'c')
;
INSERT INTO MyTable VALUES (3, 'a')
;
INSERT INTO MyTable VALUES (4, 'a')
;
INSERT INTO MyTable VALUES (4, 'c')
;
SELECT DT0.awk_id,
DT1.product AS product_1, DT2.product AS product_2
FROM (
(
SELECT DISTINCT awk_id
FROM MyTable) AS DT0
INNER JOIN (
SELECT T1.awk_id, T1.product
FROM MyTable AS T1
WHERE 1 = (
SELECT COUNT(*)
FROM MyTable
WHERE awk_id = T1.awk_id
AND product <= T1.product)
) AS DT1 ON DT0.awk_id = DT1.awk_id
) LEFT JOIN (
SELECT T1.awk_id, T1.product
FROM MyTable AS T1
WHERE 2 = (
SELECT COUNT(*)
FROM MyTable
WHERE awk_id = T1.awk_id
AND product <= T1.product)
) AS DT2 ON DT0.awk_id = DT2.awk_id
;
 
Top