Re: CrossTabulate names

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Lance Wynn (lance_wynn_at_N.O.S.P.A.M.hotmail.com)
Date: 08/23/04

  • Next message: anonymous_at_discussions.microsoft.com: "Re: How to add Calendar" 
    Date: Sun, 22 Aug 2004 18:44:29 -0600

    Excellent! I learn something new every day on these NG's!

    Thanks
        Lance

    "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
    news:eEue7YJiEHA.4092@TK2MSFTNGP10.phx.gbl...
    Lance,

    A very nice and compact way to do it is with the following pattern:

    SELECT PKey, --Primary key
                MAX(CASE YourDecisionColumn
                            WHEN 'A' THEN YourValueColumn
                            ELSE NULL
                            END) AS A,
                MAX(CASE YourDecisionColumn
                            WHEN 'B' THEN YourValueColumn
                            ELSE NULL
                            END) AS B,
                ...
    FROM YourTable
    GROUP BY PKey

    This would tabulate the following table:

    PKey YourDecisionColumn YourValueColumn
    ---------------------------------------------------------
    1 A AValCol1
    1 B BValCol1
    2 A AValCol1
    2 B BValCol1

    into:

    PKey A B
    ----------------------------------
    1 AValCol1 BValCol1
    2 AValCol2 BValCol2

    Depending on your indexes, this method can perform quite a bit better than
    other methods that use temp tables or correlated subqueries.

    "Lance Wynn" <lance_wynn@N.O.S.P.A.M.hotmail.com> wrote in message
    news:OLzdfGBiEHA.1972@TK2MSFTNGP09.phx.gbl...
    > Hello, I've done something like this before, you can create a temp table
    for
    > your output (If you know how many possible name_type values there are),
    > and then do an update using a:
    >
    > Create #tmpOut with all required fields (Each possible name_type field)

  • Next message: anonymous_at_discussions.microsoft.com: "Re: How to add Calendar"

    Relevant Pages

    • Re: CrossTabulate names
      ... A very nice and compact way to do it is with the following pattern: ... GROUP BY PKey ... This would tabulate the following table: ... other methods that use temp tables or correlated subqueries. ...
      (microsoft.public.sqlserver.datamining)
    • Re: Interesting approach for compacting on close
      ... Thanks, Lyle, Salad, and all. ... Again, I am using a workspaces.accdb, which is a separate database to hold only the temp tables, so they are NOT in the FE. ... Providing a screen where a user can pick multiple invoices for printing, ordering into a purchase, etc. ... "Compact On Close" to be just another one of the superstitions they ...
      (comp.databases.ms-access)
    • Re: Interesting approach for compacting on close
      ... strongly about the compacting feature. ... the temp tables are already off-loaded into a workspace.accdb (separate database from the FE). ... Then you have other design errors in your front end besides storing ...
      (comp.databases.ms-access)
    • Re: Reading tables structure
      ... have problems when a user opened two sessions of the front ... By creating a random temporary databse to hold the temp ... >> with a compact on close worked well in a single session, ... >> to create a static database holding the temporary tables ...
      (microsoft.public.access.modulesdaovba)
    • Re: How to split a string read from the file?
      ... which means the pattern is not appropriate ... print @temp; ... look at the perldocs for 'foreach', 'push' and array slices ...
      (perl.beginners)