Cross-Tab query

Cross-Tab query

Post by Neil Albroc » Wed, 02 Feb 2000 04:00:00



Help!

I am trying to construct an SQL string, to interface with MS SQL 6.5, from
MS ISQL; along the following lines.

SELECT IF([field1]=[field2] THEN [field3] ELSE [feild4]) AS fieldname,
field5 FROM Table1

Make sense?

It can be done in Access, when talking to JET. Can it be done in Transact
SQL?

Any thoughts would be appreciated.

Neil

 
 
 

Cross-Tab query

Post by Shawn Clar » Wed, 02 Feb 2000 04:00:00


Neil,

Here you go:

CREATE TABLE Testing (
  Field1 int NOT NULL,
  Field2 int NOT NULL,
  Field3 int NOT NULL,
  Field4 int NOT NULL,
  Field5 int NOT NULL)

INSERT INTO Testing (Field1, Field2, Field3, Field4, Field5) VALUES
(1,2,3,4,1)
INSERT INTO Testing (Field1, Field2, Field3, Field4, Field5) VALUES
(5,5,6,7,2)
INSERT INTO Testing (Field1, Field2, Field3, Field4, Field5) VALUES
(8,9,10,11,3)
INSERT INTO Testing (Field1, Field2, Field3, Field4, Field5) VALUES
(12,12,13,14,4)
INSERT INTO Testing (Field1, Field2, Field3, Field4, Field5) VALUES
(15,15,16,17,5)

SELECT FieldName = CASE WHEN Field1 = Field2 THEN Field3 ELSE Field4 END,
Field5 FROM Testing

Shawn Clark
Meridian Technology Group


Quote:> Help!

> I am trying to construct an SQL string, to interface with MS SQL 6.5, from
> MS ISQL; along the following lines.

> SELECT IF([field1]=[field2] THEN [field3] ELSE [feild4]) AS fieldname,
> field5 FROM Table1

> Make sense?

> It can be done in Access, when talking to JET. Can it be done in Transact
> SQL?

> Any thoughts would be appreciated.

> Neil


 
 
 

Cross-Tab query

Post by Neil Pik » Wed, 02 Feb 2000 04:00:00


Neil,

Quote:> Subject: Cross-Tab query
> Date: Tue, 1 Feb 2000 16:28:07 -0000

> Help!

> I am trying to construct an SQL string, to interface with MS SQL 6.5, from
> MS ISQL; along the following lines.

> SELECT IF([field1]=[field2] THEN [field3] ELSE [feild4]) AS fieldname,
> field5 FROM Table1

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

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

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (412 entries) see

http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
 or www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.sql-server.co.uk

 
 
 

Cross-Tab query

Post by Neil Albroc » Thu, 03 Feb 2000 04:00:00


Brilliant...

I had to tweak your idea slightly for my purposes, but in essence, you were
spot on.

Thanks very much...

Neil


>Neil,

>> Subject: Cross-Tab query
>> Date: Tue, 1 Feb 2000 16:28:07 -0000

>> Help!

>> I am trying to construct an SQL string, to interface with MS SQL 6.5,
from
>> MS ISQL; along the following lines.

>> SELECT IF([field1]=[field2] THEN [field3] ELSE [feild4]) AS fieldname,
>> field5 FROM Table1

>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

>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

> Neil Pike MVP/MCSE.  Protech Computing Ltd
> (Please reply only to newsgroups)
> SQL FAQ (412 entries) see

>http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
> or www.ntfaq.com/sql.html (+ ntfaq download)
> or http://www.sql-server.co.uk

 
 
 

1. Moving Access Cross Tab Query to SQL Cross Tab

I have this access query that I am trying to move into SQL as Cross Tab query.  Every time I move this one into Query Analyzer, it fails.  Any help would be greatly appreciated

I have the visual design if anyone would like to take a stab at it

Or maybe its just a complex Summary Query

SELECT DutySquadrons.Group, Sum(IIf([ReceivedMPF]<[CloseoutDate]+30,1,0)) AS OnTime, Count(Active.Counter) AS Tot, IIf([tot]=0,0,100*([OnTime]/[Tot])) AS Rate, Sum(IIf(([index]<13),1,0)) AS J_Tot, Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]<13),1,0)) AS J_OnTime, IIf([j_tot]=0,0,([J_OnTime]/[J_Tot])*100) AS J_Rate, Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]>12 And [index]<19),1,0)) AS S_OnTime, Sum(IIf(([index]>12 And [index]<19),1,0)) AS S_Tot, IIf([s_tot]=0,0,([S_OnTime]/[S_Tot])*100) AS S_Rate, Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]>18),1,0)) AS O_OnTime, Sum(IIf(([index]>18),1,0)) AS O_Tot, IIf([o_tot]=0,0,([O_OnTime]/[O_Tot])*100) AS O_Rate, [S_OnTime]+[O_OnTime] AS B_OnTime, [S_Tot]+[O_Tot] AS B_Tot, IIf([B_Tot]=0,0,([B_OnTime]/[B_Tot])*100) AS B_Rate

FROM ((Active INNER JOIN DutySquadrons ON Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON Active.SSN=personnel.SSN) INNER JOIN Rank ON personnel.Rank=Rank.Rank

WHERE (((Active.[completion date]) Between [varStart] And [varStop]))

GROUP BY DutySquadrons.Group

HAVING (((DutySquadrons.Group)<>"tenant"));

2. ODBC on Unix

3. Cross-Tab Query Scenario

4. Document Stored procedures

5. Executing a MS Access Cross-Tab Query from MS SQL 6.5

6. COLDFUSION ODBC Error IM002

7. Cross-tab queries

8. Latest version of Sybase for SPARC's

9. Cross-tab Query !!!

10. Cross Tab queries

11. run Cross Tab query in SQL Server 7

12. Cross Tab queries