Bütçe Raporu

soruna iki sekilde yanit vermek istedim, 1.si internet'ten bir zaman arastirip buldugum Pivot table üreten stored prosedür. 2.si direkt bir view yazarak.
2. sik bana "balik vermek/balik tutmayi ögretmek" ikilemine düsecegimi hissettirdiginden ben 1. sikki seçtim. Stored Procedure baska raporlamalarinizda da yardimci olabilir.
kolay gelsin. prosedürü aktariyorum.
CREATE PROCEDURE [dbo].[sp_XTAB]

@MYDBO varchar(255),  -- database name
@MYQRY varchar(255),  -- view (name) to create/replace
@MYTBL varchar(4000),  -- table name or join statement
@MYOUT varchar(255), -- column/s to group
@MYPIV varchar(255),  -- column/s to pivot
@MYWHR1 varchar(4000), -- WHERE clause limits fields to be pivoted (---OPTIONAL---)
@MYFLD varchar(255),  -- field/s to calculate and populate pivoted columns I.e. field + field
@MYSUM varchar(255), -- calculation method I.e. COUNT SUM AVG  etc.
@MYALT varchar(255), -- NULL or 0 (zero) I.e. NULL goes with COUNT/Character fields
@MYWHR2 varchar(4000), -- WHERE clause filters calculated field/s (---OPTIONAL---)
@MYSTP varchar(8000) -- Output or other statement (---OPTIONAL---)

 AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MYSTR varchar(8000), @MYTBLB varchar(4000), @MYPIVB varchar(4000)

-- Replace apostrophes with " | "  to avoid scoping errors which would be created by sql-parser when @MYSTR is submitted for execution.
SELECT @MYTBLB = REPLACE(@MYTBL,CHAR(39),CHAR(124))
SELECT @MYOUT = REPLACE(@MYOUT,CHAR(39),CHAR(124))
SELECT @MYPIVB = REPLACE(@MYPIV,CHAR(39),CHAR(124))
SELECT @MYWHR2 = REPLACE(@MYWHR2,CHAR(39),CHAR(124))
SELECT @MYALT = REPLACE(@MYALT,CHAR(39),CHAR(124))
SELECT @MYSTP = REPLACE(@MYSTP,CHAR(39),CHAR(124))
--  " | " symbols are put back to apostrophes before sub-strings are submitted for execution.

-- Build @MYSTR with secondary execution code.
SELECT @MYSTR = 'DECLARE @MYSUBSTR varchar(8000), @MYSUBSTR1 varchar(8000), @MYSUBSTR2 varchar(8000), @MYSUBSTR3

varchar(8000), @MYSUBSTR4 varchar(8000), @MYSUBSTR5 varchar(8000), @MYSUBSTR6 varchar(8000), @MYSUBSTR7 varchar(8000),

@MYSUBSTR8 varchar(8000), @MYSUBSTR9 varchar(8000), @MYSUBEND varchar(8000), @MYSUBQRY varchar(255), @MYSUBTBL

varchar(4000), @MYSUBOUT varchar(255), @MYSUBPIV varchar(255), @MYSUBFLD varchar(255), @MYSUBSUM varchar(255), @MYSUBALT

varchar(255), @MYXCOL varchar(255) , @MYSUBWHR2 varchar(4000), @MYSUBSTP varchar(8000), @MYSUBINT As int, @MYSUBMAX int
Use '+ @MYDBO + '
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ''' + @MYQRY + ''')
DROP VIEW ' + @MYQRY

-- The second execution must declare its own set of variables and these are set for initialisation here.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBQRY = ''' + @MYQRY + '''
SELECT @MYSUBTBL = ''' + @MYTBLB + '''
SELECT @MYSUBOUT = ''' + @MYOUT + '''
SELECT @MYSUBPIV = ''' + @MYPIVB + '''
SELECT @MYSUBFLD = ''' + @MYFLD + '''
SELECT @MYSUBSUM = ''' + @MYSUM + '''
SELECT @MYSUBALT = ''' + @MYALT + '''
SELECT @MYSUBSTR1 = '''+CHAR(59)+'''
SELECT @MYSUBSTR2 = '''+CHAR(59)+'''
SELECT @MYSUBSTR3 = '''+CHAR(59)+'''
SELECT @MYSUBSTR4 = '''+CHAR(59)+'''
SELECT @MYSUBSTR5 = '''+CHAR(59)+'''
SELECT @MYSUBSTR6 = '''+CHAR(59)+'''
SELECT @MYSUBSTR7 = '''+CHAR(59)+'''
SELECT @MYSUBSTR8 = '''+CHAR(59)+'''
SELECT @MYSUBSTR9 = '''+CHAR(59)+'''
SELECT @MYSUBWHR2 = ''' + @MYWHR2 + '''
SELECT @MYSUBSTP = ''' + @MYSTP + '''
SELECT @MYSUBINT = 0
SELECT @MYSUBMAX = 17544'

-- Replace " | " with double apostrophes here.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBTBL = REPLACE(@MYSUBTBL,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBOUT = REPLACE(@MYSUBOUT,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBPIV = REPLACE(@MYSUBPIV,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBWHR2 = REPLACE(@MYSUBWHR2,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBALT = REPLACE(@MYSUBALT,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''') '

-- Initialise secondary execution code.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBSTR = ''CREATE VIEW '' + @MYSUBQRY + '' AS SELECT TOP 100 PERCENT '' + @MYSUBOUT'

-- Add secondary execution iteration to identify and create the pivoted columns.
SELECT @MYSTR = @MYSTR + '
DECLARE curXTAB CURSOR FAST_FORWARD FOR
SELECT DISTINCT '+@MYPIV+'
FROM '+@MYTBL+'
'+@MYWHR1+'
ORDER BY '+@MYPIV+'

OPEN curXTAB
FETCH NEXT FROM curXTAB
INTO @MYXCOL
WHILE @@FETCH_STATUS = 0
BEGIN

If @MYSUBINT <=  @MYSUBMAX
BEGIN
SELECT @MYSUBINT = @MYSUBINT + 1

SELECT @MYXCOL = IsNull(@MYXCOL,''NULL'')'

-- Build a series of secondary execution strings accomodating up to 63000 characters of dynamic sql.
SELECT @MYSTR = @MYSTR + '
If (Len(@MYSUBSTR) < 7001)
BEGIN
SELECT @MYSUBSTR = @MYSUBSTR  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR1) < 7001)
BEGIN
SELECT @MYSUBSTR1 = @MYSUBSTR1  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR2) < 7001)
BEGIN
SELECT @MYSUBSTR2 = @MYSUBSTR2  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR3) < 7001)
BEGIN
SELECT @MYSUBSTR3 = @MYSUBSTR3  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR4) < 7001)
BEGIN
SELECT @MYSUBSTR4 = @MYSUBSTR4  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR5) < 7001)
BEGIN
SELECT @MYSUBSTR5 = @MYSUBSTR5  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR6) < 7001)
BEGIN
SELECT @MYSUBSTR6 = @MYSUBSTR6  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR7) < 7001)
BEGIN
SELECT @MYSUBSTR7 = @MYSUBSTR7  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR8) < 7001)
BEGIN
SELECT @MYSUBSTR8 = @MYSUBSTR8  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR9) < 7001)
BEGIN
SELECT @MYSUBSTR9 = @MYSUBSTR9  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR9) >= 7000)
BEGIN
SELECT @MYSUBSTR9 = ''SELECT XTAB_ERROR__QUERY_TOO_LARGE_FOR_THIS_VERSION_OF_XTAB''
END

END
ELSE'
-- Clear all dynamic strings if more than 17544 distinct rows to pivot.
SELECT @MYSTR = @MYSTR + '
BEGIN
SELECT @MYSUBSTR1 = ''''
SELECT @MYSUBSTR2 = ''''
SELECT @MYSUBSTR3 = ''''
SELECT @MYSUBSTR4 = ''''
SELECT @MYSUBSTR5 = ''''
SELECT @MYSUBSTR6 = ''''
SELECT @MYSUBSTR7 = ''''
SELECT @MYSUBSTR8 = ''''
SELECT @MYSUBSTR9 = ''''
SELECT @MYSUBWHR2 = ''''
SELECT @MYSUBSTP = ''EXEC(| XTAB_ERROR__MORE_THAN_17544_DISTINCT_ROWS_TO_PIVOT|''
END

FETCH NEXT FROM curXTAB
INTO @MYXCOL
END

CLOSE curXTAB
DEALLOCATE curXTAB'

-- Initialise secondary FROM WHERE GROUP and ORDER BY statements.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBEND = ''
FROM '' + @MYSUBTBL

If Len(@MYSUBWHR2) > 0
BEGIN
SELECT @MYSUBEND =  @MYSUBEND + ''
'' + @MYSUBWHR2 + '' ''
END

If Len(@MYSUBSUM) > 0
BEGIN
SELECT @MYSUBEND = @MYSUBEND + ''
GROUP BY '' + @MYSUBOUT
END

SELECT @MYSUBEND = @MYSUBEND + ''
ORDER BY '' + @MYSUBOUT'

-- Remove the CHAR(59) padding from second execution strings.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBSTR1 = SUBSTRING(@MYSUBSTR1,2,8000)
SELECT @MYSUBSTR2 = SUBSTRING(@MYSUBSTR2,2,8000)
SELECT @MYSUBSTR3 = SUBSTRING(@MYSUBSTR3,2,8000)
SELECT @MYSUBSTR4 = SUBSTRING(@MYSUBSTR4,2,8000)
SELECT @MYSUBSTR5 = SUBSTRING(@MYSUBSTR5,2,8000)
SELECT @MYSUBSTR6 = SUBSTRING(@MYSUBSTR6,2,8000)
SELECT @MYSUBSTR7 = SUBSTRING(@MYSUBSTR7,2,8000)
SELECT @MYSUBSTR8 = SUBSTRING(@MYSUBSTR8,2,8000)
SELECT @MYSUBSTR9 = SUBSTRING(@MYSUBSTR9,2,8000)'

-- Secondary execution code with zero to 63000+ character tolerance.
SELECT @MYSTR = @MYSTR + '
If Len(@MYSUBSTR1) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR2) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR3) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR4) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR5) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR6) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR7) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 +

@MYSUBEND)
END
ELSE
If Len(@MYSUBSTR8) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 +

@MYSUBSTR7 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR9) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 +

@MYSUBSTR7 + @MYSUBSTR8 + @MYSUBEND)
END
ELSE
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 +

@MYSUBSTR7 + @MYSUBSTR8 + @MYSUBSTR9 + @MYSUBEND)
END'

-- Initialise secondary (optional) output statement.
SELECT @MYSTR = @MYSTR + '
If Len(@MYSUBSTP) > 2
BEGIN
SELECT @MYSUBSTP = REPLACE(@MYSUBSTP,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
EXEC(@MYSUBSTP)
END'

EXEC(@MYSTR)

gullpasha Yazdı: Merhaba arkadaslar. Bir rapor hazirlamaya çalisiyorum ama isin içinde çikamadim açikcasi belki elinde benim yapmak istedigi rapordan hazir olan vardir.   Gelirler                       kod                   ocak  subat  mart   nisan    mayis .............   Yurtiçi ürün satisi      600-10-10-1-001    xxx    xxx     xxx     xxx      xxx yurtdisi ürün satisi     ...........                ...      ......    ...      ....      ....   Bütün Gelir ve gider kalemlerini bu sekilde ay bazinda gösteren bir raporu elinde mevcut olan varsa gönderebilir mi acaba

http://www.themssforum.com/SQLServerDev/XTAB-CROSSTAB/
linkinde prosedürün kullanim örnegini Northwind database'i için örneklemis.
Ben size konunuza yakin olmasi için ssyle bir örnek vereyim

CREATE VIEW NV_ORNEK AS
SELECT A.HES_KOD,B.HS_ADI,A.AY_KODU AS AY,
CASE WHEN BA=1 THEN TUTAR ELSE 0 END BORC,
CASE WHEN BA=2 THEN TUTAR ELSE 0 END ALAC,
CASE WHEN BA=1 THEN TUTAR ELSE -TUTAR END BAKIYE
FROM TBLMUHFIS A,TBLMUPLAN B
WHERE A.HES_KOD=B.HESAP_KODU

view' ni yaratalim. burada benim hesaplarin bakiyelerini Ay- Ay listelemek istedigimi düsünelim

EXEC sp_XTAB
'database_adi',
'xtBIL',  --Gecici tablo
'NV_ORNEK',
'HES_KOD,HS_ADI',
'AY',
'',
'BAKIYE',
'SUM',
'NULL',
'',
'SELECT xtBIL.* FROM xtBIL ORDER BY HES_KOD DROP VIEW xtBIL'
komutuyla istedigimizi aliriz
Not: bu prosedürü 4-5 sene önce bulup kullanmistim Access ve Excel'den alistigim Pivot'u SQL nasil yapamaz diyerek arayip buldum. kullanimini hatirlamam biraz zor oldu.
-hala WHERE cümleciklerini prosedüre dahil edemedim-
Kolay gelsin

merhabalar bende extend oladigi icin alamiyorum extendsiz nasil alabilirim bu raporu

dogru haklisiniz ordan alabiliyorum ancak benim istedim ayni yukardaki…http://www.themssforum.com/SQLServerDev/XTAB-CROSSTAB/ ..rapordaki gibi tum islemleri extend olmadan yapmanin bi formulu bunun da tek yollu anladigim kadariyla makrodan geciyor(extend olmadigi icin baska care yok)

http://192.168.3.17:15871/cgi-bin/blockpage.cgi?ws-session=1090639081

bu linkte bir extend caçlismasi var bu link disinda benim bir caçlismam vardi 2 yil önce yaptigim suan nasil yaptigimi hatirlayamiyorum. ALTTAKI LINK TEKI CALISMA GIBI BIR ÇALISMAYI USTTEKI BILGILER DOGRULTUSUNDA YAPMAK ISTIYORUM

 

http://rapidshare.com/files/211326445/EXTENS__304_Z_SADECE_V__304_EW___304_LE_VER__304__ALMA.xls.html

burdaki konudan ulasmistim  bahsetigim linke

Benim Extend de yaptigim Bilanço, Gelir tablosu Rasyo çalismasi bu.

Siz excelde nasil bir rapor almak istiyorsunuz. Özet tablo basit.

O sekilde siz create edin VT de Extend siz olarak ki halini hazirlariz ama bu günlerde biraz yogunum.