none
(Sumber: milist DOTNET) preview hasil dari data RRS feed

  • Pertanyaan

  • salam,

    bagaimana untuk preview data menggunakan syntax sql ya dari data contoh seperti ini :
    tanggal    kode item    nama item     qty    ket   nama lokasi
    1/2/2012  A01             Mouse            1     SA   Lokasi 1
    1/2/2012  A01             Mouse            2     SA   Lokasi 2
    1/2/2012  A01             Mouse            0     SA   Lokasi 3
    1/2/2012  A02             Keyboard        5     SA   Lokasi 1
    1/2/2012  A02             Keyboard        0     SA   Lokasi 2
    1/2/2012  A02             Keyboard        2     SA   Lokasi 3
    2/2/2012  A01             Mouse            25   BL    Lokasi 1
    2/2/2012  A02             Keyboard        2     BL    Lokasi 1
    2/2/2012  A02             Keyboard        2     JL     Lokasi 1
    2/2/2012  A01             Mouse            1     JL     Lokasi 2
    3/2/2012  A01             Mouse            1     RB    Lokasi 1
    3/2/2012  A02             Keyboard        1     RJ     Lokasi 3
    NB : SA -> Saldo Awal
            JL -> Jual
            BL -> Beli
            RJ -> Retur Jual
            RB -> Retur Beli
    yang ingin saya tampilkan sebagai berikut :
    Kode Item    Nama Item    Lokasi 1      Lokasi 2    Lokasi 3
    A01             Mouse             24               1               0
    A02             Keyboard         5                 0               1


    bagaimana ya perintahnya..? sudah berulang kali coba tidak berhasil


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.05
    Moderator

Jawaban

  • Saya coba berikan contoh solusinya ya, sbnrnya saya agak bingung kode item A01 kenapa bisa 24 di lokasi 1? saya ketemunya 25, mungkin saya ada salah menentukan pengurang/penjumlah untuk kode transaksinya.

    misalkan tabel yg kamu tulis itu namanya stok.

    Cara 1: Dengan perintah PIVOT (perintah PIVOT sudah ada sejak SQL Server 2005)

    ;with temp_stok as

    (

        select kodeitem, namaitem, lokasi, sum(qty * 

                            case ket when 'SA' then 1

                                            when 'BL' then 1

                                            when 'JL' then -1

                                            when 'RB' then -1

                                            when 'RJ' then 1 end ) tqty       

            from stok

            group by kodeitem, namaitem, lokasi

    )

    select kodeitem, namaitem, [LOKASI 1], [LOKASI 2], [LOKASI 3]

    from temp_stok pivot

            (sum(tqty) 

            for lokasi in ([LOKASI 1], [LOKASI 2], [LOKASI 3])) pvt

    order by kodeitem                       

    Cara 2: Dengan aggregate function dan group by saja

    ;with temp_stok as

    (

        select kodeitem, namaitem, lokasi, sum(qty * 

                            case ket when 'SA' then 1

                                            when 'BL' then 1

                                            when 'JL' then -1

                                            when 'RB' then -1

                                            when 'RJ' then 1 end ) tqty       

            from stok

            group by kodeitem, namaitem, lokasi

    )

    select kodeitem, namaitem, sum(case lokasi when 'LOKASI 1' then tqty else 0 end) [LOKASI 1],

                                          sum(case lokasi when 'LOKASI 2' then tqty else 0 end) [LOKASI 2],

                                              sum(case lokasi when 'LOKASI 3' then tqty else 0 end) [LOKASI 3] 

    from temp_stok

    group by kodeitem, namaitem

    order by kodeitem

    Coba diotak-atik lagi, mungkin perintah sql di atas bisa disederhanakan lagi, kalau kamu ga pakai SQL Server, notasi CTE nya bisa dibuat subquery di klausa from.

    Dijawab oleh: Paulus Iman


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.07
    Moderator

Semua Balasan

  • Saya coba berikan contoh solusinya ya, sbnrnya saya agak bingung kode item A01 kenapa bisa 24 di lokasi 1? saya ketemunya 25, mungkin saya ada salah menentukan pengurang/penjumlah untuk kode transaksinya.

    misalkan tabel yg kamu tulis itu namanya stok.

    Cara 1: Dengan perintah PIVOT (perintah PIVOT sudah ada sejak SQL Server 2005)

    ;with temp_stok as

    (

        select kodeitem, namaitem, lokasi, sum(qty * 

                            case ket when 'SA' then 1

                                            when 'BL' then 1

                                            when 'JL' then -1

                                            when 'RB' then -1

                                            when 'RJ' then 1 end ) tqty       

            from stok

            group by kodeitem, namaitem, lokasi

    )

    select kodeitem, namaitem, [LOKASI 1], [LOKASI 2], [LOKASI 3]

    from temp_stok pivot

            (sum(tqty) 

            for lokasi in ([LOKASI 1], [LOKASI 2], [LOKASI 3])) pvt

    order by kodeitem                       

    Cara 2: Dengan aggregate function dan group by saja

    ;with temp_stok as

    (

        select kodeitem, namaitem, lokasi, sum(qty * 

                            case ket when 'SA' then 1

                                            when 'BL' then 1

                                            when 'JL' then -1

                                            when 'RB' then -1

                                            when 'RJ' then 1 end ) tqty       

            from stok

            group by kodeitem, namaitem, lokasi

    )

    select kodeitem, namaitem, sum(case lokasi when 'LOKASI 1' then tqty else 0 end) [LOKASI 1],

                                          sum(case lokasi when 'LOKASI 2' then tqty else 0 end) [LOKASI 2],

                                              sum(case lokasi when 'LOKASI 3' then tqty else 0 end) [LOKASI 3] 

    from temp_stok

    group by kodeitem, namaitem

    order by kodeitem

    Coba diotak-atik lagi, mungkin perintah sql di atas bisa disederhanakan lagi, kalau kamu ga pakai SQL Server, notasi CTE nya bisa dibuat subquery di klausa from.

    Dijawab oleh: Paulus Iman


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.07
    Moderator
  • Setuju dengan @paulus.iman, apakah query ( 2 alternatif query) -nya sudah meng-solve problem belum? dan seperti-nya alternatif yang saya buat juga seperti yang @paulus.iman alami.

    tambahkan alternatif query ( cross tab ):

    one-liner:

    SELECT 

            s.[Code_Item] AS KodeItem,

            s.[Name_Item] AS NameItem,

            SUM( CASE s.[Location] WHEN 'Lokasi 1' 

                    THEN ( 

                            ( CASE s.[Note] WHEN 'SA' THEN s.[Quantity] ELSE 0 END ) + 

                            ( CASE s.[Note] WHEN 'BL' THEN s.[Quantity] ELSE 0 END ) + 

                            ( CASE s.[Note] WHEN 'RJ' THEN s.[Quantity] ELSE 0 END ) - 

                            ( CASE s.[Note] WHEN 'RB' THEN s.[Quantity] ELSE 0 END ) - 

                            ( CASE s.[Note] WHEN 'JL' THEN s.[Quantity] ELSE 0 END ) 

                    ) ELSE 0 END 

            ) AS [Lokasi 1],

            SUM( CASE s.[Location] WHEN 'Lokasi 2' 

                    THEN ( 

                            ( CASE s.[Note] WHEN 'SA' THEN s.[Quantity] ELSE 0 END ) + 

                            ( CASE s.[Note] WHEN 'BL' THEN s.[Quantity] ELSE 0 END ) + 

                            ( CASE s.[Note] WHEN 'RJ' THEN s.[Quantity] ELSE 0 END ) - 

                            ( CASE s.[Note] WHEN 'RB' THEN s.[Quantity] ELSE 0 END ) - 

                            ( CASE s.[Note] WHEN 'JL' THEN s.[Quantity] ELSE 0 END ) 

                    ) ELSE 0 END 

            ) AS [Lokasi 2],

            SUM( CASE s.[Location] WHEN 'Lokasi 3' 

                    THEN ( 

                            ( CASE s.[Note] WHEN 'SA' THEN s.[Quantity] ELSE 0 END ) + 

                            ( CASE s.[Note] WHEN 'BL' THEN s.[Quantity] ELSE 0 END ) + 

                            ( CASE s.[Note] WHEN 'RJ' THEN s.[Quantity] ELSE 0 END ) - 

                            ( CASE s.[Note] WHEN 'RB' THEN s.[Quantity] ELSE 0 END ) - 

                            ( CASE s.[Note] WHEN 'JL' THEN s.[Quantity] ELSE 0 END ) 

                    ) ELSE 0 END 

            ) AS [Lokasi 3]

    FROM 

            [dbo].[TableName] AS s

    WHERE 

            ( s.[Created_Date] BETWEEN '2012-02-01' AND '2012-02-29' )

    GROUP BY

            s.[Code_Item],

            s.[Name_Item]

    ORDER BY

            s.[Code_Item]

    atau pakai cross tab dengan dual query (from sub-query)

    SELECT 

            s.[Code_Item] AS KodeItem,

            s.[Name_Item] AS NameItem,

            SUM( CASE s.[Location] WHEN 'Lokasi 1' THEN s.[total] ELSE 0 END ) AS [Lokasi 1],

            SUM( CASE s.[Location] WHEN 'Lokasi 2' THEN s.[total] ELSE 0 END ) AS [Lokasi 2],

            SUM( CASE s.[Location] WHEN 'Lokasi 3' THEN s.[total] ELSE 0 END ) AS [Lokasi 3]

    FROM 

    (

            SELECT 

                    t.[Code_Item],

                    t.[Name_Item],

                    t.[Location],

                   

                            ( CASE t.[Note] WHEN 'SA' THEN t.[Quantity] ELSE 0 END ) + 

                            ( CASE t.[Note] WHEN 'BL' THEN t.[Quantity] ELSE 0 END ) + 

                            ( CASE t.[Note] WHEN 'RJ' THEN t.[Quantity] ELSE 0 END ) - 

                            ( CASE t.[Note] WHEN 'RB' THEN t.[Quantity] ELSE 0 END ) - 

                            ( CASE t.[Note] WHEN 'JL' THEN t.[Quantity] ELSE 0 END ) 

                    ) AS total

            FROM 

                    [dbo].[TableName] AS t

            WHERE 

                    ( t.[Created_Date] BETWEEN '2012-02-01' AND '2012-02-29' )

            GROUP BY

                    t.[Code_Item],

                    t.[Name_Item],

                    t.[Location],

                    t.[Note],

                    t.[Quantity]

    ) AS s

    GROUP BY

            s.[Code_Item],

            s.[Name_Item]

    ORDER BY

            s.[Code_Item]

    Bagaimana apakah query-query tersebut sudah menjawab?

    Dijawab oleh: dede


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.09
    Moderator
  • hmm..
    agak binggung juga kalo ada penambahan lokasi lagi mau rombak lagi codingnya? :(


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.09
    Moderator
  • Kalau lokasinya bisa bertambah secara dinamis, sepertinya tidak bisa pakai ad-hoc query, kemungkinan bisa pakai stored procedure yg logicnya kamu buat sedemikian rupa sehingga bisa mengenerate pivot table spt yang kamu inginkan, but it will make your hands dirty with code :D

    Cara paling mudah, hasilnya ditampilkan langsung di report (misalnya dng SSRS / Crystal Report). Kalau pakai SSRS bisa pakai objek Matrix, atau di Crystal Report bisa pakai Crosstab.

    Dijawab oleh: Paulus Iman


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.10
    Moderator
  • Cara umum dalam TSQL adalah membuat-nya Dynamic Query.

    rasanya banyak referensi untuk itu (walaupun tak mimik 100%) dengan kasus anda.

    coba cari dahulu di  duckduckgo google atau search engine lainnya.

    karena agak panjang kalau bikin sample-nya. :)

    Dijawab oleh: dede


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.10
    Moderator
  • jadi kalo misalnya ingin tampilkan masing-masing summary berdasarkan ket bagaimana ya?
    misalnya seperti ini:


    tanggal    kode item    nama item     qty    ket  
    1/2/2012  A01             Mouse           1     SA  
    1/2/2012  A02             Keyboard       5     SA  
    2/2/2012  A01             Mouse         25     BL   
    2/2/2012  A02             Keyboard       2     BL  
    2/2/2012  A02             Keyboard       2     JL    
    2/2/2012  A01             Mouse           1     JL  
    3/2/2012  A01             Mouse           1     RB   
    3/2/2012  A02             Keyboard       1     RJ   
    4/2/2012  A01             Mouse           5     BL  
    4/2/2012  A02             keyboard        2     JL  
    NB : SA -> Saldo Awal
            JL -> Jual
            BL -> Beli
            RJ -> Retur Jual
            RB -> Retur Beli
    kode item    nama item    SA   BL   JL  RB  RJ
    A001         Mouse            1     30    1    1    0
    A002         Keyboard        5       2    4    0    1

    bagaimana ya syntax nya?


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.11
    Moderator
  • Sebetulnya saya agak berharap anda memberikan effort lebih dahulu, sampai di mana query yang sudah anda buat. Coba lihat ke http://sqlfiddle.com/#!3/4b9d8/13

    Dijawab oleh: dede


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.11
    Moderator
  • Betul apa yg dibilang @dede, kalau anda mengerti penjelasan query "pivot" yg sblmnya ditanyakan, mestinya cara tsb juga bisa untuk mensolve mslh yg saat ini.

    Dijawab oleh: Paulus Iman


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 April 2012 09.12
    Moderator