如何基于使用SQL Server 2005的SQL语句集创建视图?
问题描述:
我写了一些SQL语句,最后给出了一个表。我想创建一整套语句作为“视图”。我如何获得? 语句被置于下面:如何基于使用SQL Server 2005的SQL语句集创建视图?
------------ Creating TempTable(#MasterTable) ----------------------
CREATE TABLE #MasterTable
(ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50),
D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50))
------------ Creating TempTable(#TempItems) ----------------------
CREATE TABLE #TempItems(ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50),
D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50))
------------ Creating & Inserting TempTable(@Dim) ----------------------
DECLARE @Dim TABLE (DCodes VARCHAR(100))
INSERT INTO @Dim SELECT D1_Code FROM MAS_SizeType WHERE D1_Code <> ''
INSERT INTO @Dim SELECT D2_Code FROM MAS_SizeType WHERE D2_Code <> ''
INSERT INTO @Dim SELECT D3_Code FROM MAS_SizeType WHERE D3_Code <> ''
------------ Inserting data into TempTable(#MasterTable) ----------------------
INSERT INTO #MasterTable
SELECT STR_Item.ItemID, STR_Item.ItemName, STR_Item_Specifications.SpecificationName,
STR_Item.D1, STR_Item.D2, STR_Item.D3, MAS_SizeType.D1_Code, MAS_SizeType.D2_Code,
MAS_SizeType.D3_Code
FROM STR_Item INNER JOIN
MAS_SizeType ON STR_Item.SizeTypeID = MAS_SizeType.SizeTypeID INNER JOIN
STR_Item_Specifications ON STR_Item.SpecificationID = STR_Item_Specifications.SpecificationID
-------------------- Inserting Data into #TempItems Table ----------------------------
INSERT INTO #TempItems
SELECT
*
FROM #MasterTable
------------------Cursor for All dimensions details into single row for each items ----------------------
DECLARE @DCode VARCHAR(MAX), @Cnt INT
SET @Cnt = 0
DECLARE ColAdd CURSOR FOR
SELECT DISTINCT DCodes FROM @Dim
OPEN ColAdd
FETCH NEXT FROM ColAdd INTO @DCode
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Cnt = 1
EXECUTE ('ALTER TABLE #TempItems ADD ' + @DCode + ' VARCHAR(50)')
EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D1
FROM #MasterTable M
INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D1_Code = ''' + @DCode + '''')
EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D2
FROM #MasterTable M
INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D2_Code = ''' + @DCode + '''')
EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D3
FROM #MasterTable M
INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D3_Code = ''' + @DCode + '''')
FETCH NEXT FROM ColAdd INTO @DCode
END
CLOSE ColAdd
DEALLOCATE ColAdd
IF (@Cnt = 1)
BEGIN
SELECT @DCode = STUFF((SELECT DISTINCT '],[' + ltrim(DCodes)
FROM @Dim
ORDER BY '],[' + ltrim(DCodes)
FOR XML PATH('')
), 1, 2, '') + ']'
EXECUTE ('SELECT ItemID, ItemName, Specifications, ' + @DCode + ' FROM #TempItems ORDER BY ItemName')
END
DROP TABLE #TempItems
DROP TABLE #MasterTable
答
这是一个存储过程:不是视图。一个视图是单个SELECT(可能带有CTE和UNION)
如果您需要从中选择一个,我建议尝试multi-statement table valued function。但是,有一些限制可能会阻止这一点。因人而异。和性能不错那么好
好的。我尝试使用表值函数。谢谢。 – thevan 2011-06-14 07:02:22
是否有可能使用表值函数获取动态表列? – thevan 2011-06-14 07:08:27
@thevan:不知道,没有尝试过。我会使用一个存储过程来处理这种复杂性的代码 – gbn 2011-06-14 08:03:50