Pivot Table Nedir Nasil Kullanilir
Pivot table bir tablodaki verileri yatay olarak göstermemizi saglar yani satirlarda bulunan bilgileri istedigimiz alanlara göre sutunlara çevirir. Özellikle rapor hazirlarken verileri daha anlasilir ve düzenli hale getirmemizi saglar. Örnegin bir siparis tablomuz var. Bir müsterinin verdigi her bir siparis bir satirda gösteriliyor. Her müsteri için son 6 aylik siparis bilgilerini görmek istiyoruz. Birinci yöntem her hareketi alt alta yazmak olabilir ama daha anlasilir ve düzenli bir rapor için her müsteri bir satirda yer almali ve bu satirda son alti aya ait tüm siparis tutarlarinin toplami gösterilmelidir. Bu durumda pivot table kullanmak imdadimiza yetisir. Bu örnegin daha iyi anlasilmasi için küçük bir çalisma yapalim.
Ilk olarak basit bir tablo yapalim ve içine asagidaki örnek verileri atalim.
CREATE TABLE [dbo].[Siparis](
[SiparisID] [int] IDENTITY(1,1) NOT NULL,
[MusteriAdSoyad] [varchar](80) NULL,
[UrunAdi] [varchar](50) NULL,
[Tutar] [decimal](10, 2) NULL,
[Donem] [int] NULL
) ON [PRIMARY]
| SiparisID | MusteriAdSoyad | UrunAdi | TutarDonem |
|---|---|---|---|
| 1 | Sabri KUNT | Apple IPAD | 1025201001 |
| 2 | Ali DEMIR | LG Netbook | 950200912 |
| 3 | Metin TOSUN | PacketBell Notbook | 1200200911 |
| 4 | Sabri KUNT | Nikon D5000 | 1480200912 |
| 5 | Sabri KUNT | Microsoft Mouse | 100200912 |
| 6 | Metin TOSUN | HP Printer | 250200912 |
| 7 | Metin TOSUN | HP Server | 2100200911 |
| 8 | Ali DEMIR | HP Pavilion | 1600200912 |
| 9 | Ali DEMIR | ADSL Modem | 80200911 |
Simdi müsteri basina aylik olarak ne kadar harcama yapildigini görmek için bir sorgu yazalim.
SELECT
MusteriAdSoyad
,Donem
,sum(Tutar) as ToplamTutar
FROM Siparis
group by MusteriAdSoyad ,Donem
Yukaridaki sorgu sonucu asagidaki gibi her donem için müsterilerin kaç liralik harcama yaptiklarini görürüz.
| MusteriAdSoyad | Donem | ToplamTutar |
|---|---|---|
| Ali DEMIR | 200911 | 80 |
| Metin TOSUN | 200911 | 3300 |
| Ali DEMIR | 200912 | 2550 |
| Metin TOSUN | 200912 | 250 |
| Sabri KUNT | 200912 | 1580 |
| Sabri KUNT | 201001 | 1025 |
Peki, bizim binlerce müsterimiz olsa ve toplam 1 yil boyunca her ay ne kadarlik harcamala yaptiklarini görmek istersek yukaridaki gibi bir sorgunun sonucunda içinden çikamayacagimiz büyük bir tablomuz olurdu. Her müsteri için aylik harcama miktarlarini farkli satirlarda göstermek hiç de isimize gelmezdi.
Simdi ise her müsteriyi tek bir satirda gösterelim ve siparis verdikleri her ay içinde bir kolon açarak bunun altinda toplam siparis tutarlarini gösterelim. Pivot table için Siparis tablosunu gruplayarak kullanmamiz gerekiyor. Çünkü siparis tablosunda her ay için müsteri bazinda birden fazla harcama var. Yukarida yaptigimiz gruplamayi yeni bir tablo olarak alip bunu pivot yaparsak istedigimiz sonuca ulasabiliriz.
SELECT *
FROM (
SELECT
MusteriAdSoyad
,Donem
,sum(Tutar) as ToplamTutar
FROM Siparis
group by MusteriAdSoyad ,Donem
) as gTablo
PIVOT
(
SUM(ToplamTutar)
FOR Donem IN ([200911],[200912],[201001])
)
AS p
Yukaridaki sorgu sonucu asagidaki gibi her ay bir kolon olacak seklide bilgilerimiz yatayda dizildi
| MusteriAdSoyad | 200911 | 200912 |
|---|---|---|
| Ali DEMIR | 80 | 2550 |
| Metin TOSUN | 3300 | 250 |
| Sabri KUNT | NULL | 1580 |
Gördügünüz gibi bu seklide bir tablo olusturmak sonucun daha düzenli olmasini sagladigi gibi incelenmesini kolaylastirir.
ekran göruntusu :
CREATE TABLE #Dikey
Merhaba,
Aslında bir sorum olacak. Elimdeki tabloda müşteri numaraları ve telefon numarası sütunları bulunmakta. Bir telefon numarasına ait birden fazla müşteri numarası mevcut. Telefon numarası tekil olacak şekilde müşteri numaralarını sütunlarda nasıl gösterebilirim?
Örnek olarak;
şeklindeki tabloyu;
şeklinde sorgulamak istiyorum.
Alttaki sorguyu da örnek olarak byrakyyorum.
Kolon adlary için, IsNull([200911], 0) As ‘Donem_200911’ ?eklindeki yöntemle yeni bir ad tanymlayabilirsiniz.
geç olmamy?tyr diye umuyorum ama kolon isimleri derken galiba 2011,2012 kolonlaryna spesifik bir isim vermekten bahsediyorsunuz, e?er onu soruyorsanyz parantez içindeki select sorgusunda dönem için yyllary case when ile isimlendirebilirsiniz sonra a?a?yda for kysmynda 2011,2012 yazmak yerine sizin yazmy? oldu?unuz kolon isimlerini yazarsynyz.
kolon ismini tyklady?ynda, yukaryda fx yanyndaki kysymda de?i?iklik yapynca oluyor, sizde aktif olmadymy?
Kolon isimlerini özelle?tirebilen var my?
bende baktim ama bir çözüm yok gibi. ama ilk firsatta microsoftdan gelen arkadaslara bir sorarim.
Olmuyor bosuna kasmaya gerek yok, deneyecekler varsa bi yenilik gelmeden mümkün görünmüyor..
Gecen surede bir cozume ulasirsaniz sizde paylasirsaniz cok sevinirim, ilginize bir kez daha tesekkur ederim.
Bu arada ufak bir not: elde etmek istedigim ciktiyi MySql tarafinda Contact li bir fonksiyonla alabiliyorum sanirim ama ya ben uzun zamandir bununla ugrasiyorum bir muadilini bulamadim o kadar aramama ragmen yada ben buldugum ornekleri kendime uyarlayamadim yaklasik 2 haftadir bunun uzerine kafa patlatiyorum gercekten saka gibi ms sql de boyle birseyin yapilamiyor olmasi.
Selam dostum,
Onerdigin sekilde cikti alabiliyorum fakat benim almak istedigim sonuc ayri kolonlarda olmasi. Procedure de yazdim temp’e de attim stringi de split ettim fakat cok veri oldugu icin split olayi pek mantikli gelmiyor character character okuyup parse ettirdim fakat soyle bir sorun oldu,
onerdigin sonucu alip parse ettikten sonra temp ‘e atiyorum fakat bu temp ile join yapmam gerekiyor baska tablolarlada
yani bu uclemeninin yannida baska bir tablo ile join gerekiyor. Son olarak birde 2 tablonunu baglantisini tutan 3. Kategori_AltKategori tablosunu pivot ile yatay hale getirip oyle bu uclemeyi joinlemeye calisicam basarabilirsem burada da sonucu paylasirim.
Yardimci olabilecek yok mu acaba?
bu arada senin gonderdigin scripte table variable kullanmistin, ben temp table kullandim. o kismi degistirip denersen sonucu sende görebilirsin
asagidaki sorgu su sonucu çikarir.
id kategoriadi altkategoriler
———– ———– ————————————
1 Bilgisayar hdd,cpu,cpu,memory
2 Beyaz Esya buz dolabi,bulasik makinesi
(2 row(s) affected)
alt kategorileri kolon kolon degilde, tek bir kolonda birlestirebilirsin. zaten alt kategori sayin sabitte olmayacaktir. bu durumda kac kolon olusacak bilemezsin.
su sorgu isene yararmi:
select id,k.deger as kategoriadi, STUFF(
(
SELECT ‘,’ + t2.deger AS [text()]
from #kategori_altKategori t1
inner join #alt_kategori t2 on t1.altKategoriID = t2.id
where k.id = t1.kategoriID
Order by t2.id
for xml PATH(”)),1,1,”) AS altkategoriler
from #kategori k
hemen bakiyorum
Yardimci olabilecek yok mu acaba?
Merhaba @msk,
Elimdeki data kumesi asagidaki gibidir;
insert into @kategori_altKategori(id,kategoriID,altKategoriID) values (2,1,2)
insert into @kategori_altKategori(id,kategoriID,altKategoriID) values (3,1,3)
insert into @kategori_altKategori(id,kategoriID,altKategoriID) values (4,2,4)
insert into @kategori_altKategori(id,kategoriID,altKategoriID) values (5,2,5)
Almak istedigim cikti
ID kategoriAdi AltKategoriAdi_1 AltKategoriAdi_2 .. AltKategoriAdi_n..
1 Bilgisayar hdd cpu Altkategori..N
Seklindedir.
Merhaba @msk,
Elimdeki data kumesi asagidaki gibidir;
declare @kategori table (id int,deger varchar(10))
insert into @kategori (id,deger) values (1,’Bilgisayar’)
insert into @kategori (id,deger) values (2,’Beyaz Esya’)
declare @alt_kategori table (id int,deger varchar(20))
insert into @alt_kategori (id,deger) values (1,’hdd’)
insert into @alt_kategori (id,deger) values (2,’cpu’)
insert into @alt_kategori (id,deger) values (3,’memory’)
insert into @alt_kategori (id,deger) values (4,’buz dolabi’)
insert into @alt_kategori (id,deger) values (5,’bulasik makinesi’)
declare @kategori_altKategori table(id int,kategoriID int,altKategoriID int)
insert into @kategori_altKategori(id,kategoriID,altKategoriID) values (1,1,1)
insert into @kategori_altKategori(id,kategoriID,altKategoriID) values (2,1,2)
insert into @kategori_altKate
yani donem bilgisini kolon yazip hemen altindaki satirlara toplamtutar bilgisi degilde, satir da donem yan satirda ucret bilgisimi istiyrorsun ?
elindeki veriden örnek bir parca ve olusturmak istedigin halini gonderirsen daha saglikli cevap verebilirim
Merhaba,
Benim söyle bir sorunum, daha dogrusu almak istedigim bir çikti var.
Pivot ile bir fonksiyon kullarak cikti elde ediyor, toplam ortalama adet gibi.. Benim elde etmek istedigim cikti o alanin ornegin toplamini bulmak yerine yan yana yazdirmak istiyorum
Yukaridaki tabloyu baz alip ornek verecek olursam:
MusteriAdSoyad Donem_1 Toplam Tutar_1 Donem_2 Toplam Tutar_2
Makale cok isime yaradi .Gercekten Tesekurler.
Ama soyle bir denemede takildim.
ÖR:
PIVOT
(
Count(SatisTemsilciID)
For DurumID IN (Select DurumID from tblGorusme )
)
AS p
Kirmizi yazilmiz olana gore cekilsin istedi ama olmadi.Yardimci olabilirmisniz acaba hatam nerde?Acaba orda select kullanamiyozmu?
Makale cok isime yaradi .Gercekten Tesekurler.
Ama soyle bir denemede takildim.
ÖR:
ALTER PROCEDURE PivotDeneme
(
@GorusmeZamani datetime,
@DurumID int=0–Kullanilmayacak
)
AS
Select * from (
SELECT S.SatisTemsilciID,S.SatisTemsilciAdi,S.SatisTemsilciSoyadi,G.DurumID
FROM tblSatisTemsilcileri AS S CROSS JOIN
tblGorusmeDurum AS G CROSS JOIN
tblSatisTemsilciGorusmeAbone AS SA
WHERE
Makale güzel olmus tesekkürler.
merhabalar benim sorum biraz komplike
1. SQL’de tablelarim var ve Excel’de hazir pivot tablolu raporlarim var
2. SQL’deki tablelar çok yüklü oldugundan Pivot Tablo refreshleri çok yavas çalismakta
3. Bu nedenle kullanicilar Excel’deki raporlari açarken belirli bir sorgu ile tüm SQL tablosundaki bilgileri refresh yapmadan belirli bir sorgu ile bu pivot tabloya bilgi aktarmak istiyorum
Nasil yapabilirim acaba? Tesekkürser
Merhabalar
Su anda stajdayim ve birkac progra<min ayri ayri yaptigi grafiklerin hepsinin excelle yapilacagini idda ettim evet basardim ama bir sorunum var .Elimde iki adet dosya var bir dosyada 2009 verileri digerindede 2010 nisan ayina kadar olan veriler lazim,ihtiyacim olan sey pivot tablonun her guncellemede son 6 ayi gostermesi.Mesela elime 2010 mayis veriside gectiginde 2009 11 isime yaramayacak her guncellemede son 6 ayi gosteren bir kod varmi?
Manual olarak elle ayarlamak istedigimdede 2010 1. 2. 3. 4. ayini secemiyorum 2009 un bu aylarinida ekliyor 11 ve 12. aylarla berabwer.bu kod olursa sanirim bu sorun cozulur ve manual olarak ugrasmak zorunda kalmazlar
guncellenebilir olmasi icin kullandigim kod bu;
Private Sub Worksheet_Deactivate()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub
Private Sub Worksheet_Deactivate()
Dim pt As Pivo
bhu proqram sanalika ws iicin dimy ?
makalenin en sonundaki sorguya bakacak olursaniz. select * from (select statament) seklinde
yani siz joinli bir sql yazarak bunu bir tablo gibi dusunebilirsiniz.
orn:
select * from
(
select kolon1,kolon2
from tablo1 t1
inner join tablo2 t2 on t1.id = t2.id
) as jtablo
pivot
(
sum(jtablo.kolon2)
FOR kolon1 IN ([]….[])
)
as p
umarim yardimci olabilmisimdir
Birden fazla tablo varsa nasil yapabiliriz?Joini nereye yazmaliyim?