Re: Crosstab query in SQL

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Greg O (greg_at_No-SPAM*NO(SPAM@ag-software.com)
Date: 03/11/04 

Date: Fri, 12 Mar 2004 09:19:43 +1100

HI,
The problem with crosstab reports is that you need to know the columns
before you can create the query. Using an automated toll like
xp_ags_crosstab is a better solution because you can feed in simple queries
and not worry about the crosstab works.
I suggest you look at xp_ags_crosstab, so far I have tested results with
over 8000+ and 10,000 rows with problems and results in under one minute

It is licensed per server no matter how many connection or databases
http://www.ag-software.com/xp_ags_crosstab.aspx 

-- 
I hope this helped
Greg O
Save HOURS documenting your SQL Server! -- FREE Trial!
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab Queries without OLAP -- 30 day free Trial!
http://www.ag-software.com/xp_ags_crosstab.aspx
"Rahul Chatterjee" <rahul@benesysinc.com> wrote in message
news:Oo3Nip2BEHA.2804@tk2msftngp13.phx.gbl...
> Hello All
>
> I have a sql table which has information like
>
> Area        Plan       EmployeeCnt       Coverage
> 1                A            2                            S
> 1                A            1                            F
> 1                B            1                            F
> 2                A            3                            2Person
> 2                A            1                            S
> 2                C            1                            F
>
>
> I export the table into access and use a crosstab query (as below) to
create
> a report
>
> TRANSFORM Sum(L100PHARMACYCENSUSREPORT.EMPCNT) AS SumOfEMPCNT
> SELECT L100PHARMACYCENSUSREPORT.AREA, L100PHARMACYCENSUSREPORT.PLANTYP
> FROM L100PHARMACYCENSUSREPORT
> WHERE (((L100PHARMACYCENSUSREPORT.DEP3)<>'B') AND
> ((L100PHARMACYCENSUSREPORT.AREA)<>''))
> GROUP BY L100PHARMACYCENSUSREPORT.AREA, L100PHARMACYCENSUSREPORT.PLANTYP,
> L100PHARMACYCENSUSREPORT.AREA
> PIVOT L100PHARMACYCENSUSREPORT.DEP3;
>
>
> Area       Plan    Single    2Person    Family
> 1                A    2                                 1
> 1                B                                       1
> 2                A    1                3
> 2                C                                        1
>
>
> How would I do the same thing in SQL server itself without having to
export
> to Access
>
> Thanks
>
>

Relevant Pages

  • Re: TWO Crosstab Query Ouput Questions
    ... field very similar to the LOCATE field in the STATES2 that we created. ... I was certain that all I had to do was create a crosstab on RCROSS with RLOC ... do is to modify the main query to change your RFLY_RSTA to LOCATE and the ... locations for each species as rows with the ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... we want to see and use those as the input to the crosstab. ... and only 1098 in the final query. ... locations for each species as rows with the ... concerning the Totals Query. ...
    (microsoft.public.access.queries)
  • Re: dynamic crosstab A2002 - Almost there
    ... I would set up the crosstab as ... Query 1: QryMonthlyIncidentsBase_Crosstab ... Base your report on this query. ... >>> Dim ReportLabelAs String ...
    (microsoft.public.access.queries)
  • Re: Cross Tab Query with two value=-[Result] & " - " & [Error] as the
    ... This solution uses a crosstab with a cartesian to create multiple values. ... Anyway, the Union Query now has 6 SELECT parts, not 3, and each one ... SELECT ID,Client,Date, ResultA as Value1, "ResultA" as Task ...
    (microsoft.public.access.queries)
  • Re: CrossTab Query - Display Is null Fields
    ... I can filter this manually within the CrossTab Query: ... "Duane Hookom" wrote: ... Table = ZONE TABLE ...
    (microsoft.public.access.queries)