Ralf Eisenreich

Here is an example using dynamic Pivot:

The Pivot columns (country short names) are dynamically generated from a query.
Single Quotations within Single Quotations can be realized by using 2 singe Quotations at once.

DECLARE @wave_id int
SET @wave_id = 2

– Populate table variable with list of MPC_SHORT_NAMES
SET NOCOUNT ON
DECLARE @T AS TABLE(y nvarchar(10) NOT NULL PRIMARY KEY)
INSERT INTO @T SELECT DISTINCT MPC_NAME_SHORT_EN_US FROM VL8C_DWH.VL8RS_DIM_MPCS WHERE WAVE_ID = @wave_id

– Construct the column list for the IN clause
– e.g., [D],[E],[F]
DECLARE @cols AS nvarchar(MAX), @y AS nvarchar(10)
SET @y = (SELECT MIN(y) FROM @T)
SET @cols = N”
WHILE @y IS NOT NULL
BEGIN
SET @cols = @cols + N’,[' + @y + N']‘
SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))
–SELECT @cols

– Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N’
SELECT
*
FROM
(
SELECT
KEY_TYPES.KEY_TYPE_NAME,
KEY_TYPES.KT_SORT_ORDER,
DATA.MPC_NAME_SHORT_EN_US,
DATA.K29*100 AS K29
FROM
(
SELECT
KC.KEY_TYPE_CLUSTER,
KC.KT_NAME_EU AS KEY_TYPE_NAME,
KC.KT_SORT_ORDER
FROM
VL8C_DWH.VL8RS_DIM_KEY_TYPE_CLUSTERS KC
WHERE
WAVE_ID = ‘ + CAST(@wave_id AS nvarchar) + N’
) KEY_TYPES

LEFT JOIN
(
SELECT
M.MPC_NAME_SHORT_EN_US,
KC.KEY_TYPE_CLUSTER,
FM.K29_CNP_INDEX_TOTAL_BUSINESS AS K29
FROM

VL8C_DWH.VL8RS_FACTS_MAIN FM

INNER JOIN
VL8C_DWH.VL8RS_DIM_KEY_TYPES K
ON FM.KEY_TYPE_ID = K.KEY_TYPE_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_KEY_TYPE_CLUSTERS KC
ON K.KEY_TYPE_CLUSTER_ID = KC.KEY_TYPE_CLUSTER_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_WAVES W
ON FM.WAVE_ID = W.WAVE_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_COMPETITORS C
ON FM.COMPETITOR_ID = C.COMPETITOR_ID

INNER JOIN
VL8C_DWH.VL8RS_DIM_MPCS M
ON FM.MPC_ID = M.MPC_ID

WHERE
W.WAVE_ID = ‘ + CAST(@wave_id AS nvarchar) + N’
AND C.COMPETITOR_NAME = ”MB” AND
M.EUX_MEMBER = 1 AND
(K.REPORT_RELEVANCY = ”B” OR K.REPORT_RELEVANCY = ”E”)
) DATA
ON KEY_TYPES.KEY_TYPE_CLUSTER = DATA.KEY_TYPE_CLUSTER

WHERE
DATA.K29 IS NOT NULL AND
DATA.MPC_NAME_SHORT_EN_US <> ”D”
) p
pivot
(
sum(K29)
for MPC_NAME_SHORT_EN_US
in (‘ + @cols + N’)
) as p’
PRINT @sql — for debugging
EXEC sp_executesql @sql