Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    17

    Thumbs up Unanswered: Please Help On This Query

    I have Tabel With this Structure :

    create table Temp (
    Number nvarchar(20),
    BranchCode nvarchar(20),
    Transdate datetime,
    total money,
    kode_product nvarchar(20)
    branchname nvarchar(5)
    )

    Number BranchCode TransDate Total Kode_Product BranchName
    LPWP 2009 OBLP 1-Feb-09 360,000.00 TMFBA BALIKPAPAN
    LPWP 2009 OBLP 1-Feb-09 360,000.00 TNTPE BALIKPAPAN
    LPWP 2009 OBLP 1-Feb-09 460,000.00 TMFBA BALIKPAPAN
    LPWP 2009 OBLP 1-Feb-09 460,000.00 TMXGA BALIKPAPAN
    LPWP 2009 OBLP 1-Feb-09 920,000.00 TNTPE BALIKPAPAN
    LPWP 2009 OBLP 2-Feb-09 260,000.00 LKPXC BALIKPAPAN
    LPWP 2009 OBLP 2-Feb-09 260,000.00 LWPEE BALIKPAPAN

    I want to query so the latest result view like this

    Number BranchCode TMFBA TNTPE TMXGA LKPXC LWPEE
    LPWP 2009 OBLP 820,000.00 1,280,000.00 460,000.00 260,000.00 260,000.00

    which mean on code_product being coloumn on this result area,and the row is sum(total)

    Anyone help me?

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    try this if u r using 2005 and above
    Code:
    select number,branchcode, max([TMFBA]), max([TNTPE]), max([TMXGA]), max([LKPXC]), max([LWPEE])
    from @temp
    pivot (sum(total) for kode_product in ( [TMFBA], [TNTPE], [TMXGA], [LKPXC], [LWPEE])) pvt
    group by number,branchcode
    Last edited by bklr; 03-04-09 at 01:01.

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    if ur using 2000 then use dynamic cross tab
    select number,branchcode,
    sum(case when kode_product ='TMFBA' then total end) as TMFBA,
    sum(case when kode_product ='TNTPE' then total end) as TNTPE,
    sum(case when kode_product ='TMXGA' then total end) as TMXGA,
    sum(case when kode_product ='LKPXC' then total end) as LKPXC,
    sum(case when kode_product ='LWPEE' then total end) as LWPEE
    from @temp
    group by number,branchcode

  4. #4
    Join Date
    Feb 2009
    Posts
    17

    Thumbs up Thanks bklr

    Again and again very thanks to bklr, you r always helpfull anyway how i can help you 2 payback your kindness...let my pray for you...and thanks again with all my heart....success and wish you always luck..u r always shining again and again...and always...thanks CASE CLOSED

Posting Permissions

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