How can I do a crosstab function using standard TSQL in SQL Server?

A. It's obviously easier to use a product that has this sort offunctionality built-in - e.g. Excel, but it is possible to do it in standardSQL, though there the query has to be hard-coded to the number ofcolumns/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
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
GROUP BY Product_Code