استفاده از where clause در Dynamic pivot query

[Leila-2011]

سلام دوستان
این کوئری بدون عبارت where به من جواب میده اما وقتی where اضافه میکنم اجرا نمیشه
لطفا بگین مشکل کجاست


declare @Pname nvarchar(2000)
set @Pname=''
select @Pname = @Pname + SA_PropertyName + ',' from [QC-SaveAssemble] group by SA_PropertyName
set @Pname=LEFT(@Pname , len(@Pname)-1)
exec('select *
FROM dbo.[QC-AssembleIndex] INNER JOIN
dbo.[QC-SaveAssemble] ON dbo.[QC-AssembleIndex].Ass_Inx = dbo.[QC-SaveAssemble].SA_AssInx
WHERE (dbo.[QC-AssembleIndex].Ass_RepInx=1)
pivot ( max(SA_PropertyValue) for SA_PropertyName IN ('+@Pname+')) AS x ')

[یوسف زالی]

به نظرم وقتی دارید از این حالت استفاده می کنید دیگه اصلا نیازی به پایوت ندارید و می تونید در همون مود داینامیک کوئری تون رو مستقیم بگیرید.
در مورد کاری که دارید می کنید و کوئری که می خواهید و اطلاعاتی در مورد جداول بگید تا اگر بدونم راهنمایی کنم.

[Leila-2011]

نه حتما باید از پایوت استفاده کنم jوی جدول save Assemble یه فیلدهایی دارم که باید بیان توی ستون
این ساختار دو تا جدولمه :

CREATE TABLE [dbo].[QC-SaveAssemble](
[SA_ID] [int] IDENTITY(1,1) NOT NULL,
[SA_AssInx] [int] NOT NULL,
[SA_PropertyName] [nvarchar](50) NULL,
[SA_PropertyValue] [nvarchar](50) NULL,
[SA_Active] [bit] NULL,


CREATE TABLE [dbo].[QC-AssembleIndex](
[Ass_Inx] [int] IDENTITY(1,1) NOT NULL,
[Ass_RepInx] [int] NOT NULL,
[Ass_AssembleCode] [nvarchar](30) NOT NULL,
[Ass_Counter] [nvarchar](20) NOT NULL,
[Ass_PartCode] [nvarchar](10) NULL,
[Ass_QCIns_Code] [varchar](5) NULL,
[Active] [bit] NULL,

نیاز دارم که PropertyName رو از Save Assemble بیارم تو ستون و PropertyValue رو زیرش قرار بدم

امیدوارم توضیحاتم کافی باشه

[یوسف زالی]

منظورم از این که نیازی بهش ندارید چیزی شبیه به اینه:



declare @Qry varchar(max) = 'declare @Tbl table(SN int' + CHAR(13)

select @Qry += ', [' + SA_PropertyName + '] varchar(100) default ''''' + CHAR(13)
from [QC-SaveAssemble]
group by SA_PropertyName

set @Qry += ')' + CHAR(13) + 'insert into @Tbl select 1'

select @Qry += ', ''' + max(SA_PropertyValue) + '''' + CHAR(13)
from [QC-SaveAssemble]
group by SA_PropertyName

set @Qry += CHAR(13) + 'select * from @Tbl' + CHAR(13)

exec(@Qry)

البته خیلی متوجه خروجیتون نشدم. اگر تصویری از اونچه که می خواهید بکشید بهتر می شه راهنمایی کرد.

[Leila-2011]

کوئری بالا با اینکه من اصلا نفهمیدمش جواب میده فقط مسئله اینه که من حاصل این کوئری + بقیه مقادیر حاصل از join دوتا جدولمو میخام و اینکه یه شرط (where) هم باید داشته باشم که روی Ass_RepInx اعمال شه

[یوسف زالی]

دوست من گفتم که، روی کاغذ یک مثالش رو بکش، با مثلا 7 8 تا ردیف. خروجی رو هم بکش تا ببینم چی می خواهی.
join داری و از طرفی Aggregate هم داری. باید دقیق ببینم چی می خواهی.

[Leila-2011]

این جدول Report Index :
105588
,
این جدول Save Report :
105589

, و اینم خروجی ای که نیاز دارم :
105590

[یوسف زالی]

هر چی فسفر داشتم از دست دادم!
این هم از کوئری شما:



declare @SubQrueriesPlaceHolder varchar(50) = '<SubQrueries>'
declare @Qry varchar(max) = 'select A.*' + @SubQrueriesPlaceHolder + char(13) + ' from [QC-AssembleIndex] A'
declare @SubQrueries varchar(max) = ''

select @SubQrueries += CHAR(13) + ' ,(select max(SA_PropertyValue) from [QC-SaveAssemble] where SA_AssInx = Ass_Inx and SA_PropertyName = ''' + SA_PropertyName + ''') as ' + SA_PropertyName
from [QC-SaveAssemble]
group by SA_PropertyName

set @Qry = REPLACE(@Qry, @SubQrueriesPlaceHolder, @SubQrueries)

exec(@Qry)


تست کردم تمام حالات ممکن رو پوشش می ده.
اگر در فهمش مشکل داشتید بگید تا توضیح بیشتری بدم.

[Leila-2011]

متاسفانه به من جواب نمیده این error میده :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

و اصلا این کوئری رو نمیفهمم اگه ممکنه توضیح بدین و از اون گذشته من نیاز دارم که یه فیلتر روی فیلد Ass_RepInx داشته باشم،
راه آسونتری برای انجام این کار سراغ ندارین؟؟ ، یک هفته است تو این مشکل موندم داره دیوونم میکنه دیگه

[یوسف زالی]

شاید تو کپی کردن حواستون نبوده و همراه با اعداد کپی کردید.
ببنید، کلا داستان ساخت یک کوئری داینامیک ساخت یک رشته هست. بنابراین هر جا که لازمه بک پرینت بگیرید و تست کنید.
توضیحات مشروح:

برنامه SQL Server Management رو باز کنید.
دکمه ی New Query رو بزنید.
این کوئری رو اونجا کپی کنید.
به جای exec بنویسید print البته بدون پرانتز.
با Ctrl+X اجرا کنید.
همون طور که می بینید رشته ای چاپ می شه که قرار بوده ران شه.
تمام فوت و فن کار درست کردن همین رشته هست.
فیلتر ها هم به راحتی اضافه شد.
هر کجا که مایل بودید یک Print بگذارید و دوباره ران کنید تا ببینید که در هر مرحله داره چه اتفاقی می افته.

کوئری شما:



declare @SubQrueriesPlaceHolder varchar(50)
declare @FiltersPlaceHolder varchar(500)
declare @Qry varchar(max)
declare @SubQrueries varchar(max)
declare @Filters varchar(max)

set @Filters = '1, 2' -- inja filtere khod ro benevisid, joda shode ba cama

-- set kardane maghadire avalie
select @SubQrueriesPlaceHolder = '<SubQrueries>',
@FiltersPlaceHolder = '<Filters>',
@Qry = 'select A.*' + @SubQrueriesPlaceHolder + char(13) + ' from [QC-AssembleIndex] A where Ass_RepInx in (' + @FiltersPlaceHolder + ')',
@SubQrueries = ''

-- halghe ruye table [QC-SaveAssemble]
select @SubQrueries += CHAR(13) + ' ,(select max(SA_PropertyValue) from [QC-SaveAssemble] where SA_AssInx = Ass_Inx and SA_PropertyName = ''' + SA_PropertyName + ''') as [' + SA_PropertyName + ']'
from [QC-SaveAssemble]
group by SA_PropertyName

-- jaygozarie subquery ha va filter ha dar reshte asli
set @Qry = REPLACE(@Qry, @SubQrueriesPlaceHolder, @SubQrueries)
set @Qry = REPLACE(@Qry, @FiltersPlaceHolder, @Filters)

--print @Qry
exec(@Qry)



شاید من عادت کردم سخت بنویسم، ولی برای من از دستور پایوت خیلی راحت تر به نظر میاد!
احتمالا بشه با پایوت هم پیادش کرد.

[Leila-2011]

آفرین کاملا داره درست جواب میده ، ممنونم
، من چند وقت sql کار کنم میتونم برسم به جایی که شما الان هستین ؟ ;)

،
امکانش هست که ستون Active رو تو select نیارم ؟؟

[محمد سلیم آبادی]

اینو امتحان کنید
بر اساس پست شماره 7:
select *
from reportindex r
inner join
(
select sr_repinx ,
max(case when sr_propertyName = 'thinner code' then sr_propertyvalue end) as thinnerCode,
max(case when sr_propertyName = 'surt roughtness' then sr_propertyvalue end) as surfroughtness,
from savereport
where sr_repinx = 18
)d
on r.rep_inx = d.sr_repinx;

[Leila-2011]

نیاز اصلی من اینه که داینامیک کار کنم

[یوسف زالی]

سلیم جان ممکنه چند تا فیلد دیگه هم بهش اضافه بشه.
همیشه همون دو تا نیست.



من چند وقت sql کار کنم میتونم برسم به جایی که شما الان هستین ؟

یه سال!! :لبخندساده:
شوخی می کنم. من نمی دونم سطح شما چقدره، اما فکر می کنم با تلاش بتونید سه ماهه به جاهای خیلی خوبی برسید.



امکانش هست که ستون Active رو تو select نیارم ؟؟

اگر پرینت رو بگیرید متوجه کد می شید و می تونید در سلکت به جای *.A هر چیزی بگذارید.
موفق باشید.

[محمد سلیم آبادی]

این لینک هم ببینید بد نیست(البته متاسفانه فیلتر شده):
http://www.google.com/url?sa=t&rct=j&q=plamen%20ratchev%20dynamic%20pivot&source=web&cd=2&cad=rja&ved=0CDIQFjAB&url=http%3A%2F%2Fpratchev.blogspot.com%2F2008%2F12 %2Fdynamic-pivoting.html&ei=9Ym9Ue7FC4KbtAaf7oCoAg&usg=AFQjCNFfLDiSQRGw4FWQKKuaGJ1BArQo5g&bvm=bv.47883778,d.Yms

[محمد سلیم آبادی]

یوسف جان. من کوئری های داینامیکی که نوشته بودی را کامل خوندم.
خیلی جالب بود. منتها ایرادی که داره استفاده از subquery هست. که به شدت برای عملکرد کوئری مضرر.
تکنیکی که در پست شماره 12 استفاده شده بهینه تر از همه تکنیک هاست.
در نتیجه ترجیه دادم که آن تکنیک را با کمک کد شما به صورت داینامیک در بیارم.
اگر نظری یا پیشنهادی راجب کد داری دریغ نکن.
declare @SubQrueriesPlaceHolder varchar(50),
@FiltersPlaceHolder varchar(500),
@Qry varchar(max),
--@SubQrueries varchar(max),
@caseExpression varchar(max) = '',
@Filters varchar(max) = '1, 2'

select @SubQrueriesPlaceHolder = '<SubQrueries>',
@FiltersPlaceHolder = '<Filters>',
@Qry = 'select * from [dbo].[QC-AssembleIndex] a inner join '+
' (select SA_AssInx ' + @SubQrueriesPlaceHolder + char(13) +
' from [QC-SaveAssemble] S' + char(13) +
' where SA_AssInx in (' + @FiltersPlaceHolder + ')'+
'group by SA_AssInx) d '+
'on a.[Ass_RepInx] = d.SA_AssInx'

select @caseExpression += ',max(case when SA_PropertyName = ''' + SA_propertyName + ''' then SA_propertyValue end) as ' + SA_PropertyName + char(13)
from [QC-SaveAssemble]
group by SA_PropertyName


set @Qry = REPLACE(@Qry, @SubQrueriesPlaceHolder, @caseExpression)
set @Qry = REPLACE(@Qry, @FiltersPlaceHolder, @Filters)


print @Qry

/*
select * from [dbo].[QC-AssembleIndex] a
inner join
(select SA_AssInx ,max(case when SA_PropertyName = 'name1' then SA_propertyValue end) as name1
,max(case when SA_PropertyName = 'name2' then SA_propertyValue end) as name2
,max(case when SA_PropertyName = 'name3' then SA_propertyValue end) as name3
from [QC-SaveAssemble] S
where SA_AssInx in (1, 2)
group by SA_AssInx) d
on a.[Ass_RepInx] = d.SA_AssInx
*/

[یوسف زالی]

کدت رو خوندم و ران کردم. تو پست قبلي هم که داديد يکم عجله اي نگاه کردم، کافي بود همون رو دايناميک کني.
درسته حق باشماست. استفاده زیاد و تعداد بالا از ساب کوئری روی Performance تاثیر منفی داره. به همین جهت استفاده از Join بهتره.
راستش من کدم رو خیلی سریع نوشتم و فکر این قضیه رو نکردم. ساختار کد شما از نظر سرعت بهینه تره.
با روش شما موافقم. مخصوصا با Group By آخر که روی فیلد غیر کاراکتری هست.
تنها ایرادش اینه که در AS فیلدها باید براکت بگذارید تا اگر اسمش دو قسمتی بود یا فارسی بود مشکلی پیدا نشه.

[Leila-2011]

declare @SubQrueriesPlaceHolder varchar(50),
@FiltersPlaceHolder varchar(500),
@Qry varchar(max),
--@SubQrueries varchar(max),
@caseExpression varchar(max) = '',
@Filters varchar(max) = '1, 2'

select @SubQrueriesPlaceHolder = '<SubQrueries>',
@FiltersPlaceHolder = '<Filters>',
@Qry = 'select * from [dbo].[QC-AssembleIndex] a inner join '+
' (select SA_AssInx ' + @SubQrueriesPlaceHolder + char(13) +
' from [QC-SaveAssemble] S' + char(13) +
' where SA_AssInx in (' + @FiltersPlaceHolder + ')'+
'group by SA_AssInx) d '+
'on a.[Ass_Inx] = d.SA_AssInx'

select @caseExpression += ',max(case when SA_PropertyName = ''' + SA_propertyName + ''' then SA_propertyValue end) as ' + SA_PropertyName + char(13)
from [QC-SaveAssemble]
group by SA_PropertyName


set @Qry = REPLACE(@Qry, @SubQrueriesPlaceHolder, @caseExpression)
set @Qry = REPLACE(@Qry, @FiltersPlaceHolder, @Filters)


exec@Qry
کوئری بالا به من جواب نمیده ، مشکل کجاست؟
این ارورو میده :
Msg 203, Level 16, State 2, Line 26
The name 'select * from [dbo].[QC-AssembleIndex] a inner join (select SA_AssInx ,max(case when SA_PropertyName = 'P1' then SA_propertyValue end) as P1
,max(case when SA_PropertyName = 'P2' then SA_propertyValue end) as P2

from [QC-SaveAssemble] S
where SA_AssInx in (1, 2)group by SA_AssInx) d on a.[Ass_Inx] = d.SA_AssInx' is not a valid identifier.

[یوسف زالی]

دلیلش نگذاشتن Qry@ در پرانتزه (جلوی exec)

[Leila-2011]

ببخشید دوستان کوئری هایی که پیشنهاد کردین یه مشکل داره اونم اینکه Property value ها رو بر اساس RepInx انتخاب نمیکنه ، همه Porperty value ها رو برمیگردونه ، سعی کردم درستش کنم ولی چون dynamic string رو بلد نیستم موفق نشدم ، لطفا کمک کنید :(

[Leila-2011]

ببینید مثلا این مقادیر جدولم هست:
106306
،
میخام که مثلا برای RepInx 25 فقط Test رو داشته باشم ، کوئری بالا هم Tolerance و هم Test رو بهم میده