|
How can I do a crosstab
function using standard TSQL in SQL Server?
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 |
