pivot tablo sorgusu

Question

Merhaba Arkadaşlar ,

dinamik bir pivot tablo sorgusu oluşturmaya çalıştım. Fakat kodlarda sondan ikinci satırdaki @kolonlar değişkeninde hata veriyor. Nasıl düzeltebilirim. Yardımcı olursanız çok memnun olurum.

DECLARE @Kolonlar VARCHAR(MAX)

    SELECT @Kolonlar =

      COALESCE(

        @Kolonlar + ',[' + cast(beden as varchar) + ']',

        '[' + cast(beden as varchar)+ ']'

      )

FROM    tverilecekgiyimyedek

GROUP BY  beden  order by  beden

SELECT * FROM 

    (

       SELECT  tgiyim.giyim as giyim ,tverilecekgiyimyedek.beden   FROM tverilecekgiyimyedek INNER JOIN

                         tgiyim ON tverilecekgiyimyedek.giyimid = tgiyim.giyimid INNER JOIN

                         tpersonel ON tverilecekgiyimyedek.personelid = tpersonel.personelid

 

 WHERE        (tpersonel.kadroid IS NULL) AND (YEAR(tverilecekgiyimyedek.verilistarih) = @yil) AND (tpersonel.subeid = ISNULL(@subeid, tpersonel.subeid)) AND

                         (tverilecekgiyimyedek.mevsimid = ISNULL(@mevsimid, tverilecekgiyimyedek.mevsimid)) 

    ) AS TABLO_SATIS

    PIVOT

    (

    

    count(beden) 

     FOR beden

     IN

      (   

        @kolonlar  -- hatalı satır

       )

    )

   

    AS PIVOT_TABLOM

 

Answer 1

Merhaba Mehmet,

SQL Server Dynanic Pivot table query ve örnek için lütfen verdiğim linke bakabilirsin. O yazıda bu konuyu Dynamic Pivot Table Queries kısmında örneklemeye çalışmıştım.

Aşağıdaki kod bloğunu eskisi ile değiştirirsen çalışabileceğini düşünüyorum. Sadece kolonları oluşturduğun sorgu değil, ana pivot tablo sorgusunu da dinamik hale getirmelisin.

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'
SELECT * FROM  
    ( 
       SELECT  tgiyim.giyim as giyim ,tverilecekgiyimyedek.beden   FROM tverilecekgiyimyedek INNER JOIN
                         tgiyim ON tverilecekgiyimyedek.giyimid = tgiyim.giyimid INNER JOIN
                         tpersonel ON tverilecekgiyimyedek.personelid = tpersonel.personelid
WHERE        (tpersonel.kadroid IS NULL) AND (YEAR(tverilecekgiyimyedek.verilistarih) = @yil) AND (tpersonel.subeid = ISNULL(@subeid, tpersonel.subeid)) AND 
                         (tverilecekgiyimyedek.mevsimid = ISNULL(@mevsimid, tverilecekgiyimyedek.mevsimid)) 
) AS TABLO_SATIS 
PIVOT 
( 
    count(beden)  
     FOR beden 
     IN 
      (    
	  ' + @kolonlar + '
       ) 
    ) 
AS PIVOT_TABLOM'
 
EXECUTE(@Sql)

Umarım işine yarar.

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 2

Merhaba Eralp,

Cevabın için teşekkür ediyorum. Vermiş olduğun bilgiler doğrultusunda kodlarımı değiştirdim. Bahsettiğiniz kısmıda dinamik yaptım ve sorguyu sql management studioda  çalıştırdığımda tam istediğim sonucu elde ettim.  Şimdi şöyle bir sıkıntım oldu. Ben bu sorguyu  visual studio da bir dataset oluşturup, datalarıda yukarıda oluşturduğumuz stored procedureden alıp oluşturduğum raporda göstermek istiyorum.  Lakin yukarıda verdiğin execute(@sql) satırından dolayı datasette kayıtları  göremiyorum.  stored procedurenin içinde  ( uzatmadan yazıyorum) 

select * from tverilecekgiyimyedek

yazdığımda datasette kayıtları görebiliyorum, ama

yukarıdaki sorguyu string içinde yazıp  bir değişkene atadığımızda 
set @sql=' select * from tverilecekgiyimyedek'  execute(@sql)

datasette hiçbir kayıt göstermiyor. yani dataset benden execute olmadan bir sorgu yazmamı bekliyor. Aklıma gelen şu doğrumudur bilmiyorum ama execut(@sql) dedikten sonra burdan gelen kayıtları bir tabloya alıp o tabloyu select * from tablo deyip yazarsak olur diye tahmin ediyorum ama bunun nasıl yapılacağını bilmiyorum. Umarım sıkıntımı tam olarak anlatabilmişimdir. Emek ve uğraşlarınız şimdi çok teşekkür ediyorum.

Answer 3

Merhaba Mehmet,

Bir de aşağıdaki gibi deneyebilir misin?

exec sp_executesql @sql

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 4

Merhaba Eray,

Verdiğin kodu denedim ama maalesef olmadı.

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

hatasını veriyor. @kolonlar için bir parametremi girmek gerekiyor acaba vermiş olduğun satırın sonuna ?

Answer 5

Kullandığın parametre '@statement' ın tipi varchar() sanıyorum. Onu nvarchar(max) olarak tanımlarsan sp_executesql çalışacaktır.

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 6

değişkenin tipini varchar dan nvarchar olarak değiştirdim . sorgu hata vermeden sql management studio da çalıştı ama   exe csp_executesql @sql  ile başta yazdığımız EXECUTE(@sql)  aynı şekilde davrandı ve datasette select sorgusuyla dönen herhangi bir kayıt olmadı maalesef.

Answer 7

Problem acaba Pivot sorgusundan dolayı kolon isimlerinin her defasında farklı geliyor olmasından mı acaba?

Şu kodu deneyebilirsin belki. Ama yine problem temp tablodaki kolon isimlerini ancak @kolonlar değişkeninde saklı olması. Yani yine dinamik SQL çalıştırmak zorunda kalacaksın. Açıkçası çözüm için tam bir reçetem yok.

Ancak SQL Profiler'ı açıp web üzerinden çağrılan komutun SQL Server üzerinde ne şekilde çalıştırıldığını kontrol edebilirsin. Belki arada bir problem olmuş olabilir.

declare @sql nvarchar(max) = 'select object_id, name from sys.tables'

create table #t (object_id int, name sysname)

insert into #t(object_id, name)
exec sp_executesql @sql  

select * from #t

drop table #t

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 8

Son gönderdiğiniz kodu aşağıdaki şekilde stored procedure ekledim, Datasetimi oluşturdum ve datasette istediğim sonucu elde ettim,  fakat burda oluşturduğumuz temp tablo statik ben temp tablomunda dinamik olması lazım. aşağıdaki kolon adlarını aldığım sorgudan bana beden numaraları dönecek. her beden benim bir kolonum olacak buna ilaveten hiç değişmeyecek ilk kolonum var o kolonda da giyimin adı yazacak.  yani şöyle;

                30 32 34 36 38   (beden numaraları  ve bu bedenlerden kaç tane mevcut )

gömlek     2    3   5   3   4

papyon    3    3   1   3    9

bir sorguyla toplam kaç tane bedenim varsa onu elde ettim diyelim ( 20 mesela )  buradan

ben temp tabloyu dinamik olarak oluşturabilirmiyim

creat table  #temp(

giyim  varchar(100),    // bu sabit bir kolon

ardından ( 20 ayrı beden olduğu için 20 tane kolon ) 

kolon1,kolon2,....... kolon 20  int tipinde 20  kolon oluştursun bu kısımda

)

İnşallah anlatabilmişimdir .

      

IF

object_id('tempdb..##tempim') IS NOT NULL

BEGIN

END

CREATE

TABLE ##tempim

(

giyim

varchar(100), beden int

)

DECLARE @Kolonlar nVARCHAR(MAX)

SELECT @Kolonlar =

COALESCE(

@Kolonlar

+ ',[' + convert(varchar,beden,104) + ']',

'[' + convert(varchar, beden,104)+ ']'

)

FROM tverilecekgiyimyedek where tverilecekgiyimyedek.beden=48

GROUP

BY beden order by beden

print @Kolonlar

DECLARE @SORGU nVARCHAR(MAX)

SET @SORGU =N'

SELECT * from

(

SELECT tgiyim.giyim as giyim ,tverilecekgiyimyedek.beden FROM tverilecekgiyimyedek INNER JOIN

tgiyim ON tverilecekgiyimyedek.giyimid = tgiyim.giyimid INNER JOIN

tpersonel ON tverilecekgiyimyedek.personelid = tpersonel.personelid

) AS TABLO_SATIS

PIVOT

(

count(beden)

FOR beden

IN

(

'

+ @Kolonlar+ '

)

) AS PIVOT_TABLOM

'

insert into ##tempim(giyim,beden )

exec

sp_executesql @sorgu

select

* from ##tempim

DROP TABLE ##tempim

Answer 9

Mehmet Bey, merhaba;

Anlattıklarınızı yanlış anlamadıysam aşağıdaki gibi örnekler sizin yapmak istediğinizle oldukça benzerlik gösteriyor. Bir göz atabilirseniz memnun olurum.

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Teşekkürler.

---

Microsoft bu servisi kullanıcılarına yardım etme, Microsoft ürünleri ve teknolojileriyle ilgili bilgi bankasını genişletme amacıyla ücretsiz sunmaktadır.
Bu içerik olduğu gibi benim tarafımdan hazırlanmış olup Microsoft tarafından herhangi bir sorumluluk üstlenildiği anlamına gelmez.
Facebook Üzerinden Takip Et!
Twitter'da Takip Et!

Answer 10

Merhaba Mehmet,

Belki de dataset yerine datareader kullansan bütün bu taklalara ihtiyaç kalmayacak.

Data reader ile deneyebilir misin? Böylece belki temp tablo vs gibi şeylere ihtiyaç duymadan dönen seti doğrudan okuyabilirsin.

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 11

 Merhaba Ali Rıza, vermiş olduğun linkleri kontrol ettim fakat tam olarak istediğim pivot örnekleri değildi benim için, cevabın için yine de çok teşekkür ediyorum. 

Eralp' in daha önce verdiği 

declare @sql nvarchar(max) = 'select object_id, name from sys.tables'

create table #t (object_id int, name sysname)

insert into #t(object_id, name)

exec sp_executesql @sql 

select * from #t

drop table #t

kodlarından yola çıkarak  dinamik olarak temp tablo oluşturdum ve istediğim sonucu elde ettim. Sqldatareader kullanarak  daha önce  rapor oluşturmadığım için  yine dataset üzerinden gitmek zorunda kaldım . Cevap veren tüm arkadaşlara zaman ayırdıkları için çok teşekkür ediyorum :)

declare

@kayitsayisi int

declare

@sql varchar(max)

select

@kayitsayisi=count(grup.beden) from

(

SELECT beden

FROM tverilecekgiyimyedek

GROUP

BY beden

)

grup

IF object_id('tempdb..##tempim') IS NOT NULL

BEGIN

DROP TABLE ##tempim

END

CREATE

TABLE ##tempim

(

giyim

varchar(max)

)

declare

@sayac int

set

@sayac=1

declare

@sorgu2 varchar(max)

set

@sorgu2=''

while

@sayac<=@kayitsayisi

begin

if

(@sayac=@kayitsayisi)

begin

set

@sorgu2=@sorgu2+'c'+cast(@sayac as varchar)+' int null'

end

else

begin

set

@sorgu2=@sorgu2+'c'+cast(@sayac as varchar)+' int null,'

end

set

@sayac=@sayac+1

end

select

@sql = 'ALTER TABLE ##tempim ADD ' + @sorgu2

exec(@sql)

 

 

 

Answer 12

Merhaba Mehmet Bey;

Öncelikle rica ederiz. Ayrıca çözümü uygulayıp, kendi projenize göre uyarladıktan sonra kodlar ile birlikte bizimle paylaşıp bizleri de bilgilendirmeniz büyük incelik.

Teşekkürler.

---

Microsoft bu servisi kullanıcılarına yardım etme, Microsoft ürünleri ve teknolojileriyle ilgili bilgi bankasını genişletme amacıyla ücretsiz sunmaktadır.
Bu içerik olduğu gibi benim tarafımdan hazırlanmış olup Microsoft tarafından herhangi bir sorumluluk üstlenildiği anlamına gelmez.
Facebook Üzerinden Takip Et!
Twitter'da Takip Et!