Menggabungkan isi field menjadi satu String di MS SQL 2005 April 21, 2010
Posted by layyuddi in SQL.Tags: alternative concatenate, Menggabungkan isi field
add a comment
Ketika kita membutuhkan data dari isi kolom menjadi satu string. Kita bisa menggunakan sintak for xml.
Contohnya saya akan menggabungkan isi dari field name pada sys.databases :
select Name from sys.databases where database_id > 4
| Name |
| Temp |
| Liquibase |
| NorthWind |
| EMPLOYEES |
Dengan query berikut kita bisa menggabungkan nama tersebut :
select
stuff(
(select ', ' + name -- Note the lack of column name
from sys.databases
where database_id > 4
order by name
for xml path('')
)
, 1, 2, '') as Namelist;
| Namelist |
| EMPLOYEES, Liquibase, NorthWind, Temp |
Bagaimana jika kita ingin menambahkan tanda kurung siku pada setiap nama seperti berikut: <EMPLOYEES>, <Liquibase>, <NorthWind>, <Temp>
Jika kita menggunakan syntax seperti diatas :
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path('')
)
, 1, 2, '') as namelist;
Hasilnya tanda < dan > muncul menjadi < dan > :
| namelist |
| <EMPLOYEES>, <Liquibase>, <NorthWind>, <Temp> |
Untuk mengatasi itu, kita bisa menambahkan sedikit syntak forxml nya. Berikut querynya :
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 1, 2, '') as namelist;
atau
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), type
).value('(./text())[1]','varchar(max)')
, 1, 2, '') as namelist
Hasilnya :
| namelist |
| <EMPLOYEES>, <Liquibase>, <NorthWind>, <Temp> |
Query Ranking dalam MS SQL Server 2005 April 20, 2010
Posted by layyuddi in SQL.Tags: MS SQL Server 2005, Query Ranking
add a comment
Ranking atau peringkat, manusia senang membuat ranking. Dalam MS SQL Server 2005 pun tersedia beberapa fungsi untuk menampilkan hasil query dengan nomor ranking. Untuk contoh-contoh query ranking saya menggunakan Entity Relationship berikut :
Saya membuat view berikut untuk menjumlahkan SubTotal dari setiap order ID :
CREATE VIEW [dbo].[viewSumSubTotalPerOrder]
AS
SELECT OrderID, SUM(SubTotal) AS Total
FROM dbo.OrderItems
GROUP BY OrderID
Untuk menampilkan query ranking, kita bisa menggunakan query ini :
SELECT c.Name, o.DateOrdered, o.orderId, vw.Total,
ROW_NUMBER() OVER (ORDER BY Total DESC) AS BestCustomer
FROM viewSumSubTotalPerOrder AS vw
INNER JOIN Orders AS o ON
o.OrderID = vw.OrderID
INNER JOIN Customers AS c ON
c.CustomerID = o.CustomerID
Akan menghasilkan :
| Name | DateOrdered | orderId | Total | BestCustomer |
| Kurniawan | 00:00.0 | 11 | 450000 | 1 |
| Slamet | 00:00.0 | 6 | 240000 | 2 |
| Kurniawan | 00:00.0 | 10 | 220000 | 3 |
| Rangky | 00:00.0 | 3 | 100000 | 4 |
| Firman | 00:00.0 | 8 | 60000 | 5 |
| Rangky | 00:00.0 | 1 | 45000 | 6 |
| Rico | 00:00.0 | 2 | 45000 | 7 |
| Slamet | 00:00.0 | 5 | 40000 | 8 |
| Fajar | 00:00.0 | 7 | 30000 | 9 |
| Firman | 00:00.0 | 9 | 15000 | 10 |
| Rico | 00:00.0 | 4 | 2000 | 11 |
Yang ini query ranking yang dibagi-bagi berdasarkan nama customernya :
SELECT c.Name, o.DateOrdered, o.orderId, vw.Total,
ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY Total DESC) AS BestCustomer
FROM viewSumSubTotalPerOrder AS vw
INNER JOIN Orders AS o ON
o.OrderID = vw.OrderID
INNER JOIN Customers AS c ON
c.CustomerID = o.CustomerID
Menghasilkan :
| Name | DateOrdered | orderId | Total | BestCustomer |
| Rico | 00:00.0 | 2 | 45000 | 1 |
| Rico | 00:00.0 | 4 | 2000 | 2 |
| Rangky | 00:00.0 | 3 | 100000 | 1 |
| Rangky | 00:00.0 | 1 | 45000 | 2 |
| Fajar | 00:00.0 | 7 | 30000 | 1 |
| Slamet | 00:00.0 | 6 | 240000 | 1 |
| Slamet | 00:00.0 | 5 | 40000 | 2 |
| Firman | 00:00.0 | 8 | 60000 | 1 |
| Firman | 00:00.0 | 9 | 15000 | 2 |
| Kurniawan | 00:00.0 | 11 | 450000 | 1 |
| Kurniawan | 00:00.0 | 10 | 220000 | 2 |
Tapi kurangnya adalah, hasil ranking ini tidak bisa di masukan didalam kondisi “where” ataupun didalam expresi “order by”. Untuk melakukan itu kita harus memasukan dulu ketable terpisah atau masukannya keview.
Berikut contoh querynya :
CREATE VIEW [dbo].[viewBestCustomers] AS
SELECT c.Name, o.DateOrdered, o.orderId, vw.Total,
ROW_NUMBER() OVER (ORDER BY Total DESC) AS BestCustomer
FROM viewSumSubTotalPerOrder AS vw
INNER JOIN Orders AS o ON
o.OrderID = vw.OrderID
INNER JOIN Customers AS c ON
c.CustomerID = o.CustomerID
SELECT Name, DateOrdered, orderId, Total, BestCustomer
FROM viewBestCustomers
WHERE BestCustomer BETWEEN 3 AND 5
| Name | DateOrdered | orderId | Total | BestCustomer |
| Kurniawan | 00:00.0 | 10 | 220000 | 3 |
| Rangky | 00:00.0 | 3 | 100000 | 4 |
| Firman | 00:00.0 | 8 | 60000 | 5 |
Semua hasil diatas dihasilkan dengan syntax ROW_NUMBER(), selanjutnya saya akan menampilkan perbedaan dengan menggunakan syntax RANK() dan DENSE_RANK :
Menggunakan RANK() :
SELECT c.Name, o.DateOrdered, vw.Total,
RANK() OVER (ORDER BY Total DESC) AS BestCustomer
FROM viewSumSubTotalPerOrder AS vw
INNER JOIN Orders AS o ON
o.OrderID = vw.OrderID
INNER JOIN Customers AS c ON
c.CustomerID = o.CustomerID
Menghasilkan :
| Name | DateOrdered | Total | BestCustomer |
| Kurniawan | 00:00.0 | 450000 | 1 |
| Slamet | 00:00.0 | 240000 | 2 |
| Kurniawan | 00:00.0 | 220000 | 3 |
| Rangky | 00:00.0 | 100000 | 4 |
| Firman | 00:00.0 | 60000 | 5 |
| Rangky | 00:00.0 | 45000 | 6 |
| Rico | 00:00.0 | 45000 | 6 |
| Slamet | 00:00.0 | 40000 | 8 |
| Fajar | 00:00.0 | 30000 | 9 |
| Firman | 00:00.0 | 15000 | 10 |
| Rico | 00:00.0 | 2000 | 11 |
Menggunakan DENSE_RANK :
SELECT c.Name, o.DateOrdered, vw.Total,
DENSE_RANK() OVER (ORDER BY Total DESC) AS BestCustomer
FROM viewSumSubTotalPerOrder AS vw
INNER JOIN Orders AS o ON
o.OrderID = vw.OrderID
INNER JOIN Customers AS c ON
c.CustomerID = o.CustomerID
Menghasilkan :
| Name | DateOrdered | Total | BestCustomer |
| Kurniawan | 00:00.0 | 450000 | 1 |
| Slamet | 00:00.0 | 240000 | 2 |
| Kurniawan | 00:00.0 | 220000 | 3 |
| Rangky | 00:00.0 | 100000 | 4 |
| Firman | 00:00.0 | 60000 | 5 |
| Rangky | 00:00.0 | 45000 | 6 |
| Rico | 00:00.0 | 45000 | 6 |
| Slamet | 00:00.0 | 40000 | 7 |
| Fajar | 00:00.0 | 30000 | 8 |
| Firman | 00:00.0 | 15000 | 9 |
| Rico | 00:00.0 | 2000 | 10 |
Yang terakhir adalah, query untuk membagi hasil ranking berdasarkan berapa grup yang kita mau :
SELECT ProductID, Name, Price, NTILE(4) OVER (ORDER BY Price DESC) as Quartile
FROM Products
Menghasilkan :
| ProductID | Name | Price | Quartile |
| 3 | Table | 100000 | 1 |
| 6 | Shoes | 40000 | 1 |
| 2 | Chair | 30000 | 2 |
| 5 | Bag | 20000 | 2 |
| 7 | Hat | 15000 | 3 |
| 4 | Pen | 2000 | 3 |
| 1 | Book | 1500 | 4 |
| 8 | Pencil | 500 | 4 |
Bagi yang mau mencoba-coba, saya sertakan juga data mentahnya disini. File yang saya upload berextensi .doc, ubah saja menjadi .sql.
Semoga tutorial sederhana ini membantu dalam pembuatan report, atau query sederhana yang anda inginkan.
Mencari nama Stored Procedure atau Mencari Text didalam SP April 14, 2010
Posted by layyuddi in SQL.Tags: Mencari Stored Procedure, SQL, Stored Procedure
add a comment
Saya biasa membuat satu SP buat mencari nama SP atau mencari text didalam SP tersebut. Berikut SP yang saya buat :
1. Find_SP
CREATE PROCEDURE Find_SP
@StringToSearch varchar(100)
AS
SET @StringToSearch = ‘%’ + @StringToSearch + ‘%’
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = ‘P’
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name
2. Find_Text_In_SP
CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100)
AS
SET @StringToSearch = ‘%’ +@StringToSearch + ‘%’
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = ‘P’
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
