lawang code

lawang Code


Tanyakan kode disini

Belajar SQL Query di SQL Server

oleh handita okviyanto 5 tahun yang lalu

Kode Snippet SQL Query di SQL Server


SELECT DATA

SELECT

SELECT * FROM TableAnda
SELECT * FROM TableAnda WHERE FieldA='1' AND FieldB=2 




INSERT DATA

 INSERT INTO Table(Field1,Field2,..,FieldN) VALUES (Value1,'Value2',...'ValueN');
 INSERT INTO TableA(Field1,Field2,Field3) SELECT Field1,Field2,Field3 FROM TableB




UPDATE DATA

UPDATE

UPDATE NamaTable SET FieldA='1', FieldB=2 WHERE FieldC='Value'

UPDATE dengan CASE

UPDATE vkbp2014_dsp SET kategori=
            CASE 
                WHEN     (estrata='HT14') THEN 1
                WHEN     (estrata='RS14') THEN 2
                WHEN     (estrata='CT14') THEN 3
                WHEN     (estrata='LP14') THEN 4
                WHEN     (estrata='RT14') THEN 5
                ELSE 0
             END

UPDATE dengan JOIN

UPDATE
    a
SET
    a.FieldA= b.FieldB
FROM
    TabelA a
INNER JOIN
    TabelB b
ON 
   a.FieldA1=b.FieldB1
   AND a.FieldA2=b.FieldB2
  WHERE a.FieldA3=1




HAPUS DATA

DELETE

DELETE FROM NamaTable WHERE ID='1'




JOIN data

SELECT * FROM TableA a JOIN TableB b ON a.ID=b.ID
SELECT * FROM TableA a INNER JOIN Table b ON a.ID=b.ID
SELECT * FROM TableA a LEFT JOIN Table b ON a.ID=b.ID
SELECT * FROM TableA a RIGHT JOIN Table b ON a.ID=b.ID
SELECT * FROM TableA a FULL JOIN Table b ON a.ID=b.ID

JOIN Dengan nilai NULL

 SELECT * FROM TableA a JOIN Table b ON ((a.Field1=b.Field1) OR (a.Field1 IS NULL AND b.Field1 IS NULL));
 SELECT * FROM TableA a JOIN Table b ON (coalesce(a.Field1, '') = coalesce(b.Field1, ''));




NUMBERING

INSERT ROW NUMBER

INSERT INTO Table1(Column1) SELECT ROW_NUMBER() OVER (ORDER BY Column2)




TRANSPOSE KOLOM MENJADI BARIS DAN SEBALIKNYA

Mengubah Kolom menjadi baris dan sebaliknya menggunakan PIVOT dan UNPIVOT

Persiapan datanya sebagai berikut :

  CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);

INSERT INTO yourTable
    ([color], [Paul], [John], [Tim], [Eric])
VALUES
    ('Red', 1, 5, 1, 3),
    ('Green', 8, 4, 3, 5),
    ('Blue', 2, 2, 9, 1);

Kemudian Query Transposenya sebagai berikut :

 select name, [Red], [Green], [Blue]
from
(
  select color, name, value
  from yourtable
  unpivot
  (
    value for name in (Paul, John, Tim, Eric)
  ) unpiv
) src
pivot
(
  sum(value)
  for color in ([Red], [Green], [Blue])
) piv

Bisa juga menggunakan query biasa

select name,
  sum(case when color = 'Red' then value else 0 end) Red,
  sum(case when color = 'Green' then value else 0 end) Green,
  sum(case when color = 'Blue' then value else 0 end) Blue
from
(
  select color, Paul value, 'Paul' name
  from yourTable
  union all
  select color, John value, 'John' name
  from yourTable
  union all
  select color, Tim value, 'Tim' name
  from yourTable
  union all
  select color, Eric value, 'Eric' name
  from yourTable
) src
group by name






SUBTOTAL

Menambahkan Total data di baris paling terakhir

SELECT ISNULL(Jumlah,'Total') Field,* FROM Table GROUP BY ROLLUP(Jumlah) 




CONTOH COMPLEX QUERY

Contoh query yang memakai SELECT,GROUP, JOIN, UNION, PIVOT, CASE, UNPIVOT, ROLLUP

Query ini menggabungkan tabel dari beberapa table yaitu SampelSHPM,SampelSIND,SampelSLP dan SampelSRS dengan berbagai kondisi

SELECT *,HOTEL+RESTORAN+WARUNGMAKAN+KEDAIMAKAN+KAFE+CATERING+MINUMKELILING+RS+LAPAS+MANUFAKTUR+RESTORANPND+CATERINGPND as Total  FROM 
(
    SELECT ISNULL(komoditi,'Total') Komoditi,ISNULL(SUM(HOTEL),0) as HOTEL,ISNULL(SUM(RESTORAN),0) as RESTORAN,
        ISNULL(SUM(WARUNGMAKAN),0) AS WARUNGMAKAN   ,ISNULL(SUM(KEDAIMAKAN),0) AS KEDAIMAKAN,ISNULL(SUM(KAFE),0) AS KAFE,ISNULL(SUM(CATERING),0) as CATERING,ISNULL(SUM(MINUMKELILING),0) as MINUMKELILING,
        ISNULL(SUM(RS),0) AS RS,ISNULL(SUM(LAPAS),0) AS LAPAS,ISNULL(SUM(MANUFAKTUR),0) as MANUFAKTUR,ISNULL(SUM(RESTORANPND),0) AS RESTORANPND,ISNULL(SUM(CATERINGPND),0) AS CATERINGPND
    FROM
    (
        SELECT *
        FROM
        (
                SELECT 
                    CASE WHEN TipeUsaha=1 THEN 'HOTEL'
                    WHEN TipeUsaha=2 THEN 'RESTORAN'
                    WHEN TipeUsaha=3 THEN 'WARUNGMAKAN'
                    WHEN TipeUsaha=4 THEN 'KEDAIMAKAN'
                    WHEN TipeUsaha=5 THEN 'KAFE'
                    WHEN TipeUsaha=6 THEN 'CATERING'
                    WHEN TipeUsaha=7 THEN 'MINUMKELILING'
                    WHEN TipeUsaha=8 THEN 'RS'
                    WHEN TipeUsaha=9 THEN 'LAPAS'
                    WHEN TipeUsaha=10 THEN 'MANUFAKTUR'
                    WHEN TipeUsaha=11 THEN 'RESTORANPND'
                    WHEN TipeUsaha=11 THEN 'CATERINGPND'
                    ELSE 'LAIN'
                    END AS TipeUsaha
                ,
                    SUM(Beras) as Beras,
                    SUM(Jagung) as Jagung,
                    SUM(TepungTerigu) as TepungTerigu,
                    SUM(DagingSapi) as DagingSapi,
                    SUM(DagingAyam) as DagingAyam,
                    SUM(BawangMerah) as BawangMerah,
                    SUM(BawangPutih) as BawangPutih,
                    SUM(Cabe) as Cabe,
                    SUM(KacangKedelai) as KacangKedelai,
                    SUM(MinyakGoreng) as MinyakGoreng,
                    SUM(GulaPasir) as GulaPasir,
                    SUM(Garam) as Garam

                FROM 
                (
                    SELECT
                        CASE 
                            WHEN (B3R1=2 AND kategori=2) THEN 11 --RESTORAN PND
                            WHEN (B3R1=6 AND kategori=2) THEN 12 --CATERING
                            ELSE B3R1
                        END AS
                        TipeUsaha,
                        CASE WHEN B3R2_1=1 THEN 1 ELSE 0 END AS Beras,
                        CASE WHEN B3R2_2=3 THEN 1 ELSE 0 END AS Jagung,
                        CASE WHEN B3R2_3=5 THEN 1 ELSE 0 END AS TepungTerigu,
                        CASE WHEN B3R2_4=7 THEN 1 ELSE 0 END AS DagingSapi,
                        CASE WHEN B3R2_5=1 THEN 1 ELSE 0 END AS DagingAyam,
                        CASE WHEN B3R2_6=3 THEN 1 ELSE 0 END AS BawangMerah,
                        CASE WHEN B3R2_7=5 THEN 1 ELSE 0 END  AS BawangPutih,
                        CASE WHEN B3R2_8=7 THEN 1 ELSE 0 END AS Cabe,
                        CASE WHEN B3R2_9=1 THEN 1 ELSE 0 END AS KacangKedelai,
                        CASE WHEN B3R2_10=3 THEN 1 ELSE 0 END AS MinyakGoreng,
                        CASE WHEN B3R2_11=5 THEN 1 ELSE 0 END AS GulaPasir,
                        CASE WHEN B3R2_12=7 THEN 1 ELSE 0 END AS Garam
                    FROM
                        SampelKategoriData a
                    JOIN SampelSHPM b ON a.ID = 'H' + CAST(b.id_sampel AS VARCHAR)
                    JOIN IDentitasSHPM c ON b.id_sampel = c.id_sampel_fk
                    WHERE b.prop+b.kab='1208'


                    UNION

                    -- SAMPEL SIND
                    SELECT
                            10,-- B3R1 AS TipeUsaha, Manufaktur
                            CASE WHEN B3R4_1=1 THEN 1 ELSE 0 END AS Beras,
                            CASE WHEN B3R4_2=3 THEN 1 ELSE 0 END AS Jagung,
                            CASE WHEN B3R4_3=5 THEN 1 ELSE 0 END AS TepungTerigu,
                            CASE WHEN B3R4_4=7 THEN 1 ELSE 0 END AS DagingSapi,
                            CASE WHEN B3R4_5=1 THEN 1 ELSE 0 END AS DagingAyam,
                            CASE WHEN B3R4_6=3 THEN 1 ELSE 0 END AS BawangMerah,
                            CASE WHEN B3R4_7=5 THEN 1 ELSE 0 END  AS BawangPutih,
                            CASE WHEN B3R4_8=7 THEN 1 ELSE 0 END AS Cabe,
                            CASE WHEN B3R4_9=1 THEN 1 ELSE 0 END AS KacangKedelai,
                            CASE WHEN B3R4_10=3 THEN 1 ELSE 0 END AS MinyakGoreng,
                            CASE WHEN B3R4_11=5 THEN 1 ELSE 0 END AS GulaPasir,
                            CASE WHEN B3R4_12=7 THEN 1 ELSE 0 END AS Garam
                    FROM
                        SampelKategoriData a
                    JOIN SampelSIND b ON a.ID = 'I' + CAST(b.id_sampel AS VARCHAR)
                    JOIN IdentitasSIND c ON b.id_sampel = c.id_sampel_fk
                    WHERE b.prop+b.kab='1208'


                    UNION

                 --- SAMPEL SRS
                    SELECT
                        8,--8 itu RS
                        CASE WHEN B3R2_1=1 THEN 1 ELSE 0 END AS Beras,
                        CASE WHEN B3R2_2=3 THEN 1 ELSE 0 END AS Jagung,
                        CASE WHEN B3R2_3=5 THEN 1 ELSE 0 END AS TepungTerigu,
                        CASE WHEN B3R2_4=7 THEN 1 ELSE 0 END AS DagingSapi,
                        CASE WHEN B3R2_5=1 THEN 1 ELSE 0 END AS DagingAyam,
                        CASE WHEN B3R2_6=3 THEN 1 ELSE 0 END AS BawangMerah,
                        CASE WHEN B3R2_7=5 THEN 1 ELSE 0 END  AS BawangPutih,
                        CASE WHEN B3R2_8=7 THEN 1 ELSE 0 END AS Cabe,
                        CASE WHEN B3R2_9=1 THEN 1 ELSE 0 END AS KacangKedelai,
                        CASE WHEN B3R2_10=3 THEN 1 ELSE 0 END AS MinyakGoreng,
                        CASE WHEN B3R2_11=5 THEN 1 ELSE 0 END AS GulaPasir,
                        CASE WHEN B3R2_12=7 THEN 1 ELSE 0 END AS Garam
                    FROM
                        SampelKategoriData a
                    JOIN SampelSRS b ON a.ID = 'H' + CAST(b.id_sampel AS VARCHAR)
                    JOIN IDentitasSRS c ON b.id_sampel = c.id_sampel_fk
                    WHERE b.prop+b.kab='1208'

                    UNION 

                    --LAPAS
                    SELECT
                        9,--B3R1 AS TipeUsaha, 9 itu lapas
                        CASE WHEN B3R2_1=1 THEN 1 ELSE 0 END AS Beras,
                        CASE WHEN B3R2_2=3 THEN 1 ELSE 0 END AS Jagung,
                        CASE WHEN B3R2_3=5 THEN 1 ELSE 0 END AS TepungTerigu,
                        CASE WHEN B3R2_4=7 THEN 1 ELSE 0 END AS DagingSapi,
                        CASE WHEN B3R2_5=1 THEN 1 ELSE 0 END AS DagingAyam,
                        CASE WHEN B3R2_6=3 THEN 1 ELSE 0 END AS BawangMerah,
                        CASE WHEN B3R2_7=5 THEN 1 ELSE 0 END  AS BawangPutih,
                        CASE WHEN B3R2_8=7 THEN 1 ELSE 0 END AS Cabe,
                        CASE WHEN B3R2_9=1 THEN 1 ELSE 0 END AS KacangKedelai,
                        CASE WHEN B3R2_10=3 THEN 1 ELSE 0 END AS MinyakGoreng,
                        CASE WHEN B3R2_11=5 THEN 1 ELSE 0 END AS GulaPasir,
                        CASE WHEN B3R2_12=7 THEN 1 ELSE 0 END AS Garam
                    FROM
                        SampelKategoriData a
                    JOIN SampelSLP b ON a.ID = 'H' + CAST(b.id_sampel AS VARCHAR)
                    JOIN IDentitasSLP c ON b.id_sampel = c.id_sampel_fk
                    WHERE b.prop+b.kab='1208'


                ) gabung
                GROUP BY TipeUsaha
        ) as S
        UNPIVOT (
                VAL FOR komoditi IN (Beras,Jagung,TepungTerigu,DagingSapi,DagingAyam,BawangMerah,BawangPutih,Cabe,KacangKedelai,MinyakGoreng,GulaPasir,Garam)
        ) as UNPIV
    ) SRC
    PIVOT (
            SUM(VAL)
            FOR TipeUsaha IN (HOTEL,RESTORAN,WARUNGMAKAN,KEDAIMAKAN,KAFE,CATERING,MINUMKELILING,RS,LAPAS,MANUFAKTUR,RESTORANPND,CATERINGPND)
    )PIV
    GROUP BY ROLLUP(komoditi)
) Gab


CREATE TABLE IF NOT EXIST

CREATE TABLE IF NOT EXIST tidak ada di SQL Server nah sebagai pengganti script ini bisa gunain script ini

if not exists (select * from sysobjects where name='GantiNamaKRTDenganPengusaha' and xtype='U') 
 BEGIN

CREATE TABLE [dbo].[GantiNamaKRTDenganPengusaha] (
    [IDSampel] varchar(255) NULL ,
    [NamaPengusaha] varchar(255) NULL 
)
    END


CONTOH KOMPLEKS QUERY 2

SELECT 

       TABLE_JUM.[id_sampel_SHPM]
      ,SHPM_umum.[prop]
      ,SHPM_umum.[kab]
      ,SHPM_umum.[kec]
      ,SHPM_umum.[desa]
      ,SHPM_umum.[daerah]
      ,SHPM_umum.[NBS]
      ,SHPM_umum.[NKS]
      ,SHPM_umum.[nus]
      ,TABLE_JUM.[status]
      ,SHPM_umum.[nama_perusahaan_master]
      ,SHPM_umum.[alamat_master]
      ,SHPM_umum.[kegiatan_utama]
      ,SHPM_umum.[kode_kbli]
      ,SHPM_umum.[hasil_pencacahan] 
      ,SHPM_umum.[flag]     
      ,SHPM_umum.[nama_pengusaha] as nama_pengusaha_entrian
      ,SHPM_umum.[nama_usaha] as nama_usaha_entrian
      ,SHPM_umum.[alamat_usaha] as alamat_usaha_entrian
      ,SHPM_umum.[kodepos]
      ,SHPM_umum.[telepon]
      ,SHPM_umum.[faks]
      ,SHPM_umum.[tgl_pencacah]
      ,SHPM_umum.[tgl_pengawas]
      ,SHPM_umum.[B3R1]
      ,SHPM_umum.[B3R2_1]
      ,SHPM_umum.[B3R2_2]
      ,SHPM_umum.[B3R2_3]
      ,SHPM_umum.[B3R2_4]
      ,SHPM_umum.[B3R2_5]
      ,SHPM_umum.[B3R2_6]
      ,SHPM_umum.[B3R2_7]
      ,SHPM_umum.[B3R2_8]
      ,SHPM_umum.[B3R2_9]
      ,SHPM_umum.[B3R2_10]
      ,SHPM_umum.[B3R2_11]
      ,SHPM_umum.[B3R2_12]
      ,SHPM_umum.[B3R4]

      --,cast(round(TABLE_JUM.[1_Jumlah_Bahan_Digunakan],2) as numeric(36,2))

      ,TABLE_JUM.[1_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[2_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[3_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[4_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[5_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[6_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[7_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[8_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[9_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[10_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[11_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[12_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[13_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[14_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[15_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[16_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[17_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[18_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[19_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[20_Jumlah_Bahan_Digunakan] 
      ,TABLE_JUM.[22_Jumlah_Bahan_Digunakan] as [21_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[23_Jumlah_Bahan_Digunakan] as [22_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[24_Jumlah_Bahan_Digunakan] as [23_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[25_Jumlah_Bahan_Digunakan] as [24_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[26_Jumlah_Bahan_Digunakan] as [25_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[27_Jumlah_Bahan_Digunakan] as [26_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[30_Jumlah_Bahan_Digunakan] as [27_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[33_Jumlah_Bahan_Digunakan] as [28_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[34_Jumlah_Bahan_Digunakan] as [29_Jumlah_Bahan_Digunakan]
      ,TABLE_JUM.[35_Juml




SELECT metadata dari field

SELECT column_name,data_type,
character_maximum_length as max_length
FROM information_schema.columns
WHERE table_name = 'RTDetail' 



Contoh query metadata

SELECT max_length as a,MaxLength as b,
* FROM
(
    SELECT column_name,data_type,
    character_maximum_length as max_length
    FROM information_schema.columns
    WHERE table_name = 'ARTDetail' 
    AND DATA_TYPE LIKE '%char%'

    UNION

    SELECT column_name,data_type,
    character_maximum_length as max_length
    FROM information_schema.columns
    WHERE table_name = 'RTDetail' 
    AND DATA_TYPE LIKE '%char%'


) a
JOIN 
Metadata_KOR b ON a.column_name=b.Field
WHERE a.max_length<b.MaxLength


SELECT max_length as a,MaxLength as b,
* FROM
(
    SELECT column_name,data_type,
    character_maximum_length as max_length
    FROM information_schema.columns
    WHERE table_name = 'ARTDetail' 
) a
JOIN 
Metadata_KOR b ON a.column_name=b.Field
WHERE a.max_length<b.MaxLength



GROUPING

var d = listErrorKonsistensi.GroupBy(x => new { x.Prop, x.Kab, x.NKS, x.Nurt, x.NoART, x.Type, x.Semester, x.ErrorID }).Where(grp => grp.Count() > 1).SelectMany(grp => grp.Select(r => r)).ToList();

sqlserver sql
Anda harus login terlebih dahulu untuk memberikan komentar

Recent Tag

X 5
X 6
X 1
X 21
X 2
X 3
X 1
X 255
X 1

Bantuan

Help

Feedback

Help