In that case, here is the dynamic sql version of the previous code...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
UID int,
GeoBusinessType VARCHAR(30),
GeoCountry varchar(30),
UIDCountry varchar(30),
UIDBusinessType varchar(20),
Revenue smallmoney);
INSERT #TestData (UID, GeoBusinessType, GeoCountry, UIDCountry, Revenue) VALUES
( '123456', 'Commercial', 'United States', 'United States', 100 )
, ( '123456', 'Consumer', 'United States', 'United States', 150 )
, ( '123456', 'Consumer', 'United States', 'United States', 200 )
, ( '678901', 'Commercial', 'United States', 'United States', 250 )
, ( '678901', 'Commercial', 'United States', 'United States', 300 )
, ( '123246', 'Consumer', 'United States', 'United States', 350 )
, ( '123246', 'Consumer', 'France', 'France', 400 )
, ( '123246', 'Commercial', 'France', 'France', 450 )
, ( '123246', 'Commercial', 'France', 'France', 500 )
, ( '246488', 'Consumer', 'France', 'France', 550 )
, ( '246488', 'Consumer', 'France', 'France', 600 )
, ( '246488', 'Consumer', 'France', 'France', 650 )
, ( '246488', 'Consumer', 'France', 'France', 700 )
, ( '492968', 'Consumer', 'France', 'France', 750 )
, ( '492968', 'Consumer', 'Germany', 'Germany' , 800 )
, ( '492968', 'Consumer', 'Germany', 'Germany' , 850 )
, ( '985929', 'Consumer', 'Germany', 'Germany' , 900 )
, ( '985929', 'Consumer', 'Germany', 'Germany' , 950 )
, ( '985929', 'Commercial', 'Germany', 'Germany' , 1000 )
, ( '985929', 'Commercial', 'Germany', 'Germany' , 1050 )
, ( '197189', 'Consumer', 'Germany', 'Germany', 1100 )
, ( '197189', 'Consumer', 'India', 'India' , 1150 )
, ( '197189', 'Commercial', 'India', 'India' , 1200 )
, ( '197189', 'Commercial', 'India', 'India' , 1250 );
UPDATE td SET
td.UIDBusinessType = td.GeoBusinessType
FROM
#TestData td;
--SELECT * FROM #TestData td;
DECLARE
@GeoC VARCHAR(8000) = '',
@UidC VARCHAR(8000) = '',
@sql VARCHAR(8000) = '',
@DeBug BIT = 0;
WITH
cte_DistinctCountry AS (
SELECT
x.Country,
ColOrder = ROW_NUMBER() OVER (ORDER BY CASE x.Country WHEN 'United States' THEN 1 WHEN 'France' THEN 2 WHEN 'Germany' THEN 3 WHEN 'India' THEN 4 ELSE 999 END , x.Country)
FROM
#TestData td
CROSS APPLY ( VALUES (td.GeoCountry), (td.UIDCountry) ) x (Country)
GROUP BY
x.Country
)
SELECT
@GeoC = CONCAT(@GeoC, '
[GeoCountry_', dc.Country, '] = MAX(CASE WHEN x.Label = ''GeoCountry'' AND x.Value = ''', dc.Country, ''' THEN ''GeoCountry'' END),'),
@UidC = CONCAT(@UidC, '
[UIDCountry_', dc.Country, '] = MAX(CASE WHEN x.Label = ''UIDCountry'' AND x.Value = ''', dc.Country, ''' THEN ''UIDCountry'' END),')
FROM
cte_DistinctCountry dc
ORDER BY
dc.ColOrder;
SET @sql = CONCAT(@sql, '
SELECT
td.UID,
GeoBusinessType_Commercial = MAX(CASE WHEN x.Label = ''GeoBusinessType'' AND x.Value = ''Commercial'' THEN ''GeoBusinessType'' END),
GeoBusinessType_Consumer = MAX(CASE WHEN x.Label = ''GeoBusinessType'' AND x.Value = ''Consumer'' THEN ''GeoBusinessType'' END),',
@GeoC,
@UidC, '
UIDBusinessType_Commercial = MAX(CASE WHEN x.Label = ''UIDBusinessType'' AND x.Value = ''Commercial'' THEN ''UIDBusinessType'' END),
UIDBusinessType_Consumer = MAX(CASE WHEN x.Label = ''UIDBusinessType'' AND x.Value = ''Consumer'' THEN ''UIDBusinessType'' END),
Revenue = MAX(x.Revenue)
FROM
#TestData td
CROSS APPLY ( VALUES
(''GeoBusinessType'', td.GeoBusinessType, td.Revenue),
(''GeoCountry'', td.GeoCountry, 0),
(''UIDCountry'', td.UIDCountry, 0),
(''UIDBusinessType'', td.UIDBusinessType, 0)
) x (Label, Value, Revenue)
GROUP BY
td.UID;');
IF @DeBug = 1
BEGIN
PRINT (@sql);
END;
ELSE
BEGIN
EXEC (@sql);
END;
