#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2008
    Posts
    1
    Rep Power
    0

    SQL query to convert rows into column


    I have detail table like

    date item_id grade in out
    ------ ------- ------- ----- -----
    01-01-08 001 A 10 0
    02-01-08 001 O 8 0
    01-02-08 002 O 1 0
    03-01-08 001 T 0 10
    02-01-08 003 O 20 0
    02-01-08 003 T 0 10
    02-01-08 003 B 0 8


    Result View
    =======

    Item_id A B O T Total
    ------- --- ---- --- --- -------
    001 10 0 8 -10 8
    002 0 0 1 0 1
    003 0 -8 20 -10 2

    I want result group by Item_id and sum of grade in column .
    where grade could be any Alphabet, the column of result query could varies.
    Each grade contains sum of (in-out) of item_id of detail table.

    Smartsys99
  2. #2
  3. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    It's a bit ugly, but the typical solution to this problem is to GROUP BY Item_id and SUM() the total for each column using a CASE statement to assign a 1 to the desired value and a 0 to anything else. If you have sql server 2005 you might be able to do a little better with the PIVOT statement. Both solutions require that you know the expected columns in advance.
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn

IMN logo majestic logo threadwatch logo seochat tools logo