如何为下面提到的问题创建存储过程 [英] how to create store procedure for below mention problem
问题描述
在数据库中有4列。
ProductName价格规格值
键盘560电路是
键盘560技术没有
键盘560多好
所以,我需要productName和价格只有一次...如何创建商店程序对于这个问题???
ProductName价格规格价值
键盘560电路是
techs no
多好。
i需要输出类型
为每个需要写一个单独的存储过程。
测试它:
创建 表# tbl(ProductName VARCHAR ( 30 ),价格 MONEY ,Specification VARCHAR ( 30 ),[Value] VARCHAR ( 10 ))
INSERT INTO #tbl(ProductName,Price,Specification,[Value])
VALUES (' keyboard', 560 ,' circuit',' yes')
INSERT < span class =code-keyword> INTO #tbl(ProductName,Price,Specification,[Value])
VALUES (' keyboard', 560 ,' techs',' 否')
INSERT INTO #tbl(ProductName,Price,规范,[值])
VALUES (' keyboard', 560 ,' multi ',' good')
DEC LARE @cols VARCHAR ( 300 )
DECLARE @ dt VARCHAR ( 2000 )
DECLARE @ pt VARCHAR ( 4000 )
SET @cols = STUFF(( SELECT DISTINCT ' ],[' +规范
< span class =code-keyword> FROM #tbl
ORDER BY ' ],[' +规范
FOR XML PATH(' ')), 1 , 2 ,' ')+ ' ]'
SET @ dt = ' SELECT ProductName,Price,Specification,ValID = CASE WHEN [Value] =''no''那么0当[Value] =''yes''那么1 ELSE 2 END' +
' FROM #tbl'
- EXEC(@dt)
SET @ pt = ' SELECT ProductName,Price,' + < span class =code-sdkkeyword> @cols + ' ' +
' FROM(' + @ dt + ' )AS DT' +
' PIVOT( MAX(ValID)FOR规范IN(' + @cols + ' ))AS PT'
EXEC ( @ pt )
DROP TABLE #tbl
在上面的例子中使用简单的技巧。我将[Value]列中的值转换为数值:no
- >0
;是
- >1
,其他 - >2
。为什么?使用agregate功能并创建数据透视表。
结果:
ProductName价格cicuit多技术
键盘560,00 1 2 0
SELECT CASE WHEN y.rowNumber = 1 那么 ' keyboard' ELSE ' ' END AS ProductName, CASE WHEN y.rowNumber = < span class =code-digit> 1 那么 ' 560' ELSE ' ' END AS price,y.Specification,y.Value
FROM (
SELECT Row_Number() OVER ( ORDER BY 规范) AS rowNumber,x.Specication,x.Value
FROM (
SELECT 规范,值
FROM 表
WHERE ProductName = ' keyboard' AND price = 560
)x
)y
in database there are 4 columns .
ProductName Price Specification Value
keyboard 560 circuit yes
keyboard 560 techs no
keyboard 560 multi good
so, i required productName and price only one time ...how to create store procedure for this problem???
ProductName Price Specification Value
keyboard 560 circuit yes
techs no
multi good.
i required that type of output
Write a separate Stored Procedure for each need.
Test it:
CREATE TABLE #tbl (ProductName VARCHAR(30), Price MONEY, Specification VARCHAR(30), [Value] VARCHAR(10)) INSERT INTO #tbl (ProductName, Price, Specification, [Value]) VALUES('keyboard', 560, 'circuit', 'yes') INSERT INTO #tbl (ProductName, Price, Specification, [Value]) VALUES('keyboard', 560, 'techs', 'no') INSERT INTO #tbl (ProductName, Price, Specification, [Value]) VALUES('keyboard', 560, 'multi', 'good') DECLARE @cols VARCHAR(300) DECLARE @dt VARCHAR(2000) DECLARE @pt VARCHAR(4000) SET @cols = STUFF((SELECT DISTINCT '],[' + Specification FROM #tbl ORDER BY '],[' + Specification FOR XML PATH('')),1,2,'') + ']' SET @dt = 'SELECT ProductName, Price, Specification, ValID = CASE WHEN [Value]=''no'' THEN 0 WHEN [Value]=''yes'' THEN 1 ELSE 2 END ' + 'FROM #tbl' --EXEC(@dt) SET @pt = 'SELECT ProductName, Price, ' + @cols + ' ' + 'FROM (' + @dt + ') AS DT ' + 'PIVOT(MAX(ValID) FOR Specification IN(' + @cols + ')) AS PT' EXEC(@pt) DROP TABLE #tbl
In above example is use simple trick. I "convert" values from [Value] column into numeric values:no
->0
;yes
->1
, other->2
. Why? To use agregate function and to create pivot table.
Result:
ProductName Price cicuit multi techs keyboard 560,00 1 2 0
SELECT CASE WHEN y.rowNumber = 1 THEN 'keyboard' ELSE '' END AS ProductName, CASE WHEN y.rowNumber = 1 THEN '560' ELSE '' END AS price, y.Specification, y.Value FROM ( SELECT Row_Number() OVER (ORDER BY Specification) AS rowNumber, x.Specication, x.Value FROM ( SELECT Specification, Value FROM Table WHERE ProductName = 'keyboard' AND price = 560 ) x ) y
这篇关于如何为下面提到的问题创建存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!