Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014
    Posts
    5

    Unanswered: Query to Parse one column based on another

    Hello All,

    I am a newbie to the SQL server world and I have a question I just haven't been able to answer.

    I have a table that is something like this:

    Animal | Count
    Dog |10
    Dog |5
    Dog |16
    Dog |2
    Cat |1
    Cat |12
    Cat |14
    Cat |7

    I need to write a query that will return something like this:

    Dog_Count| Cat_ Count
    10 |10
    5 |5
    16 |16
    2 |2


    Can anyone get me started down the right path?

    Thanks in advance!
    Ken
    Last edited by KenRob; 11-24-14 at 13:57.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So you want to take the column named Count (which is a really bad idea because Count() is a SQL function) for the "Dog" rows and replicate them for the "Cat" rows? That seems very strange to me, is it what you really want?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       [Animal] CHAR(3)
    ,  [Count]  NUMERIC
    )
    
    INSERT INTO @t ([Animal], [Count])
       VALUES ('Dog', 10), ('Dog', 5), ('Dog', 16), ('Dog', 2)
    ,     ('Cat', 1), ('Cat', 12), ('Cat', 14), ('Cat', 7)
    
    SELECT
       [Count] AS [Dog Count]
    ,  [Count] AS [Cat Count]
       FROM @t
       WHERE  'Dog' = [Animal]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2014
    Posts
    5

    Follow-up

    Pat,
    Not quite. By the way this is just an example, I don't really have a column called Count.

    I don't want to INSERT these values into a table, I want to create a VIEW in which each column is the count of a specific animal (to continue with this example [and I will continue with my poorly name columns).

    So something like...

    SELECT
    Count AS Dog_Count WHERE Animal IN ('Dog')
    FROM tbl_Animals



    But I would need to do this for multiple Animals.

    Does this make more sense? I hope so... It is so tricky to explain this stuff sometimes.

    Thanks!
    Ken

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It sounds like you want a PIVOT. It is much simpler and more efficient to do that at the client than on the SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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