At present we have cross tab queries which are run in MS Access and then used to create reports viewed via browser. Is there an easy way to replicate this in SQL Server and if so is this dependent on a particular version, we currently have 6.5

-- Anonymous, May 24, 1999

Answers

Nilesh,

This answer is extracted from the answer by Neil Pike of Protech Computing Ltd.

Q. How can I do a crosstab function using standard TSQL in SQL Server? (v1.0 21.12.1998) 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 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

Hope this helps.

Eric

-- Anonymous, May 26, 1999