December 21, 2009 at 8:53 am
CREATE TABLE #ProductCategory
(
ProductCategoryIDINT IDENTITY,
ProductCategoryNameVARCHAR(30)
)
INSERT #ProductCategory
VALUES('Test1'),('Test2'),('Test3'),('Test4'),('Test5')
CREATE TABLE #ContractProductCategory
(
ContractDetailIDINT,
ProductCategoryIDINT
)
SELECT * FROM #ProductCategory
INSERT #ContractProductCategory
SELECT 1,ProductCategoryID FROM #ProductCategory
UNION ALL
SELECT 1,ProductCategoryID FROM #ProductCategory
WHERE ProductCategoryID > 3
UNION ALL
SELECT 3,ProductCategoryID FROM #ProductCategory
WHERE ProductCategoryID < 3
UNION ALL
SELECT 4,ProductCategoryID FROM #ProductCategory
WHERE ProductCategoryID > 3 and ProductCategoryID < 5
CREATE TABLE #result
(
ContractDetailIDINT,
ProductCategoryVARCHAR(MAX)
)
DELETE #result
INSERT #result VALUES(1,NULL)
INSERT #result VALUES(1,NULL)
INSERT #result VALUES(2,NULL)
INSERT #result VALUES(3,NULL)
INSERT #result VALUES(4,NULL)
GO
CREATE TABLE #tmp
(
IDINTIDENTITY,
ContractDetailIDINT,
ProductCategoryNameVARCHAR(30),
CategoryStrVARCHAR(MAX)
)
SELECT * FROM #tmp
SELECT * FROM #result
UPDATE #result SET ProductCategory = NULL
DELETE #tmp
SELECT * FROM #ProductCategory
SELECT * FROM #ContractProductCategory
SELECT * FROM #result
DECLARE@contractDetailIDINT,
@prevContractDetailIDINT=0,
@categoryStrVARCHAR(MAX)
INSERT#tmp(ContractDetailID, ProductCategoryName)
SELECTR.ContractDetailID,
ProductCategoryName
FROM#result R,
#ContractProductCategoryCPC,
#ProductCategoryPC
WHERER.ContractDetailID=CPC.ContractDetailID
ANDCPC.ProductCategoryID=PC.ProductCategoryID
GROUP BY R.ContractDetailID, ProductCategoryName
ORDER BY R.ContractDetailID, ProductCategoryName
UPDATE#tmp
SET@contractDetailID=ContractDetailID,
@categoryStr=CASE WHEN @prevContractDetailID != @contractDetailID THEN '' ELSE @categoryStr + ',' END + ProductCategoryName,
@prevContractDetailID=@contractDetailID,
CategoryStr=@categoryStr
UPDATER
SETProductCategory=T.ProductCategoryName
FROM#resultR,
(
SELECTContractDetailID,
ProductCategoryName=MAX(CategoryStr)
FROM#tmp
GROUP BY ContractDetailID
) T
WHERER.ContractDetailID=T.ContractDetailID
December 21, 2009 at 4:06 pm
In addition to what Braindonor has said, please post table structure and actual execution plan with your specific questions. Doing this enables forum members to best help you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2009 at 1:57 am
Addition to Jason's comment I would say post your question in proper way that everyone should understand quickly.
Abhijit - http://abhijitmore.wordpress.com
December 23, 2009 at 6:05 pm
I agree with everyone above.
You will probably need to create a cursor for what you are trying to do.
December 24, 2009 at 3:09 pm
It does not look promising that we will see any of the additional information necessary to best assist on this thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply