This is not a dynamic solution, the number of columns are set in the code.
😎
USE tempdb;
GO
DECLARE @POLHIST TABLE
(
POLICY_DETAILS_ID VARBINARY(16) NOT NULL
,HISTORY_ID INT NOT NULL
,CODE_ID VARCHAR(12) NOT NULL
);
INSERT INTO @POLHIST (POLICY_DETAILS_ID,HISTORY_ID,CODE_ID)
VALUES
(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA01')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA14')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA14');
;WITH CODE_COLS AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_RID
,PH.HISTORY_ID
,PH.CODE_ID
FROM @POLHIST PH
)
SELECT
PH.POLICY_DETAILS_ID
,PH.HISTORY_ID
,C1.CODE_ID AS CODE1
,C2.CODE_ID AS CODE2
,C3.CODE_ID AS CODE3
,C4.CODE_ID AS CODE4
,C5.CODE_ID AS CODE5
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_RID
,PH.POLICY_DETAILS_ID
,PH.HISTORY_ID
FROM @POLHIST PH
) AS PH
LEFT OUTER JOIN CODE_COLS C1 ON PH.HISTORY_ID = C1.HISTORY_ID AND PH.PH_RID = C1.PH_RID - 0
LEFT OUTER JOIN CODE_COLS C2 ON PH.HISTORY_ID = C2.HISTORY_ID AND PH.PH_RID = C2.PH_RID - 1
LEFT OUTER JOIN CODE_COLS C3 ON PH.HISTORY_ID = C3.HISTORY_ID AND PH.PH_RID = C3.PH_RID - 2
LEFT OUTER JOIN CODE_COLS C4 ON PH.HISTORY_ID = C4.HISTORY_ID AND PH.PH_RID = C4.PH_RID - 3
LEFT OUTER JOIN CODE_COLS C5 ON PH.HISTORY_ID = C5.HISTORY_ID AND PH.PH_RID = C5.PH_RID - 4
WHERE PH.PH_RID = 1;
Results
POLICY_DETAILS_ID HISTORY_ID CODE1 CODE2 CODE3 CODE4 CODE5
---------------------------------- ----------- ------------ ------------ ------------ ------------ ------------
0x0000EE3FC15640D791EA76DD43138797 1 MMALIA01 MMALIA02 MMALIA06 MMALIA14 NULL
0x0000EE3FC15640D791EA76DD43138797 2 MMALIA02 MMALIA06 MMALIA14 NULL NULL
And just for fun, a window function version of the same, works on 2012/2014
USE tempdb;
GO
DECLARE @POLHIST TABLE
(
POLICY_DETAILS_ID VARBINARY(16) NOT NULL
,HISTORY_ID INT NOT NULL
,CODE_ID VARCHAR(12) NOT NULL
);
INSERT INTO @POLHIST (POLICY_DETAILS_ID,HISTORY_ID,CODE_ID)
VALUES
(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA01')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA14')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA14');
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_RID
,PH.POLICY_DETAILS_ID
,PH.HISTORY_ID
,PH.CODE_ID AS PH_CODE1
,LEAD(PH.CODE_ID,1,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE2
,LEAD(PH.CODE_ID,2,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE3
,LEAD(PH.CODE_ID,3,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE4
,LEAD(PH.CODE_ID,4,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE5
FROM @POLHIST PH
) AS X WHERE X.PH_RID = 1
If you need to dynamically add columns, then take a look at Script to create dynamic PIVOT queries in SQL Server[/url] by Aaron Bertrand