A. It's obviously easier to use a product that has this sort of
functionality built-in - e.g. Excel, but it is possible to do it in standard
SQL, though there the query has to be hard-coded to the number of
columns/values required.
Take the following table
Product_Code Criteria_Code Value
------------ ------------- -----
100011
1
A
100011
2
B
100011
3
C
100011
4
D
100012
1
E
100012
2
B
100012
3
F
100012
4
D
Which you want to view as follows
Product_Code Criteria_1 Criteria_2 Criteria_3 Criteria_4
------------ ---------- ---------- ---------- ----------
100011 A
B
C
D
100012 E
B
F
D
If you don't have a CASE statement (e.g. pre SQL 6.0) then use the following
:-
SELECT Product_Code,
Criteria_1=MAX(substring(Value, 1, datalength(Value) *
(1 -
abs(sign(Criteria_Code - 1))))),
Criteria_2=MAX(substring(Value, 1, datalength(Value) *
(1 -
abs(sign(Criteria_Code - 2))))),
Criteria_3=MAX(substring(Value, 1, datalength(Value) *
(1 -
abs(sign(Criteria_Code - 3))))),
Criteria_4=MAX(substring(Value, 1, datalength(Value) *
(1 -
abs(sign(Criteria_Code - 4)))))
FROM <tbl>
GROUP BY Product_Code
If you do have the CASE statement available then use :-
SELECT Product_Code,
Criteria_1=MAX(substring(Value, 1, datalength(Value) *
(CASE
Criteria_Code WHEN 1 THEN 1 ELSE 0 END))),
Criteria_2=MAX(substring(Value, 1, datalength(Value) *
(CASE
Criteria_Code WHEN 1 THEN 2 ELSE 0 END))),
Criteria_3=MAX(substring(Value, 1, datalength(Value) *
(CASE
Criteria_Code WHEN 1 THEN 3 ELSE 0 END))),
Criteria_4=MAX(substring(Value, 1, datalength(Value) *
(CASE
Criteria_Code WHEN 1 THEN 4 ELSE 0 END)))
FROM <tbl>
GROUP BY Product_Code
Register
Btw, I think there's an error in the CASE version of the solution. What now reads
'...CASE Criteria_Code WHEN 1 THEN 2 ELSE 0 END...'
should in fact be
'...CASE Criteria_Code WHEN 2 THEN 1 ELSE 0 END...'
The same error is present on the other rows (except the first) as well.
Thank you! Petter Enqvist
Petter Enqvist March 13, 2001