Results 1 to 5 of 5

Thread: query crosstab

  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: query crosstab

    Hi All,

    DB- SQL2000:

    How can I query something like this...


    IF OBJECT_ID('TempDB..#main','U') IS NOT NULL
    DROP TABLE #Main

    CREATE TABLE #Main
    (
    ID INT IDENTITY(1,1),
    PartNo VARCHAR(10) NOT NULL,
    TranType VARCHAR(7) NOT NULL,
    Date DATETIME NOT NULL,
    Quantity INT NOT NULL,
    RunningBalance INT DEFAULT 0 NOT NULL
    )

    insert into #main(partno, TranType, [date],quantity)

    select 'A', 'buy', '2006-10-10 ', 50 union all
    select 'A', 'buy', '2006-10-10 ', 50 union all
    select 'A', 'buy', '2006-10-10 ', 50 union all
    select 'A', 'buy', '2006-10-10 ', 50 union all
    select 'A', 'buy', '2006-11-01 ', 100 union all
    select 'A', 'sell', '2007-02-13 ', 150 union all
    select 'A', 'sell', '2007-05-10 ', 100 union all
    select 'A', 'buy', '2007-10-10 ', 500 union all
    select 'A', 'sell', '2007-12-01 ', 450 union all
    select 'A' ,'buy', '2007-12-02 ', 450 union all
    select 'A', 'sell', '2008-12-01 ', 550 union all
    select 'B', 'buy', '2006-12-10 ', 300 union all
    select 'B', 'sell', '2007-02-13 ', 200 union all
    select 'B' ,'buy', '2007-02-13 ', 200 union all
    select 'B', 'sell', '2007-05-01 ', 250 union all
    select 'B', 'sell', '2007-05-12 ', 250

    select * from #main

    Base from this...how can I get something like this:


    bPartNo bTranType bDate bQuantity sPartNo sTranType sDate sQuantity
    A buy 10/10/2006 50 A sell 2/13/2007 150
    A buy 10/10/2006 50 A sell 5/10/2007 100
    A buy 10/10/2006 50 A sell 12/1/2007 450
    A buy 10/10/2006 50 A sell 12/1/2008 550
    A buy 11/1/2006 100 - - - -
    A buy 10/10/2007 500 - - - -
    A buy 12/2/2007 450 - - - -
    B buy 12/10/2006 300 B sell 2/13/2007 200
    B buy 2/13/2007 200 B sell 5/1/2007 250
    - - - - B sell 5/12/2007 250


    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For 2000, lookup Crosstab Queries in Books Online and see how to do this using CASE statements.
    For 2005, lookup "Pivot Queries".
    But it is best not to do this in the database at all. This is normally a display or formatting issue, and should be handled by your application or reporting software.
    Last edited by blindman; 01-09-09 at 15:54.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    thanks..I reason I need this is I need further calculation from the result...I will look up the case statements....

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    try this one i am not sure it's is correct

    select row_number() over(partition by trantype,partno order by partno)as id1, * into #temp from #main
    select distinct t.partno,t.trantype,t.date,t.quantity,s.partno,s.t rantype,s.date,s.quantity from #temp t left join #temp s on s.id1 = t.id1 and t.partno = s.partno and t.date <> s.date

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "OVER" is not going to work on SQL Server 2000. That syntax was introduced in SQL Server 2005.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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