Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Table Group By Rows then Columns

    I have a table, Table1 with 3 columns as follows: colItemKey, colGrouping1, colGrouping2.
    colItemKey is the primary key. Say colGrouping1 has 4 different types: Grp1A, Grp1B, Grp1C and Grp1D and colGrouping2 has 5 as follows: Grp2A, Grp2B, Grp2C, Grp2D and Grp2E. How do I setup my select so that the result set is as follows:

    Grp2A Grp2B Grp2C Grp2D Grp2E

    Grp1A nnn nnn nnn nnn nnn
    Grp1B nnn nnn nnn nnn nnn
    Grp1C nnn nnn nnn nnn nnn
    Grp1D nnn nnn nnn nnn nnn

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I understood the part about "I have a table", but things started to get fuzzier and fuzzier after that... Can you try to restate your problem in more concrete terms?

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you are looking for is called a crosstab query and only Microsoft Access has this capability built into its SQL

    the best you can do in SQL Server is hardcode the colGrouping2 values you're looking for, like this:
    Code:
    select colGrouping1 
         , count(case when colGrouping2 = 'Grp2A'
                      then 937 else null end) as Grp2A
         , count(case when colGrouping2 = 'Grp2B'
                      then 937 else null end) as Grp2B
         , count(case when colGrouping2 = 'Grp2C'
                      then 937 else null end) as Grp2C
         , count(case when colGrouping2 = 'Grp2D'
                      then 937 else null end) as Grp2D
         , count(case when colGrouping2 = 'Grp2E'
                      then 937 else null end) as Grp2E
      from daTable
    group
        by colGrouping1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •