pivot dynamic query in sql server 2000

[dell816]

سلام بر استاید عزیز خصوصآ استاد ثباتی :

میخواهم query به شرح زیر داشته باشم :
بعنوان مثال میخواهم 3 یا n سابقه آخرین خرید مشتریان یا فروش کالا به تفکبک مته را بصورت فایل ضمیمه داشته باشم

چگونه باید این query را با سرعت بالا نوشت؟

[Reza_Yarahmadi]

جدول و داده های نمونه:
Create Table tbl(
id int identity(1,1),
GoodName nvarchar(50) null,
SellDate varchar(10) null,
SellCount int)

Insert Into tbl(GoodName, SellDate, SellCount)
Values
(N'کالای 1', '1390/01/28', 3),
(N'کالای 1', '1390/01/28', 1),
(N'کالای 1', '1390/02/29', 7),
(N'کالای 1', '1390/02/29', 9),
(N'کالای 1', '1390/02/30', 1),
(N'کالای 1', '1390/02/31', 5),

(N'کالای 2', '1390/02/27', 7),
(N'کالای 2', '1390/02/28', 2),
(N'کالای 2', '1390/02/28', 2),
(N'کالای 2', '1390/02/29', 5),
(N'کالای 2', '1390/02/29', 3),
(N'کالای 2', '1390/02/30', 8),
(N'کالای 2', '1390/02/31', 3),
(N'کالای 2', '1390/03/31', 3),

(N'کالای 3', '1390/02/27', 3),
(N'کالای 3', '1390/02/31', 3)
جواب مورد اول:

Declare @Str varchar(max), @Str2 varchar(max)
Set @Str = ''
Set @Str2 = ''
Select @Str = @Str + Case When CharIndex(SellDate, @Str) = 0 Then
Case When @Str <> '' Then ','
else '' End +
'[' + SellDate + ']'
Else '' End,
@Str2 = @Str2 + Case When CharIndex(SellDate, @Str2) = 0 Then
Case When @Str2 <> '' Then ','
else '' End +
'ISNULL(Sum([' + SellDate + ']), 0) as [' + SellDate + ']'
Else '' End
From tbl
Order By SellDate

Set @Str = '
Select GoodName, ' + @Str2 + '
FROM tbl
Pivot (Sum(SellCount)
For SellDate IN (' + @Str + ')) pvt
Group by GoodName'

Exec(@Str)

جواب مورد دوم:

Declare @_Str varchar(max), @_Str2 varchar(max)
Set @_Str = ''
Set @_Str2 = ''
Select @_Str = @_Str + Case When CharIndex(SubString(SellDate, 6, 2), @_Str) = 0 Then
Case When @_Str <> '' Then ','
else '' End +
'[' + SubString(SellDate, 6, 2) + ']'
Else '' End,
@_Str2 = @_Str2 + Case When CharIndex(SubString(SellDate, 6, 2), @_Str2) = 0 Then
Case When @_Str2 <> '' Then ','
else '' End +
'ISNULL(Sum([' + SubString(SellDate, 6, 2) + ']), 0) as [' + SubString(SellDate, 6, 2) + ']'
Else '' End
From tbl
Order By SellDate

Set @_Str = '
Select GoodName, ' + @_Str2 + '
FROM (
Select
GoodName,
SellCount,
SubString(SellDate, 6, 2) SellMonth
From
tbl) t
Pivot (Sum(SellCount)
For SellMonth IN (' + @_Str + ')) pvt
Group by GoodName'

Exec(@_Str)

[یوسف زالی]

این رو امتحان کن:

به تفکیک مشتری - کالا

داده ها:



Create Table tbl(id intidentity(1,1), Customer varchar(50), GoodName varchar(50)null,SellDate varchar(10)null,SellCount int)

Insert Into tbl(Customer, GoodName, SellDate, SellCount)
Values('',' 1','1390/01/28', 3),
('',' 1','1390/01/28', 1),
('',' 1','1390/02/29', 7),
('',' 1','1390/02/29', 9),
('',' 1','1390/02/30', 1),
('',' 1','1390/02/31', 5),
('',' 2','1390/02/27', 7),
('',' 2','1390/02/28', 2),
('',' 2','1390/02/28', 2),
('',' 2','1390/02/29', 5),
('',' 2','1390/02/29', 3),
('',' 2','1390/02/30', 8),
('',' 2','1390/02/31', 3),
('',' 2','1390/03/31', 3),
('',' 3','1390/02/27', 3),
('',' 3','1390/02/31', 3)


کد:



declare @S varchar(4000)
declare @t varchar(20)
set @S ='create table #c(Sn int identity(1, 1), CST varchar(50)'
declare #c cursor for select distinct GoodName from tbl -- where SellDate between ...
open #c
fetch next from #c into @t
while @@FETCH_STATUS<>-1
begin
set @S = @S +', ['+cast(@t as varchar(50))+'] decimal(18, 0) null'

fetch next from #c into @t
end
set @S = @S +') insert into #c(CST) select distinct Customer from Tbl'
close #c
open #c
fetch next from #c into @t
while @@FETCH_STATUS<>-1
begin
set @S = @S +' update #c set ['+cast(@t as varchar(50))+'] = '+
'isnull((select sum(SellCount) from tbl '+
'where GoodName = '''+cast(@t as varchar(50))+''' and Customer = CST ), 0)'

fetch next from #c into @t
end
close #c
set @S = @S +' select * from #c drop table #c'
exec(@S)
deallocate #c


نتیجه:
70149

[Reza_Yarahmadi]

این query در sqlserver 2000 کار نمیکند و فکر میکنم pivot برای 2005 و 2008 هست؟
pivot از SQL Server 9 به بعد (2005) اضافه شده و قابل استفاده است.(من با عنوان تاپیک شما توجه نکردم و از این بابت معذرت میخوام)

آیا راه دیگری وجود دارد؟
اضافه بر روشی که دوستمون گفتند میتونید بصورت زیر هم عمل کنید. روش دوستمون بدلیل استفاده از کرسر سربار زمانی بالای داره و این موضوع توی تعداد رکورد بالا خودش رو نشون میده.
Declare @Str nvarchar(max),@Str2 nvarchar(max)
Set @Str = ''
Set @Str2 = ''
Select @Str = @Str
+ Case When CHARINDEX('['+SellDate+']', @Str) <> 0 Then ''
Else
Case When @Str <> '' Then ',' Else '' End +'
[' + SellDate + '] = Case When SellDate = ''' + SellDate +
''' Then Cast(SellCount as varchar)
Else 0 End'
End,
@Str2 = @Str2
+ Case When CHARINDEX('['+SellDate+']', @Str2) <> 0 Then ''
Else
Case When @Str2 <> '' Then ',' Else '' End +'
[' + SellDate + '] = Sum([' + SellDate + '])'
End
From tbl
Order By SellDate
Set @Str =
'Select
GoodName,
' + @Str2 +'
From (
Select
GoodName,' + @Str + '
From
tbl)t
Group by
GoodName'

EXEC( @Str)

برای مورد دوم هم میتونید از همین روش (با کمی تغییر) استفاده کنید.

[یوسف زالی]

دوست عزیز تا جایی که می دونم 2000 فرمت I nvarchar(max) I رو نمیشناسه و باید جای max یه عدد بگذاریم.

[dell816]

pivot از SQL Server 9 به بعد (2005) اضافه شده و قابل استفاده است.(من با عنوان تاپیک شما توجه نکردم و از این بابت معذرت میخوام)

اضافه بر روشی که دوستمون گفتند میتونید بصورت زیر هم عمل کنید. روش دوستمون بدلیل استفاده از کرسر سربار زمانی بالای داره و این موضوع توی تعداد رکورد بالا خودش رو نشون میده.
Declare @Str nvarchar(max),@Str2 nvarchar(max)
Set @Str = ''
Set @Str2 = ''
Select @Str = @Str
+ Case When CHARINDEX('['+SellDate+']', @Str) <> 0 Then ''
Else
Case When @Str <> '' Then ',' Else '' End +'
[' + SellDate + '] = Case When SellDate = ''' + SellDate +
''' Then Cast(SellCount as varchar)
Else 0 End'
End,
@Str2 = @Str2
+ Case When CHARINDEX('['+SellDate+']', @Str2) <> 0 Then ''
Else
Case When @Str2 <> '' Then ',' Else '' End +'
[' + SellDate + '] = Sum([' + SellDate + '])'
End
From tbl
Order By SellDate
Set @Str =
'Select
GoodName,
' + @Str2 +'
From (
Select
GoodName,' + @Str + '
From
tbl)t
Group by
GoodName'

EXEC( @Str)

برای مورد دوم هم میتونید از همین روش (با کمی تغییر) استفاده کنید.

ببخشید charindex چکار میکند؟

[Reza_Yarahmadi]

CharIndex یکی از توابع SQL Server است که یک متن رو توی متن دیگه جستجو میکنه و در صورت وجود شماره کاراکتر رو برمیگردونه ، در صورت عدم وجود هم 0 برمیگردونه.

[dell816]

ببخشید دوست عزیز ، میشه راجع به این کوئری بیشتر توضیح بدین ، ممنون میشم.. من اصلاً سر در نیاوردم. ( البته کار میکند )

[dell816]

دوست عزیز ، لطفً در مورد این کوئری توضیح دهید.