如何给出这个查询sql的条件 [英] How to give where conditon for this query sql

问题描述

亲爱的朋友们，



我想在这个查询中给出Where条件我给出的这个条件我不知道。请帮帮我。

Dear Frnds,

I want to give "Where" condition in this query were i give this condition i don't know. please help me.

DECLARE @cols AS NVARCHAR(MAX),
@colsName AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ItemCode +'_'+c.col)
from Vw_InOutWard
cross apply
( select 'TotalCount' col
union all
select 'TotalAmount') c
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
select @colsName = STUFF((SELECT distinct ', ' + QUOTENAME(ItemCode +'_'+c.col) +' as [' + ItemCode + case when c.col = 'TotalCount' then ']' else ' Rate]' end
from Vw_InOutWard
cross apply
(select 'TotalCount' col
union all
select 'TotalAmount') c
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT DCNo,DCDate,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy, ' + @colsName + '
from
(select DCDate, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToContNo,RefferedBy,ToAddress, ItemCode +''_''+col col, value
from
(select DCDate, ItemCode, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy,
cast(Qty as numeric(10, 2)) totalcount,
cast(Rate as numeric(10, 2)) totalamount
from Vw_InOutWard) src
unpivot
(value for col in (totalcount, totalamount)) unpiv ) s
pivot (sum(value) for col in (' + @cols + ')) p '
execute(@query)

推荐答案

将DC No作为参数从前端传递并更改set @query，如下所示

Pass DC No as a parameter from frontend and change set @query as below

set @query = 'DECLARE @DCNo INT;
SET @DCNo ='+Cast(@DCNO as VARCHAR)+';
SELECT DCNo,DCDate,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy, ' + @colsName + '
from
(select DCDate, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToContNo,RefferedBy,ToAddress, ItemCode +''_''+col col, value
from
(select DCDate, ItemCode, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy,
cast(Qty as numeric(10, 2)) totalcount,
cast(Rate as numeric(10, 2)) totalamount
from Vw_InOutWard WHERE DCNo=@DCNO ) src
UNPIVOT
(value for col in (totalcount, totalamount)) unpiv ) s
pivot (sum(value) for col in (' + @cols + ')) p '

这篇关于如何给出这个查询sql的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！