cross tab

  • IF OBJECT_ID('TempDB..#tblInv','U') IS NOT NULL

    DROP TABLE #tblInv

    CREATE TABLE #tblInv

    (

    ID INT ,

    PartNo VARCHAR(10) NOT NULL,

    TranType VARCHAR(7) NOT NULL,

    Quantity INT NOT NULL

    )

    insert into #tblInv(id,partno, TranType,quantity)

    select 1, 'A', 'buy', 50 union all

    select 2,'A', 'buy', 50 union all

    select 3,'A', 'buy', 50 union all

    select 4,'A', 'buy', 50 union all

    select 5,'A', 'buy', 100 union all

    select 6,'A', 'sell', 150 union all

    select 7,'A', 'sell', 100 union all

    select 8,'A', 'buy', 500 union all

    select 9,'A', 'sell', 450 union all

    select 10,'A' ,'buy', 450 union all

    select 11,'A', 'sell', 550 union all

    select 12,'B', 'buy', 300 union all

    select 13,'B', 'sell', 200 union all

    select 14,'B' ,'buy', 200 union all

    select 15,'B', 'sell', 250 union all

    select 16, 'B', 'sell', 250

    select * from #tblInv

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

    idbIDPartNoTranTypeQuantitysIDsQuantity

    11Abuy506150

    22Abuy507100

    33Abuy509450

    44Abuy5011550

    55Abuy10000

    68Abuy50000

    710Abuy45000

    812Bbuy30013200

    914Bbuy20015250

    100B-016250

    thanks

  • Actually you don't need a cross tab/pivot....

    IF OBJECT_ID('TempDB..#tblInv','U') IS NOT NULL

    DROP TABLE #tblInv

    CREATE TABLE #tblInv

    (

    ID INT ,

    PartNo VARCHAR(10) NOT NULL,

    TranType VARCHAR(7) NOT NULL,

    Quantity INT NOT NULL

    )

    insert into #tblInv(id,partno, TranType,quantity)

    select 1, 'A', 'buy', 50 union all

    select 2,'A', 'buy', 50 union all

    select 3,'A', 'buy', 50 union all

    select 4,'A', 'buy', 50 union all

    select 5,'A', 'buy', 100 union all

    select 6,'A', 'sell', 150 union all

    select 7,'A', 'sell', 100 union all

    select 8,'A', 'buy', 500 union all

    select 9,'A', 'sell', 450 union all

    select 10,'A' ,'buy', 450 union all

    select 11,'A', 'sell', 550 union all

    select 12,'B', 'buy', 300 union all

    select 13,'B', 'sell', 200 union all

    select 14,'B' ,'buy', 200 union all

    select 15,'B', 'sell', 250 union all

    select 16, 'B', 'sell', 250

    select * from #tblInv

    SELECTCOALESCE(b.id,s.id) AS id, COALESCE(b.id,0) AS bid, COALESCE(b.partno,s.partno) AS partno,

    COALESCE(b.TranType,s.TranType) AS TranType, COALESCE(b.quantity,0) AS bquantity,

    COALESCE(s.id,0) AS [sid], COALESCE(s.quantity,0) AS squantity

    FROM(

    SELECTROW_NUMBER() OVER( PARTITION BY partno ORDER BY id ) AS rowid, *

    FROM#tblInv

    WHERETranType = 'buy'

    ) b

    FULL OUTER JOIN

    (

    SELECTROW_NUMBER() OVER( PARTITION BY partno ORDER BY id ) AS rowid, *

    FROM#tblInv

    WHERETranType = 'sell'

    ) s ON b.partno = s.partno AND b.rowid = s.rowid

    Edit:

    Aaaah, I think I had too much coffee in the day:w00t:, posting 2K5 solutions in 2K forums....

    Just wait for few minutes, I'll be back with 2K solution...

    --Ramesh


  • Ramesh,

    thank you very much for looking into this...It would be easy if the database is sql2005, but unfortunately, I'm dealing with sql2k...which make a bit more difficult. I had try the using Case statement, but not getting the anywhere.

    thanks

  • Try this

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    It should answer most of your questions.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • First of all, a disclaimer:

    The report you asked for is something that is likely best handled in the front end. This really is not a cross tab report (at least not the way I define it.) This is combining 2 separate data sets into one. The data you are trying to display in the same row has no true relation other than the fact that they're transactions on the same product ordered by their date.

    That said, you can get the output you're requesting like this:

    [font="Courier New"]IF OBJECT_ID('TempDB..#tblInv','U') IS NOT NULL

    DROP TABLE #tblInv

    CREATE TABLE #tblInv

    (

    ID         INT,

    PartNo         VARCHAR(10) NOT NULL,

    TranType   VARCHAR(7) NOT NULL,

    Quantity   INT NOT NULL,

    PRIMARY KEY CLUSTERED(PartNo, TranType, ID),

    RN     INT,

    BSOrder        INT

    )

    INSERT INTO #tblInv(id,partno, TranType,quantity)

    SELECT 1, 'A', 'buy',  50 UNION ALL

    SELECT 2,'A', 'buy',  50 UNION ALL

    SELECT 3,'A', 'buy',  50 UNION ALL

    SELECT 4,'A', 'buy',  50 UNION ALL

    SELECT 5,'A', 'buy',  100 UNION ALL

    SELECT 6,'A', 'sell',  150 UNION ALL

    SELECT 7,'A', 'sell',  100 UNION ALL

    SELECT 8,'A', 'buy',  500 UNION ALL

    SELECT 9,'A', 'sell',  450 UNION ALL

    SELECT 10,'A' ,'buy',  450 UNION ALL

    SELECT 11,'A', 'sell',  550 UNION ALL

    SELECT 12,'B', 'buy',  300 UNION ALL

    SELECT 13,'B', 'sell',  200 UNION ALL

    SELECT 14,'B' ,'buy',  200 UNION ALL

    SELECT 15,'B', 'sell',  250 UNION ALL

    SELECT 16, 'B', 'sell',  250

    DECLARE @RN         INT,

       @BSOrder    INT,

       @ID         INT,

       @LastPart   VARCHAR(10),

       @LastType   VARCHAR(7)

    SET @RN = 0

    SET @BSOrder = 1

    UPDATE #tblInv

    SET    @RN = RN = @RN + 1,

       @BSOrder = BSOrder = CASE WHEN @LastPart = PartNo AND @LastType = TranType THEN @BSOrder + 1 ELSE 1 END,

       @LastPart = PartNo,

       @LastType = TranType,

       @ID = [ID] -- Anchor

    FROM #tblInv WITH (INDEX(0))

    SELECT     COALESCE(B.RN, S.RN) RN,

       ISNULL(B.[ID],0) BID,

       COALESCE(B.PartNo,S.PartNo) PartNo,

       ISNULL(B.TranType,'-') TranType,

       ISNULL(B.Quantity,0) Quantity,

       ISNULL(S.[ID],0) SID,

       ISNULL(S.Quantity,0) sQuantity

    FROM   (SELECT * FROM #tblInv WHERE TranType = 'buy') B

       FULL OUTER JOIN

       (SELECT * FROM #tblInv WHERE TranType = 'sell') S

       ON B.PartNo = S.PartNo AND B.BSOrder = S.BSOrder

    ORDER BY COALESCE(B.RN, S.RN)[/font]

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • I don't get it... why does the SQuantity drop to 0 for rows 5 and 8? Not enough quantity has been sold off for that to happen on what appears to be a simple running total problem. I know the problem has been solved, but can someone explain why? Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/14/2009)


    Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.

    Aaaah...;), That's what I've done in our payroll system....:):)

    --Ramesh


  • Ramesh (1/14/2009)


    Jeff Moden (1/14/2009)


    Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.

    Aaaah...;), That's what I've done in our payroll system....:):)

    Heh... payroll, inventory... doesn't matter. I don't understand the business rules for matching two seemingly unrelated rows.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/14/2009)


    Ramesh (1/14/2009)


    Jeff Moden (1/14/2009)


    Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.

    Aaaah...;), That's what I've done in our payroll system....:):)

    Heh... payroll, inventory... doesn't matter. I don't understand the business rules for matching two seemingly unrelated rows.

    I guess it just like showing data in columnar manner, that you see in payslips. And also there are some not so good reporting tools (which i use) that doesn't do what it should be doing...

    --Ramesh


  • If the amounts sold were directly related to the amounts bought, this would be a good report. If your payroll does the same with hours worked and amount paid, good stuff, there's a direct relation. Here, you've really got 2 completley separate data sets, your ordering and your sales. If you were ordering specifically for a sale order, fine. Otherwise... I don't see much value in putting it all in the same column. But hey, if it helps you do something, that's good.

    And yeah, I had to stare at that output for several minutes before I figured out what he was actually asking for as well Jeff =).

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Ramesh (1/14/2009)


    Jeff Moden (1/14/2009)


    Ramesh (1/14/2009)


    Jeff Moden (1/14/2009)


    Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.

    Aaaah...;), That's what I've done in our payroll system....:):)

    Heh... payroll, inventory... doesn't matter. I don't understand the business rules for matching two seemingly unrelated rows.

    I guess it just like showing data in columnar manner, that you see in payslips. And also there are some not so good reporting tools (which i use) that doesn't do what it should be doing...

    Ah! Ok... I get it... the data being presented is for a single "employee" (or whatever), all the rows in the example are for a single employee, and you don't really give a hoot what the order is so long as all the deductions (for example) are on one side. Heh... that also means, this is RBAR on steriods because you don't have a decent reporting tool to do it for you and no one wants to (or can't) write an app to do it for you.

    How is the current solution working so far a performance goes? I ask because I can just see someone thinking they need a While loop to loop through each "employee" (or whatever) and that's patently not the case.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Heh... that also means, this is RBAR on steriods....

    Actually, I think its not RBAR, as its like merging two resultsets on COLUMNS...so its more like CBAR(Column-By-Agonizing-Row):hehe:

    ....because you don't have a decent reporting tool to do it for you and no one wants to (or can't) write an app to do it for you.

    And I don't have decent reporting guys too...:D:D:D

    How is the current solution working so far a performance goes? I ask because I can just see someone thinking they need a While loop to loop through each "employee" (or whatever) and that's patently not the case.

    Performance-wise, I am very satisfied (and the clients too...) with what I've done, the procedure gives me the payslips of 4000+ employees within 15-20 seconds on an average 10 executions on an average server. And I know, Jeff, you could do even better than this...:)

    --Ramesh


  • But, aren't you executing the column merge code once for each "employee"?

    And, if you're happy with the speed, then that's what matters. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It's actually much simple than you are thinking....

    here is the simplified version of it...

    SELECTEarn.RowID AS EarnRowID, Earn.SalaryHeadName AS EarnSalaryHeadName, Earn.HeadType AS EarnHeadType,

    Earn.SalaryAmount AS EarnSalaryAmount, Dedn.RowID AS DednRowID,

    Dedn.SalaryHeadName AS DednSalaryHeadName, Dedn.HeadType AS DednHeadType,

    Dedn.SalaryAmount AS DednSalaryAmount

    FROM(

    SELECTROW_NUMBER() OVER( PARTITION BY SummarySalaryID ORDER BY SummarySalaryID, Sequence, SalaryHeadID ) AS RowID,

    SummarySalaryID, SalaryHeadID, SalaryHeadCode, SalaryHeadName, SalaryHeadAlias,

    HeadType, Sequence, SalaryRate, SalaryAmount

    FROM#tmpMonthlySalaries

    WHEREPaymentType= 'Earning'

    AND ArrearPayment = 0

    AND HeadType != @strOtherPayments

    ) Earn

    FULL OUTER JOIN

    (

    SELECTROW_NUMBER() OVER( PARTITION BY SummarySalaryID ORDER BY SummarySalaryID, Sequence, SalaryHeadID ) AS RowID,

    SummarySalaryID, SalaryHeadID, SalaryHeadCode, SalaryHeadName, SalaryHeadAlias,

    HeadType, Sequence, SalaryRate, SalaryAmount

    FROM#tmpMonthlySalaries

    WHEREPaymentType= 'Deduction'

    AND ArrearPayment = 0

    AND HeadType != @strOtherPayments

    ) Dedn ON Earn.SummarySalaryID = Dedn.SummarySalaryID AND Earn.RowID = Dedn.RowID

    Though I am happy with what I've got, but still I am very much open to your suggestions...

    --Ramesh


Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply