Database

Hopeless Idealist wrote:

I would rather type:...but all the companies demand SQL-devs.

Send the list of products as comma seperated to parameter List of Products and change this query into Dynamic query.

For ex: @listOfProducts is parameter. Send as tilde or comma seperated to SQL query


Inside Query, split the comma seperated and change to list. Use the below script.

DECLARE @listOfProducts VARCHAR(2000) 
SET @listOfProducts='174~175~78~77~191~399~451' 
IF OBJECT_ID('tempdb..#listOfProducts') IS NOT NULL DROP TABLE #listOfProducts
 
SELECT  
DISTINCT  Split.a.value('.', 'VARCHAR(100)') AS Prod INTO #listOfProducts
FROM  (SELECT    
CAST ('<M>' + REPLACE(@listOfProducts, '~', '</M><M>') + '</M>' AS XML) AS String  )
AS A CROSS APPLY String.nodes ('/M') AS Split(a)
WHERE  Split.a.value('.', 'VARCHAR(100)')<>''
	 

	   
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN  (select Prod from #listOfProducts)

Chris Maunder wrote:

and once written it will almost never be updated

Quote:

PIVOT
(
<aggregation function="">(<column being="" aggregated="">)

;with cte as 
(
	select isnull([1], 0) as Janeiro,
		   isnull([2], 0) as Fevereiro,
		   isnull([3], 0) as Março,
		   isnull([4], 0) as Abril,
		   isnull([5], 0) as Maio,
		   isnull([6], 0) as Junho,
		   isnull([7], 0) as Julho,
		   isnull([8], 0) as Agosto,
		   isnull([9], 0) as Setembro,
		   isnull([10], 0) as Outubro,
		   isnull([11], 0) as Novembro,
		   isnull([12], 0) as Dezembro
	from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP
	inner join PAGAMENTO P on PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO 
	where P.SUPA_NA_CODIGO = 5) Tab1
	pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in  ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
)
select  0.95 * Janeiro,0.95 * Fevereiro,0.95 * Março,0.95 * Abril,
		0.95 * Maio,0.95 * Junho,0.95 * Julho,0.95 * Agosto,
		0.95 * Setembro,0.95 * Outubro,0.95 * Novembro,0.95 * Dezembro
from cte;