Aku mau berbagi ilmu lagi nich mas bro,
mumpung aku inget ya...
Bagaimana caranya mengubah kolom menjadi baris di SQL Server :)
Gini....ni..cara ngebuatnya mas bro :)
Langkahnya :
Buka SQL SERVER Editor, lalu Copy Paste Script dibawah ini mas bro
--Membuat table temporary
create table #Temp_Table
(
id_customer int,
book varchar(64),
price int,
qty int,
tahun_terbit int
)
insert into #Temp_Table values ('1','SQL Backup','1200000','10','2016')
insert into #Temp_Table values ('2','Hosting SQL','1500000','20','2016')
insert into #Temp_Table values ('3','SQL Server Training','2000000','15','2016')
insert into #Temp_Table values ('4','Database Administrator','1000000','22','2016')
insert into #Temp_Table values ('5','Cloud Database','130000','31','2017')
insert into #Temp_Table values ('6','Cloud Database 1','130000','31','2017')
insert into #Temp_Table values ('7','Cloud Database 2','130000','31','2018')
insert into #Temp_Table values ('8','Cloud Database 3','130000','31','2018')
insert into #Temp_Table values ('9','Cloud Database 4','130000','31','2018')
insert into #Temp_Table values ('10','Cloud Database 5','130000','31','2018')
--Deklarasikan variabel-variabel yang diperlukan
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
--mendapatkan kolom yang akan di jadikan kunci pada pivot column
SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(tahun_terbit)
FROM (SELECT DISTINCT tahun_terbit FROM [dbo].[#Temp_Table]) AS PivotExample
--Membuat Query dinamis dengan semua nilai untuk kolom pivot pada waktu proses
SET @SQLQuery =
N'SELECT DISTINCT id_customer,book,price, ' + @PivotColumns + '
FROM [dbo].[#Temp_Table]
PIVOT( sum(qty)
FOR tahun_terbit IN (' + @PivotColumns + ')) AS P'
--Jalankan Query Dinamis yang tadi sudah di jabarkan
EXEC sp_executesql @SQLQuery
Aku mau berbagi ilmu lagi nich mas bro,
mumpung aku inget ya...
Bagaimana caranya Looping List Data didalam Database SQL Server :)
Sering kita binggung bagaimana memecah data dari yang berbentuk list menjadi data perbaris di SQL Server, contohnya :
Isi Data
X = Darwan,Leo,Nardo,Sitepu,
Kita inginkan menjadi :
Darwan
Leo
Nardo
Sitepu
Gini....ni..cara ngebuatnya mas bro :)
Langkahnya :
Buka SQL SERVER Editor, lalu Copy Paste Script dibawah ini mas bro
DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)
SET @valueList = 'Darwan,Leo,Nardo,Sitepu,'
set @pos = 0
set @len = 0
WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
set @value = SUBSTRING(@valueList, @pos, @len)
PRINT @value
set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END
Aku mau berbagi ilmu lagi nich mas bro,
mumpung aku inget ya...
Bagaimana mencari Nama Kolom atau Nama Field didalam Database SQL Server :)
Sering kita binggung bagaimana mencari Nama Field atau Nama Kolom di dalam sebuah Database, apalagi database yang akan kita cari tabelnya buanyak buanget ya, hehehehe.
Disini saya ada sedikit tips atau info, bagaimana mencari Nama Kolom atau Field didalam database sql server jika kita hanya tahu, nama kolomnya akan tetapi tidak inget nama tabelnya.
Yuk mulai yuk......
Cara Kesatu :
Mencari Nama Tabel dari kolom yang kita mau
Jika kita ingat nama kolom / filednya, kita bisa menggunakan Query dibawah ini :
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'Nama Kolom' )
Ini Hasilnya :
Atau
Jika kita tidak ingat nama kolom / fieldnya, kita bisa menggunakan Query dibawah ini :
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%Nama Kolom%' )
Ini Hasilnya :
Cara Kedua :
Mencari Nama Tabel beserta detail nama kolom / fieldnya, kita bisa menggunakan Query dibawah ini :
select sysobjects.name AS NAMA_TABEL, syscolumns.name AS NAMA_KOLOM
from syscolumns
left join sysobjects on sysobjects.id = syscolumns.id
where syscolumns.name like '%user%' and sysobjects.xtype ='u'
order by sysobjects.name
Dimana hasil Backup Database terdahulu menggunakan ekstensi zip dan disini saya akan memberikan script ataupun fungsi restore database dari file zip ke Database yang kita inginkan.
Langkah Pertama
Masukkan Source Code Dibawah ini kedalam editor yang mas bro punya.
Lalu Simpan Source Code ini dengan nama file yang berakhiran ".cfm" misalkan "RestoreDatabase.cfm" kedalam direktori wwwroot Coldfusion yang sudah terinstal.
Langkah Kedua
Masukkan Source Code Dibawah ini kedalam editor yang mas bro punya (Masih didalam file "RestoreDatabase.cfm").
<cfoutput>
<!DOCTYPE html">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import Database Tables</title>
</head>
<body>
<h1>Restore Data</h1>
<!---
Restore a database dari zip file.
#RestoreDatabase("dbsource","DbFile","Action")#
@param dbsource Datasource (DNS). (Required)
@param DbFile Lokasi File dari backup database (dalam zip file).
@ param Action tindakan yang akan dilakukan (view = berfungsi untuk menampilkan isi dari Backup Database file yang ada, dan restore = untuk melakukan restore data dari data yang sudah dipilih)
@author Darwan Leonardo Sitepu (dlns2001@yahoo.com)
@version 1, February 21, 2011
--->
#RestoreDatabase("darwanlns.com","c:\ColdFusion9\wwwroot\BackupDatabase.zip","view")#
<cfif StructKeyExists(form, "tablename")>
#RestoreDatabase("darwanlns.com","c:\ColdFusion9\wwwroot\BackupDatabase.zip","restore")#
</cfif>
</body>
</html>
</cfoutput>
Keterangan : darwanlns.com = Nama Datasource yang sudah dibuat di Coldfusion c:\ColdFusion9\wwwroot\BackupDatabase.zip = Alamat dari file Database yang akan direstore view = Menampilkan Isi dari database yang terdapat di zip file restore = Melakukan restore database dari pilihan yang sudah dilakukan.
Langkah Ketiga :
Untuk Menjalankan Source code yang sudah mas bro punya, mas bro ketikkan saja alamat di browser mas bro misalkan.
http://localhost:8500/RestoreDatabase.cfm
Maka Contoh Hasil dari source code diatas akan seperti ini
Hei Mas Bro,
Ketemu lagi nich....
Disini aku mau bagi-bagi ilmu, tentang bagaimana caranya membuat SQL Editor Database lengkap dengan struktur tablenya berbasis web menggunakan bahasa pemrograman Coldfusion,
Mungkin saya pernah juga merilis tulisan ini Membuat Query Editor Berbasis Web Dengan Coldfusion,
tapi disini beda mas bro, disini saya menambahkan beberapa fitur lainnya, mungkin mas bro bisa lihat dari gambar dibawah ini
Setelah Mas Bro download file diatas, mas bro langsung extrak saja ke dalam folder direktori "wwwroot" coldfusion,
Lalu tinggal di jalankan deh mas bro,
misalkan : http://localhost:8500/DatabaseExplorerSistem/
Hey Mas Bro, ketemu lagi nich....
Mas Bro, aku mau bagi-bagi pengetahuan nich, tentang bagaimana caranya mengupdate data dari 2 Table yang berbeda.
Begini...nich caranya :
UPDATE dbo.[Tabel Tujuan] SET [Field Table Tujuan] = dbo.[Table Sumber].[Field Table Sumber] FROM dbo.[Table Sumber] WHERE dbo[Tabel Tujuan].[Field Kunci Tujuan]=dbo.[Table Sumber].[Field Kunci Sumber]
Contohnya dari penerapan Query Diatas adalah :
UPDATE dbo.DT_SO
SET TOTAL_PENGELUARAN=dbo.DT_TRANSAKSI.TOTAL_PENGELUARAN
FROM dbo.DT_TRANSAKSI
atau
UPDATE dbo.DT_SO
SET TOTAL_PENGELUARAN=dbo.DT_TRANSAKSI.TOTAL_PENGELUARAN ,PENGHASILAN_KONSUMEN = dbo.DT_TRANSAKSI.PENGHASILAN_KONSUMEN
FROM dbo.DT_TRANSAKSI
atau
UPDATE dbo.DT_SO
SET TOTAL_PENGELUARAN=dbo.DT_TRANSAKSI.TOTAL_PENGELUARAN ,PENGHASILAN_KONSUMEN = dbo.DT_TRANSAKSI.PENGHASILAN_KONSUMEN
FROM dbo.DT_TRANSAKSI
WHERE dbo.DT_SO.ID_SO=dbo.DT_TRANSAKSI.ID_SO
Untuk Mengetahui Hasilnya Silahkan Dicoba y Mas Bro.
Ciank mas bro,
Mas Bro, aku mau bagi-bagi pengetahuan nich, tentang bagaimana caranya menghapus Log Database di SQL server,
Soalnya terkadang Log Database yang kita punya, bisa lebih besar berkali-kali lipat dari Data masternya,
jadi kalau kita tidak menghapus log datanya, maka lognya bisa ngebengkak mas bro.
Misalkan Database "dbDepkes.mdf" saya besarnya hanya 1 GB, tapi setelah saya lihat Log Databasenya "dbDepkes_log.ldf" besarnya bisa mencapai 476 GB.
Jadi Kalau begini caranya Hardisk Ane bisa langsung Abis nich mas bro....
Gini Nich cara menghapus Log Database SQL Server mas bro,
Langkah 1 :
* Mas Bro Buka SQL Server Management Studio Mas Bro,
* Lalu mas bro Klik Kanan Pada database yang dipilih.
* Lalu Pilih Task
* Lalu Pilih Detach
Lihat Gambar Dibawah ini
* Jika Ada Koneksi yag sedang aktif, Checklist Drop Connection, lalu tekan Ok Untuk Menutup Database yang sedang aktif
Langkah 2 :
Masuk Ke lokasi Folder Database yang ada di komputer mas bro,
Misalkan Ada di "D:\Data\Database", Lalu Rename File "dbDepkes_log.ldf" menjadi "dbDepkes_log_lama.ldf".
Langkah 3 :
Pada SQL Server Management Studio, Mas Bro Klik Kanan, Lalu Pilih Attach.
Setelah Itu Ikuti mas bro Klik tombol "Add",
Lalu Pilih Database yang yang mas bro mau, misalkan database "dbDepkes.mdf", Lalu Tekan OK
Lalu mas bro Klik pada File "dbDepkes.LDF" atau pada File Type yang tipenya adalah Log, Setelah mas bro klik, Lalu Tekan Tombol Remove.
Setelah Log Databasenya di hapus / remove sama mas bro, maka SQL Server akan secara otomatis membuat Log database baru setelah mas bro, menekan tombol OK.
Langkah 4 :
Setelah itu mas bro masuk ke lokasi Folder Database berada, lalu mas bro hapus Log database yang tadi sudah kita rubah namanya "dbDepkes_log_lama.ldf".
Nah Gitu dech mas bro, cara untuk menghapus data Log yang ada dan menggantinya dengan Data Log baru.
Mas Bro, aku punya sedikit bocoran ni, bagaimana caranya membuat Nilai terbilang mata uang rupiah di SQL SERVER,
Gini....ni..cara ngebuatnya mas bro :)
Langkah Pertama :
Buka SQL SERVER Editor, lalu Copy Paste Script dibawah ini mas bro
CREATE FUNCTION [DBO].[TerbilangDetil] (@Nilai as decimal)
RETURNS varchar(200) AS
BEGIN
declare @TS varchar(200), @ST varchar(200), @S integer, @p integer, @r1 integer, @Bil varchar(200), @TP varchar(200), @hasil varchar(200)
set @ST = Right(rTrim(Str(@Nilai + 1000)), 3)
set @S = cast(Right(@ST, 1) as integer)
set @p = cast(substring(@ST, 2, 1) as integer)
set @r1 = cast(Left(@ST, 1) as integer)
set @Bil = 'Satu Dua Tiga Empat Lima '
set @Bil = @Bil + 'Enam Tujuh Delapan Sembilan'
If @S = 0
set @TS = ''
Else
set @TS = rTrim(substring(@Bil, @S * 8 - 7, 8)) + ' '
If @p = 0
set @TP = @TS
Else
If @p = 1
If @S = 0
set @TP = 'Sepuluh '
Else
If @S = 1
set @TP = 'Sebelas '
Else
set @TP = rTrim(substring(@Bil, @S * 8 - 7, 8)) + ' Belas '
Else
set @TP = rTrim(substring(@Bil, @p * 8 - 7, 8)) + ' Puluh ' + @TS
If @r1 = 0
set @hasil = @TP
Else
If @r1 = 1
set @hasil = 'Seratus ' + @TP
Else
set @hasil = rTrim(substring(@Bil, @r1 * 8 - 7, 8)) + ' Ratus ' + @TP
return @hasil
END
Lalu Jalankan Script Diatas dengan menekan F5 atau !
Setelah Itu, Copy Paste lagi Script dibawah ini mas bro
CREATE FUNCTION [dbo].[FungsiTerbilang] (@angka as decimal(19,2))
RETURNS varchar (200) AS
BEGIN
Declare @b Decimal, @r Decimal, @J Decimal, @M Decimal, @T1 Decimal, @koma1 Decimal, @koma2 Integer, @hasil varchar(200)
Declare @s_t varchar(200), @ribu varchar(200), @Juta varchar(200), @Miliar varchar(200), @tx varchar(200), @angka1 varchar(200), @tx1 varchar(200)
set @s_t = case when Len(rTrim(cast(cast(@Angka as bigint) as varchar(20)))) < 15
then replicate('0',15 - Len(rTrim(cast(cast(@Angka as bigint) as varchar(20))))) + rTrim(cast(cast(@Angka as bigint) as varchar(20)))
else rTrim(cast(cast(@Angka as bigint) as varchar(20)))
end
set @r = cast(Right(@s_t, 3) as integer)
set @b = cast(substring(@s_t, 10, 3) as integer)
set @J = cast(substring(@s_t, 7, 3) as integer)
set @M = cast(substring(@s_t, 4, 3) as integer)
set @T1 = cast(Left(@s_t, 3) as integer)
set @ribu = case when @b = 0 then ' ' else case when @b = 1 then 'Seribu' else [dbo].TerbilangDetil(@b) + 'Ribu' end end
set @Juta = case when @J = 0 then ' ' else [dbo].TerbilangDetil(@J) + 'Juta ' end
set @Miliar = case when @M = 0 then ' ' else [dbo].TerbilangDetil(@M) + 'Miliar' end
set @tx = Case
when @Angka < 1000 -- Ratusan
then [dbo].TerbilangDetil(@r)
when @Angka < 1000000 -- Ribuan
then @ribu + ' ' + [dbo].TerbilangDetil(@r)
when @Angka < 1000000000 -- Jutaan
then [dbo].TerbilangDetil(@J) + '' + rTrim('Juta ' + @ribu) + ' ' + [dbo].TerbilangDetil(@r)
when @Angka < 1000000000000 -- Miliaran
then [dbo].TerbilangDetil(@M) + '' + rTrim('Miliar ' + @Juta + @ribu) + ' ' + [dbo].TerbilangDetil(@r)
when @Angka >= 1000000000000 -- Trilliun
then [dbo].TerbilangDetil(@T1) + '' + rTrim('Trilliun ' + @Miliar + @Juta + @ribu) + ' ' + [dbo].TerbilangDetil(@r)
End
set @angka1 = rTrim(cast(@Angka as varchar(20)))
set @koma1 = case when charindex('.', @angka1) = 0 then 0 else cast(rTrim(substring(@angka1, charindex('.',@angka1) + 1, 2)) as integer) end
set @koma2 = Len(case when charindex('.',@angka1) = 0 then '0' else rTrim(substring(@angka1, charindex('.',@angka1) + 1, 2)) end)
If @koma1 > 0
begin
If @koma1 < 10 And @koma2 = 1
set @koma1 = @koma1 * 10
set @tx1 = [dbo].TerbilangDetil(@koma1)
set @hasil = @tx + 'koma ' + @tx1 + 'Rupiah'
end
Else
set @hasil = @tx + 'Rupiah'
return (@hasil)
END
Lalu Jalankan Script Diatas dengan menekan F5 atau ! Langkah Kedua :
Untuk mengetestnya, mas bro bisa mebuat Query seperti dibawah ini
Query :
select dbo.FungsiTerbilang('9750.35')
Hasil :
Sembilan Ribu Tujuh Ratus Lima Puluh koma Tiga Puluh Lima Rupiah
