Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Answered: Have Query Results Display Vertical Instead Of Horizontal

    I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
    This is my current query
    Code:
    Select
    count(case when salesman Like 'Geo%' then id else null end) As [George]
    ,count(case when salesman Like 'Li%' then id else null end) As [Lisa]
    ,count(case when salesman Like 'Jor%' then id else null end) As [Jorge]
    ,count(case when salesman Like 'Ri%' then id else null end) As [Richard]
    ,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales]
    ,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales]
    from sales.southeastregion
    Which of course shows the results as such
    George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2
    100 50 10 90 150 100

    And I want the data to be displayed like
    George - 100
    Lista - 50
    Jorge - 10
    Richard - 90
    Team 1 - 150
    Team 2 - 100


    This is SQL Server 2008 if that matters.

  2. Best Answer
    Posted by Pat Phelan

    "
    Code:
    --  vvvvv  This is just to setup sample data to match your request , don't sweat it
    
    DECLARE @t TABLE (
       thingie      INT             NOT NULL
    ,  who          VARCHAR(10)     NOT NULL
       )
    
    INSERT INTO @t (who, thingie)
       SELECT z.name, y.number
          FROM (VALUES ('George', 100), ('Lisa', 50), ('Jorge', 10)
    ,        ('Richard', 90), ('Team 1', 150), ('Team 2', 100)) AS z(name, howmany)
          JOIN master.dbo.spt_values AS y
    	     ON ('P' = y.type
    		 AND y.number < z.howmany)
    
    --  ^^^^^  This is just to setup sample data to match your request , don't sweat it
    
    --  This is the part that you really want
    
    SELECT who, Count(thingie)
       FROM @t
       GROUP BY who
       ORDER BY who
    
    --  Show the sample data
    
    SELECT * FROM @t
    -PatP"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Your query is too complicated as a query.
    To do it the normal way,is:
    Select [name],count([sales]) from table

  4. #3
    Join Date
    Feb 2012
    Posts
    188
    Too complicated as a query? How would I simplify it?

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  vvvvv  This is just to setup sample data to match your request , don't sweat it
    
    DECLARE @t TABLE (
       thingie      INT             NOT NULL
    ,  who          VARCHAR(10)     NOT NULL
       )
    
    INSERT INTO @t (who, thingie)
       SELECT z.name, y.number
          FROM (VALUES ('George', 100), ('Lisa', 50), ('Jorge', 10)
    ,        ('Richard', 90), ('Team 1', 150), ('Team 2', 100)) AS z(name, howmany)
          JOIN master.dbo.spt_values AS y
    	     ON ('P' = y.type
    		 AND y.number < z.howmany)
    
    --  ^^^^^  This is just to setup sample data to match your request , don't sweat it
    
    --  This is the part that you really want
    
    SELECT who, Count(thingie)
       FROM @t
       GROUP BY who
       ORDER BY who
    
    --  Show the sample data
    
    SELECT * FROM @t
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a slight twist or kink on the previous answer, but clearer and easier to maintain:
    Code:
    --  vvvvv  This is just to setup sample data to match your request , don't sweat it
    
    DECLARE @t TABLE (
       thingie      INT             NOT NULL
    ,  team         INT             NOT NULL
    ,  who          VARCHAR(10)     NOT NULL
       )
    
    INSERT INTO @t (who, thingie, team)
       SELECT z.name, y.number, z.team
          FROM (VALUES ('George', 100, 1), ('Lisa', 50, 1), ('Jorge', 10, 2)
    ,        ('Richard', 90, 2)) AS z(name, howmany, team)
          JOIN master.dbo.spt_values AS y
    	     ON ('P' = y.type
    		 AND y.number < z.howmany)
    
    --  ^^^^^  This is just to setup sample data to match your request , don't sweat it
    
    --  This is the part that you really want
    
    SELECT who, Count(thingie)
       FROM @t
       GROUP BY who
    UNION SELECT 'Team ' + Cast(team AS VARCHAR), Count(thingie)
       FROM @t
       GROUP BY 'Team ' + Cast(team AS VARCHAR)
       ORDER BY 1
    
    --  Show the sample data
    
    SELECT * FROM @t
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Quote Originally Posted by ranman256 View Post
    Your query is too complicated as a query.
    To do it the normal way,is:
    Select [name],count([sales]) from table
    He left out the group by name but this is the best way of doing the task at hand. As for getting the team counts, that can be added here as well. Is there something in your database that defines who is on each team? That would be a much better method than hard coding names in case statements.
    Dave

Posting Permissions

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