[AccessD] SQL Server Pivot

AD.  Thanks for that, though I am not sure that it is going to work simply
because this is on SQL Server and contains 50 million records.  I was trying
to discover how to do something like this inside of SQL Server because I
have to tie it to where clauses, and then do this routinely, like every week
or even day. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Sunday, February 03, 2008 2:06 PM
To: Access Developers discussion and problem solving
Cc: A.D.Tejpal
Subject: Re: [AccessD] SQL Server Pivot

John,

    The requirement presented by you would involve crosstab flip flop.
Generic sample subroutine named P_MakeAndShowQryFlipFlop(), as given below,
transforms table contents from cross tab to normal and then back to new
version of crosstab. The results are displayed in programmatically built
query "Q_FlipFlop"

    The subroutine needs two arguments as follows:
    (a) Name of table
    (b) Ordinal position of first field storing age codes.
          (Imp: It is to be ensured that all fields thereafter are only
those with age codes)

    For example, if the data table is named T_Age and AgeCodes are stored in
second field onwards, the subroutine will be called as per following
statement:

    P_MakeAndShowQryFlipFlop "T_Age", 2

    With this call, the query "Q_FlipFlop" will get built programmatically,
duly taking into account the up-to-date contents of data table. It takes
care of any variations in number of columns meant for holding AgeCodes and
final results are in four column format (AgeGroup, Count of Code 1, Count of
Code 2, Count of Code 3) as sought by you.

    Note - Suggested line of action is based upon tests conducted on Access
2003 desktop (Access 2000 file format). This has to be kept in view while
adapting to SQL server.

Best wishes,
A.D.Tejpal
------------

' Crosstab FlipFlop Query (named Q_FlipFlop) ' Sample subroutine for
building the query ' and displaying its results
'======================================
Sub P_MakeAndShowQryFlipFlop( _
                    TableName As String, _
                    PosOfFirstAgeGrpField As Long)
    ' Transforms table contents from cross tab
    ' to normal and then back to new version
    ' of crosstab. The results are displayed in
    ' prgrammatically built query "Q_FlipFlop"
    On Error Resume Next
    Dim db As DAO.Database
    Dim tdf As TableDef

    Dim Qst As String, Cnt As Long
    Dim Fnm As String

    Set db = CurrentDb
    Set tdf = db.TableDefs(TableName)
    
    Qst = "TRANSFORM Count(Q1.GrpCode) AS " & _
            "CodeCount " & _
            "SELECT Q1.AgeGrp FROM ["
            
    For Cnt = PosOfFirstAgeGrpField - 1 To tdf.Fields.Count - 1
        Qst = Qst & IIf(Cnt > PosOfFirstAgeGrpField - 1, _
                " UNION ALL ", "")
        Fnm = tdf.Fields(Cnt).Name
        Qst = Qst & "SELECT " & Chr(39) & Fnm & _
                Chr(39) & " AS AgeGrp, " & Fnm & _
                " AS GrpCode FROM " & TableName
    Next
    
    Qst = Qst & "]. AS Q1 " & _
            "WHERE Not IsNull(Q1.GrpCode) " & _
            "GROUP BY Q1.AgeGrp " & _
            "PIVOT 'C_' & Q1.[GrpCode];"
    
    ' Delete target query if already existing
    ' and then re-create the same as per above SQL
    DoCmd.DeleteObject acQuery, "Q_FlipFlop"
    db.CreateQueryDef "Q_FlipFlop", Qst
    db.QueryDefs.Refresh
    
    ' Display results
    DoCmd.OpenQuery "Q_FlipFlop"
    
    Set tdf = Nothing
    Set db = Nothing
    On Error GoTo 0
End Sub
'======================================

  ----- Original Message -----
  From: jwcolby
  To: 'Access Developers discussion and problem solving' ; 'Discussion
concerning MS SQL Server' 
  Sent: Saturday, February 02, 2008 22:17
  Subject: [AccessD] SQL Server Pivot


  I have a bunch of columns that break out ages into bands.  For example:

  Presence_of_adults_age_65_74_specific
  Presence_of_adults_age_55_64_specific
  Presence_of_adults_age_45_54_specific
  etc

  Each column has codes 1,2,3

  I need counts of each column, for each value

  column Cnt1 Cnt2 Cnt3
  65_74 45 3 23
  55_64 103 48 211
  45_54 20 1 49

  etc

  I think the Pivot statement is going to do that for me but I can't seem to
  wrap my mind around the syntax.  Can anyone point me to a web page that
  clearly explains this or simply write a SQL statement that does this?

  Thanks

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com