SQLTeam.com | Weblogs | Forums

Update a table using a pivot table

Problem

This is part of stored procedure I have written. I want to write an update clause that will update the pivoted table on the last page. Kindly assist

ORIGINAL TABLE THAT I AM PIVOTING

SELECT TOP (1000) [ID]
,[SiteID]
,[CustomerID]
,[OTID]
,[Bonus Score]
,[Casino Game Type Preference]
,[Churn]
,[SportChurn]
,[CasinoChurn]
,[Product Preference]
,[Sports Branch Preference]
,[Pre-match vs Live preference]
,[Platform Preference]
,[Value Score]
,[Payment Method Preference]
,[Financial Levels]
,[Preferred Bonus Type]
,[Preferred Promo Type]
,[UpdateDate]
FROM [ReportsBA].[dbo].[AggregatedSegmentation]

PIVOTED TABLE

IF OBJECT_ID('tempdb..#pivotsegment') IS NOT NULL DROP TABLE #pivotsegment;
SELECT [SiteID],[CustomerID],[OTID],[Segment],[Value],[UpdateDate] INTO #pivotsegment
FROM
(
SELECT
CAST([SiteID] AS NVARCHAR(255)) as [SiteID]
,CAST([CustomerID] AS NVARCHAR(255)) as [CustomerID]
,CAST([OTID] AS NVARCHAR(255)) as [OTID]
,CONVERT(NVARCHAR(255),[UpdateDate],121) as [UpdateDate]
,CAST([Bonus Score] AS NVARCHAR(255)) AS [Bonus Score]
,CAST([Casino Game Type Preference] AS NVARCHAR(255)) as [Casino Game Type Preference]
,CAST([Churn] AS NVARCHAR(255)) as [Churn]
,CAST([Product Preference] AS NVARCHAR(255)) as [Product Preference]
,CAST([Sports Branch Preference] AS NVARCHAR(255)) as [Sports Branch Preference]
,CAST([Pre-match vs Live preference] AS NVARCHAR(255)) as [Pre-match vs Live preference]
,CAST([Platform Preference] AS NVARCHAR(255)) as [Platform Preference]
,CAST([Value Score] AS NVARCHAR(255)) as [Value Score]
,CAST([Payment Method Preference] AS NVARCHAR(255)) as [Payment Method Preference]
,CAST([Financial Levels] AS NVARCHAR(255)) as [Financial Levels]
,CAST([Preferred Bonus Type] AS NVARCHAR(255)) as[Preferred Bonus Type]
,CAST([Preferred Promo Type] AS NVARCHAR(255))[Preferred Promo Type]
-- ,CAST([Prev Value] AS NVARCHAR(255)) as [Prev Value]

FROM [ReportsBA].[dbo].[AggregatedSegmentation]) AS a

UNPIVOT
(
[Value]
FOR [Segment] IN ([Bonus Score] ,[Casino Game Type Preference],[Churn],[Product Preference],[Sports Branch Preference],[Pre-match vs Live preference],[Platform Preference],[Value Score],[Payment Method Preference],[Financial Levels],[Preferred Bonus Type],[Preferred Promo Type])
) as unpiv;