by Juan » Sat, 12 May 2001 05:20:52
I have a table that has three fields.
Department, Code, Interviewer.
I want to create a crosstab query that displays the total count of the code
(There are 10 codes) and group them by Department.
Something like this:
Department code1 code2 code3 ... code10
Thank you for your help and/or references.
by BP Margoli » Sat, 12 May 2001 08:02:59
In the future please post table schemas as CREATE TABLEs, and sample data as
INSERTs. Having CREATE TABLE and INSERT statements permits one to test a
query before posting it. Since you have not posted table schemas nor sample
data, the following is untested:
select Department,
code1 = sum(case when Code = 1 then 1 else 0 end),
code2 = sum(case when Code = 2 then 1 else 0 end),
...
code10 = sum(case when Code = 10 then 1 else 0 end)
from tblJuan
group by Department
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
Quote:> How can I create a crosstab query?
> I have a table that has three fields.
> Department, Code, Interviewer.
> I want to create a crosstab query that displays the total count of the
code
> (There are 10 codes) and group them by Department.
> Something like this:
> Department code1 code2 code3 ... code10
> Thank you for your help and/or references.
1. multiple queries into a crosstab query in VB
I have the following 4 queries in A97 that I would like to be able to
do in VB6 using ADO
This is the first query, it is called 'setup Grand total per day'
SELECT DWN.Dte, WSTN.Description, Sum(DWN.Duration) AS GrandTotalTime
FROM (DWN INNER JOIN WSTN ON DWN.WstnID = WSTN.WstnID) INNER JOIN
Major ON (DWN.MajID = Major.MajID) AND (DWN.WstnID = Major.WstnID)
GROUP BY DWN.Dte, WSTN.Description
ORDER BY DWN.Dte, WSTN.Description;
This is the secod query, it is called 'setup total Production per
day'
SELECT DWN.Dte, WSTN.Description, Sum(DWN.Duration) AS
TotalProducingTime
FROM (DWN INNER JOIN WSTN ON DWN.WstnID = WSTN.WstnID) INNER JOIN
Major ON (DWN.MajID = Major.MajID) AND (DWN.WstnID = Major.WstnID)
GROUP BY DWN.Dte, WSTN.Description, Major.Descr
HAVING (((Major.Descr)="PRODUCING"))
ORDER BY DWN.Dte, WSTN.Description;
The above 2 queries get linked together to form the third query, it is
called 'Setup Crosstab Downtime by day by workstation'
SELECT [setup Grand total per day].Dte, [setup Grand total per
day].Description, [GrandTotalTime]-[TotalProducingTime] AS DownTime
FROM [setup Grand total per day] INNER JOIN [setup total Production
per day] ON ([setup Grand total per day].Dte = [setup total
Production per day].Dte) AND ([setup Grand total per day].Description
= [setup total Production per day].Description);
the results of the third query are processed in the fourth query, a
crosstab, called 'Crosstab_Downtime_by_day_by_workstation'
TRANSFORM Sum([Setup Crosstab Downtime by day by
workstation].DownTime) AS [The Value]
SELECT [Setup Crosstab Downtime by day by workstation].Dte
FROM [Setup Crosstab Downtime by day by workstation]
GROUP BY [Setup Crosstab Downtime by day by workstation].Dte
PIVOT [Setup Crosstab Downtime by day by workstation].Description;
To get the above to work in VB6 thus far I have done the following
adoWaiver.ConnectionString = conStringOFIR
adoWaiver.RecordSource = "Crosstab_Downtime_by_day_by_workstation"
adoWaiver.Refresh
Set TDBGrid1.DataSource = adoWaiver
TDBGrid1.Refresh
TDBGrid1.ReBind
TDBGrid1.Refresh
This all works fine by as you can see, the query that I am calling,
query 4 above actually resides in the A97 database. I do not wish to
have it in the database at all as I want to code it up in pure VB. Can
somesome please show me how I could code up the above 4 queries into 1
superquery so that I can get the results of the crosstab into my True
Db Grid control.
PS
That dates are comming back in American format m/dd/yy, which play
havoc when trying to load the results up into excel under Australian
format 'dd/mm/yyyy'. I need to get the results into excel as
'dd/mm/yyyy'. Does anyone know how to fix this ?
Thanks
Stu-man
3. newbie question - choosing a record without a corresponding entry in a second table
5. How to list all available Tables in a Database?
6. Can I use crosstab query in ADP?
7. MS SQLServer to Oracle data migration - info needed
10. Crosstab Query Question -- Thanks
11. Crosstab Queries
12. Crosstab Queries in SQL Server 6.5?