Bisakah kita mengekspor hasil kueri sql ke excel?

saya punya barang-barang di bawah ini dan saya menulis setiap hasil di lembar excel. saya punya masalah untuk menulis di lembar excel dari setiap hasil sql

jatuhkan meja #meja_Northwind
buat tabel #table_Northwind
(

RowID int bukan identitas nol (1,1) kunci utama,
kolom_nama varchar(50)

)

masukkan ke #table_Northwind (column_name)
(PILIH TABLE_NAME
DARI INFORMASI_SCHEMA. TABEL
di mana TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )

--pilih * dari #table_Northwind
deklarasikan @Bluemchen _name nvarchar(50)
nyatakan @i int
pilih @i = min(RowID) dari #table_Northwind
nyatakan @Max int
pilih @Max = max(RowID) dari #table_Northwind

sementara @i <= @Max mulai

SELECT  (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name,  [Scehma]=schema_name(o.schema_id), o.type   

DARI sistem. sql_modules m
INNER JOIN sistem. objek o
AKTIF o. object_id = m. object_id
DI MANA m. definisi seperti +'%'+ (pilih nama_kolom dari #table_Northwind di mana RowID=@i) +'%'

atur @i = @i + 1
akhir

Pada artikel sebelumnya, Belajar SQL. Tabel Pivot SQL Server, kami telah membahas cara membuat laporan menggunakan kueri tabel PIVOT. Kami telah membuat laporan seperti itu langsung di SQL Server. Dalam artikel ini, kami akan melakukan hal yang sama, tetapi kali ini kami akan menggunakan ekspor SQL Server ke Excel. Jadi ayo mulai

Model data dan ide umum

Model data yang akan kami gunakan sama dengan yang kami gunakan di sepanjang seri ini. Bahkan kueri yang digunakan adalah yang telah kami analisis di artikel sebelumnya dan oleh karena itu saya tidak akan menjelaskannya secara mendalam di sini. Jika Anda melewatkan artikel sebelumnya, saya sangat menyarankan untuk melihat cara kerja kueri ini

Tujuan dari artikel ini adalah untuk menunjukkan bagaimana kita bisa menggabungkan data yang dikembalikan menggunakan kueri SQL dan ekspor SQL Server ke Excel. Kombinasi ini terbukti sangat kuat. Salah satu alasan utamanya adalah MS Excel menyediakan banyak opsi tambahan seperti grafik, perhitungan, hasil pemformatan. Juga, Anda bisa menggunakan satu buku kerja Excel "master" di mana Anda akan menggabungkan data dari berbagai sumber, jauh lebih mudah daripada yang bisa Anda lakukan jika Anda membaca data ini dari sumber asli. Dan, perlu disebutkan, bahwa Excel tersebar luas dan orang biasanya lebih mengenalnya daripada database

Ekspor SQL Server ke Excel – Menggunakan PIVOT Query

Kami ingin membuat laporan yang berisi daftar semua kota dari database kami. Untuk setiap kota, kita perlu mengetahui sejumlah panggilan yang dikelompokkan berdasarkan hasilnya

Untuk mencapainya, kami akan menggunakan kueri terakhir dari artikel sebelumnya. Kueri ini akan selalu menampilkan semua kombinasi kota dan hasil panggilan (kami telah menggunakan CROSS JOIN untuk membuat kategori pelaporan). Selain itu, kami dapat melakukan pivot berdasarkan hasil (nama), karena kueri kami bersifat dinamis (kami menambahkan hasil ke bagian pivot kueri berdasarkan konten kamus saat ini). Oleh karena itu, kueri kami akan berfungsi di semua kasus – bahkan jika kami menambahkan hasil baru ke kamus

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

-- 1 -- laporan (termasuk PIVOT dinamis)

MENYATAKAN

    @kolom NVARCHAR(MAX) = '',

    @kueri NVARCHAR(MAX) = '';

-- dapatkan semua nama kolom untuk tabel yang kita perlukan untuk pivot

PILIH

    @kolom += QUOTENAME(TRIM(co.outcome_text)) + '

DARI

    call_outcome co

PESAN OLEH

    co. hasil_teks;

-- hapus "," dari akhir string

SET @kolom = LEFT(@columns, LEN(@columns) - 1);

-- kueri SQL dinamis

SET @kueri ='

PILIH * DARI

(

PILIH

-- rc. kota_id,

rc. nama Kota,

-- rc. panggilan_hasil_id,

rc. hasil_teks,

rd. durasi panggilan

DARI

(

PILIH

c. id AS city_id,

c. nama Kota,

bersama. id AS call_outcome_id,

bersama. hasil_teks

DARI call_outcome co

CROSS JOIN kota c

) rc

GABUNG KIRI

(

PILIH

ci. id AS city_id,

bersama. id AS call_outcome_id,

DATEDIFF(SECOND, c. waktu_mulai, c. end_time) SEBAGAI durasi_panggilan

DARI panggilan c

INNER JOIN call_outcome co ON c. call_outcome_id = co. Indo

INNER JOIN pelanggan cu ON c. customer_id = cu. Indo

INNER JOIN kota ci ON cu. kota_id = ci. Indo

) rd AKTIF rc. id_kota = rd. city_id DAN rc. call_outcome_id = rd. call_outcome_id

) laporan_data

POROS(

COUNT(durasi_panggilan)

    UNTUK teks_hasil DI ('+ @kolom +')

) AS pivot_table;';

-- jalankan kueri dinamis

JALANKAN sp_executesql @permintaan;

Mari kita lihat hasil kueri sekarang. Seperti yang diharapkan, hasilnya adalah tabel pivot dengan semua data yang kita butuhkan. Sekarang saatnya menggunakan ekspor SQL Server ke Excel

SQL Server adalah alat yang hebat dan memiliki banyak tambahan yang dapat Anda gunakan untuk tujuan tertentu. Namun, salah satu opsi yang sering digunakan adalah cukup menyalin hasil kueri SQL Server ke MS Excel dan membuat transformasi tambahan yang diinginkan di sana. Alasannya karena MS Excel menyediakan berbagai pilihan untuk menyajikan data. Beberapa yang paling umum digunakan adalah tabel, tabel pivot, berbagai jenis grafik, dll. Selain itu, Anda dapat menggunakan buku kerja MS Excel sebagai tempat sentral di mana Anda akan mengumpulkan data dari berbagai sumber (berdasarkan format atau teknologi yang berbeda) dan menggabungkannya untuk mendapatkan wawasan menyeluruh tentang bisnis Anda. Satu hal lagi yang perlu disebutkan di sini adalah MS Excel tersebar luas dan orang-orang terbiasa bekerja dengannya. Saya tidak memiliki statistik aktual yang terkait dengan penggunaan SQL Server dan MS Excel, dan saya kira akan sulit untuk memiliki angka aktual, jadi saya akan menggunakan estimasi yang tersedia secara online. Sekitar 750 juta orang di seluruh dunia menggunakan Excel, sedangkan pada tahun 2019 kami memiliki sekitar 26 orang. 5 juta pengembang (pengembang basis data adalah sebagian kecil dari jumlah itu)

Setelah Anda menjalankan kueri, Anda cukup memilih seluruh hasil kueri dengan mengklik di sudut kiri atas (di atas nomor baris dan di kiri nama kolom). Hal selanjutnya yang harus dilakukan adalah memilih opsi "Salin dengan tajuk" dari daftar tarik-turun

Kami telah menempelkan hasil kueri di lembar kerja MS Excel. Dalam contoh kehidupan nyata, kita bisa memiliki lebih banyak pemformatan daripada hanya menerapkan huruf tebal pada kategori. Grafik di bawah tabel dengan data disiapkan dan diformat sehingga akan berubah sesuai perubahan data

Ada beberapa hal lagi yang bisa kami lakukan secara berbeda di sini

• Kami dapat menempelkan data (tabel pivot) di lembar kerja terpisah dan memiliki lembar kerja "presentasi" yang diformat sebagai tambahan
• Jika kami memiliki detail lebih lanjut, kami dapat menyiapkan beberapa grafik berbeda, serta menghitung parameter yang relevan dengan bisnis kami

Saya telah menggunakan pendekatan ini pada data dunia nyata karena memungkinkan Anda membuat dasbor yang sangat kompleks setelah menjalankan beberapa kueri. Tentu saja, Anda akan menghabiskan banyak waktu untuk menyiapkan dasbor (dan menyesuaikannya dengan perubahan persyaratan pelaporan), tetapi begitu Anda melakukannya, Anda akan mendapat banyak keuntungan setiap kali Anda perlu membuat laporan baru

Dan lebih baik lagi, ini tidak hanya terbatas pada ekspor SQL Server ke kombinasi Excel, tetapi Anda dapat mengekstrak data dari berbagai sumber (DBMS lain, file, teks…) dan menggunakan dasbor Anda sebagai tempat sentral untuk mendapatkan wawasan keseluruhan

Ekspor SQL Server ke Excel – Tanpa Permintaan PIVOT

Tujuan akhir dari tugas kita adalah membuat tabel pivot serta grafik untuk merepresentasikan hasilnya secara visual. Pendekatan di bagian sebelumnya adalah membuat kueri tabel PIVOT secara langsung di SQL dan menempelkan kueri ini ke Excel. Ada (setidaknya) 4 alasan bagus ketika Anda tidak selalu dapat menggunakan pendekatan ini

• Anda telah menyiapkan kueri SQL dan tidak ingin membuang waktu tambahan untuk mengubah kueri ini menjadi kueri tabel PIVOT, melainkan menghabiskan waktu itu untuk memanipulasi hasilnya di Excel
• Mungkin Anda tidak menggunakan SQL Server (dan MS Excel) dan DBMS Anda tidak mendukung kueri tabel PIVOT (dan kueri dinamis yang kami butuhkan)
• Anda ingin memiliki data dalam format mentah karena Anda juga memerlukannya untuk tujuan lain. Mungkin Anda akan membuat lebih dari 1 tabel pivot dari data yang dikembalikan. Mungkin Anda akan menggabungkan data tersebut dengan data lain secara langsung di Excel
• Anda tidak tahu cara membuat kueri tabel PIVOT di SQL Server, tetapi Anda tahu cara melakukannya di MS Excel. Oke, ini bukan alasan, tapi tetap saja, ini adalah kasus umum di kehidupan nyata

Sekarang mari kita lihat apa yang bisa dilakukan jika kita menggunakan opsi bahwa kita tidak menggunakan kueri pivot SQL Server. Sekali lagi kami akan menggunakan kueri yang dijelaskan di artikel sebelumnya. Ini hampir sama dengan kueri sebelumnya, dengan perbedaan utama bahwa kami tidak memiliki bagian PIVOT dari kueri tersebut

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

-- 2 -- laporan kategori & data (without pivot)

PILIH

    rc. city_id,

    rc. nama_kota,

    rc. call_outcome_id,

    rc. hasil_teks,

    rd. durasi_panggilan

DARI

(

    PILIH

        c. id AS city_id,

        c. nama_kota,

        co. id AS call_outcome_id,

        co. hasil_teks

    DARI call_outcome co

    LINTAS GABUNG kota c

) rc

KIRI GABUNG

(

    PILIH

        ci. id AS city_id,

        co. id AS call_outcome_id,

        DATEDIFF(DETIK, c.waktu_mulai, c. end_time) AS call_duration

    DARI telepon c

    INNER GABUNG call_outcome co ON c.call_outcome_id = co. id

    INNER GABUNG pelanggan cu ON c.customer_id = cu. id

    INNER GABUNG kota ci ON cu.city_id = ci. id

) rd AKTIF rc. city_id = rd. city_id DAN rc. call_outcome_id = rd. call_outcome_id

PESAN OLEH

    rc. city_id,

    rc. call_outcome_id;

Langkah pertama selesai. Kami telah berhasil mengembalikan semua data yang kami butuhkan. Hasil kami berisi semua kombinasi kota dan kemungkinan hasil (kami telah menggunakan CROSS JOIN) dan satu baris per setiap panggilan (NULL saat kami tidak memiliki panggilan)

Sekarang kami siap membuat tabel PIVOT langsung di Excel. Setelah mengklik "Sisipkan" -> "Tabel Pivot" (di Daftar Tab dan Pita) akan muncul jendela seperti pada gambar di atas. Kami akan "Pilih tabel atau rentang" yang berisi data dan lokasi tempat kami ingin meletakkan tabel pivot kami (dalam kasus kami adalah "Lembar Kerja yang Ada", mulai dari sel G2)

Hal berikutnya yang kami lakukan adalah menentukan apa yang terjadi di mana. Nama kota akan menjadi "Baris" dari tabel kami, teks hasil akan masuk ke "Kolom" dan kami akan menghitung durasi panggilan ("Nilai"). Harap perhatikan bahwa di tabel asli (output), saya telah mengganti nilai NULL di kolom E, dengan spasi kosong. Ini penting karena fungsi hitung akan menghitung 1 jika baris berisi nilai yang tidak kosong

Hal terakhir yang akan kita lakukan adalah membuat grafik. Sumber data untuk grafik kita adalah tabel pivot yang dibuat pada langkah sebelumnya. Kami akan menerapkan pemformatan ke grafik kami (gaya grafik, label, sumbu, dll. ), dan kita selesai

Pendekatan ini dapat diterapkan secara lebih umum dibandingkan dengan pendekatan ketika tabel pivot dibuat langsung di SQL Server. Dan itu saja – kami telah menggunakan ekspor SQL Server ke Excel untuk menarik data dari database dan membuat dasbor sederhana

Kesimpulan

Hari ini kita telah melihat bagaimana kita dapat menggabungkan kekuatan ekspor SQL Server ke Excel – menggunakan kueri SQL untuk mengambil data, mengimpornya ke MS Excel, dan membuat perubahan tambahan di sana. Ini adalah sesuatu yang dapat Anda lakukan dengan mudah untuk berbagai keperluan dan batasannya hanyalah imajinasi Anda (dan, tentu saja, kebutuhan bisnis). Langkah selanjutnya adalah mengotomatiskan ekspor tabel atau grafik yang dibuat di MS Excel, langsung ke Word atau PowerPoint

Bagaimana cara mengekspor hasil kueri SQL ke Excel secara otomatis?

Bagaimana cara mentransfer data dari SQL ke Excel?

Bagaimana cara mengekspor hasil kueri SQL ke CSV?