Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    12

    Unanswered: Columnar Data with Group By

    If I had the following table

    tblFormData

    State Question 1
    ------ ------------
    TX cat
    TX dog
    TX dog
    CA cat
    CA cat
    CA dog
    CA bunny
    AK bunny
    AK dog

    and I want the results to look like this

    State cat dog bunny
    -------------------------------
    TX 1 2 0
    CA 2 1 1
    AK 0 1 1

    How would I do this?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try something like this:


    select state
    ,isnull((select count(*) from tblFormData where state=a.state and [Question 1]='cat' ),0) as 'cat'
    ,---repeat the same for anothers
    from tblFormData a
    group by state

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How About:

    Code:
    
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
    GO
    
    INSERT INTO myTable99 (Col1, Col2)
    SELECT 'TX', 'cat' UNION ALL
    SELECT 'TX', 'dog' UNION ALL
    SELECT 'TX', 'dog' UNION ALL
    SELECT 'CA', 'cat' UNION ALL
    SELECT 'CA', 'cat' UNION ALL
    SELECT 'CA', 'dog' UNION ALL
    SELECT 'CA', 'bunny' UNION ALL
    SELECT 'AK', 'bunny' UNION ALL
    SELECT 'AK', 'dog'
    GO
    
       SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
         FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
    LEFT JOIN myTable99 b
           ON driver.Col1 = b.Col1
          AND b.Col2 = 'bunny'
    LEFT JOIN myTable99 c
           ON driver.Col1 = c.Col1
          AND c.Col2 = 'cat'
    LEFT JOIN myTable99 d
           ON driver.Col1 = d.Col1
          AND d.Col2 = 'dog'
    GROUP BY driver.col1
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2001
    Posts
    24
    Originally posted by Brett Kaiser
    How About:

    Code:
    
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
    GO
    
    INSERT INTO myTable99 (Col1, Col2)
    SELECT 'TX', 'cat' UNION ALL
    SELECT 'TX', 'dog' UNION ALL
    SELECT 'TX', 'dog' UNION ALL
    SELECT 'CA', 'cat' UNION ALL
    SELECT 'CA', 'cat' UNION ALL
    SELECT 'CA', 'dog' UNION ALL
    SELECT 'CA', 'bunny' UNION ALL
    SELECT 'AK', 'bunny' UNION ALL
    SELECT 'AK', 'dog'
    GO
    
       SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
         FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
    LEFT JOIN myTable99 b
           ON driver.Col1 = b.Col1
          AND b.Col2 = 'bunny'
    LEFT JOIN myTable99 c
           ON driver.Col1 = c.Col1
          AND c.Col2 = 'cat'
    LEFT JOIN myTable99 d
           ON driver.Col1 = d.Col1
          AND d.Col2 = 'dog'
    GROUP BY driver.col1
    GO
    
    DROP TABLE myTable99
    GO
    USE Northwind
    GO

    CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
    GO

    INSERT INTO myTable99 (Col1, Col2)
    SELECT 'TX', 'cat' UNION ALL
    SELECT 'TX', 'dog' UNION ALL
    SELECT 'TX', 'dog' UNION ALL
    SELECT 'CA', 'cat' UNION ALL
    SELECT 'CA', 'cat' UNION ALL
    SELECT 'CA', 'dog' UNION ALL
    SELECT 'CA', 'bunny' UNION ALL
    SELECT 'AK', 'bunny' UNION ALL
    SELECT 'AK', 'dog'
    GO

    ---------------------
    Howabout this ???
    ---------------------

    Select Col1 as State,
    Sum (Case when Col2='Cat' then 1 else 0 end) as Cat,
    Sum (Case when Col2='Dog' then 1 else 0 end) as Dog,
    Sum (Case when Col2='Bunny' then 1 else 0 end) as Bunny
    from myTable99
    Group By Col1

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    I hope 'jiggle it' has to be happy

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    there's always more than one way to skin the cat/dog/bunny

Posting Permissions

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