-------------------------------------------------------------------------------------------------------------------------
--Test Table creation 
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #TABLE (COL1 VARCHAR(100),AAA INT,BBB INT,CCC INT)
INSERT INTO #TABLE VALUES
('CAT' ,1 ,3 ,3),
('CAT' ,1 ,2 ,2),
('CAT' ,1 ,4 ,4),
('DOG' ,2 ,3 ,3),
('DOG' ,5 ,5 ,5),
('MICE' ,1 ,1 ,1)
-------------------------------------------------------------------------------------------------------------------------
--Retriving Column names dynamically and applying DYNAMIC PIVOT
-------------------------------------------------------------------------------------------------------------------------

DECLARE @Query VARCHAR(MAX)
DECLARE @Col   VARCHAR(MAX) = STUFF((SELECT     ','+MIN(col) FROM 
(select   Col1, col+CAST(Rn AS VARCHAR(100)) col,Rn,  value
from (SELECT  *,ROW_Number ()OVER (PARTITION BY Col1 ORDER BY COl1) AS Rn FROM #Table)a
cross apply
(
  select 'AAA', convert(varchar(10), AAA, 120) union all
  select 'BBB', convert(varchar(10), BBB, 120) union all
  select 'CCC', convert(varchar(10), CCC, 120)
) c (col, value)
)a 
GROUP BY col,Rn
ORDER BY Rn
FOR XML PATH('')),1,1,'')

SET @Query =N'
SELECT Col1,'+@Col+' FROM
(
select Col1, col+CAST(Rn AS VARCHAR(100)) col,  value
from (SELECT  *,ROW_Number ()OVER (PARTITION BY Col1 ORDER BY COl1) AS Rn FROM #Table)a
cross apply
(
  select ''AAA'', convert(varchar(10), AAA, 120) union all
  select ''BBB'', convert(varchar(10), BBB, 120) union all
  select ''CCC'', convert(varchar(10), CCC, 120)
) c (col, value)
)a
PIVOT (MAX(value) FOR Col in('+@Col+')) AS PVT'
EXECUTE (@Query)


-------------------------------------------------------------------------------------------------------------------------
--MeanWhile there are two Static approches to achieve this 
--1.Using Case Statements
--2.CTE Concept
-------------------------------------------------------------------------------------------------------------------------
SELECT		Col1
			,MAX(CASE WHEN Rn =1 THEN AAA END) AS AAA
			,MAX(CASE WHEN Rn =1 THEN BBB END) AS BBB
			,MAX(CASE WHEN Rn =1 THEN CCC END) AS CCC
			,MAX(CASE WHEN Rn =2 THEN AAA END) AS AAA
			,MAX(CASE WHEN Rn =2 THEN BBB END) AS BBB
			,MAX(CASE WHEN Rn =2 THEN CCC END) AS CCC
			,MAX(CASE WHEN Rn =3 THEN AAA END) AS AAA
			,MAX(CASE WHEN Rn =3 THEN BBB END) AS BBB
			,MAX(CASE WHEN Rn =3 THEN CCC END) AS CCC
FROM (SELECT  *,ROW_Number ()OVER (PARTITION BY Col1 ORDER BY COl1) AS Rn FROM #Table)a
GROUP BY Col1
-------------------------------------------------------------------------------------------------------------------------

;WITH cte1
AS(
SELECT  *,ROW_Number ()OVER (PARTITION BY Col1 ORDER BY COl1) AS Rn FROM #Table
)
,cte2
AS
(
 SELECT * FROM cte1 WHERE  Rn=1
)
,cte3
AS(
 SELECT * FROM cte1 WHERE Rn =2
)
,cte4
AS
(
 SELECT * FROM cte1 WHERE Rn =3
)
SELECT t2.COl1,t2.AAA,t2.BBB,t2.CCC,t3.AAA,t3.BBB,t3.CCC,t4.AAA,t4.BBB,t4.CCC FROM 
cte2 As t2 LEFT JOIN cte3 As t3 ON t2.COl1= t3.COl1
LEFT JOIN  cte4 t4 on t4.COl1 = t2.COl1


-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
--DROP TABLE
DROP TABLE #TABLE
-------------------------------------------------------------------------------------------------------------------------