-- UNPIVOT 으로 될꺼예요. 

WITH tbl (idx, a,b,c,d,e,f,g,h,i) AS (
	SELECT 1, '1000','2000','3000','4000','5000','6000','7000','8000','9000'
	UNION ALL  
	SELECT 2, '1100','2200','3300','4400','5500','6600','7700','8800','9900'
)
SELECT 
	idx 
	, min( CASE WHEN sortidx = 1 THEN kiki END ) AS z1
	, min( CASE WHEN sortidx = 2 THEN kiki END ) AS z2
	, min( CASE WHEN sortidx = 3 THEN kiki END ) AS z3
FROM (
	SELECT 
		idx, kiki, cols
		, (RANK() OVER(PARTITION BY idx ORDER BY cols)+2)/3 AS sortidx
		, (ROW_NUMBER() OVER(PARTITION BY idx ORDER BY cols)-1)%3 AS grpidx
	FROM tbl
		UNPIVOT ( kiki FOR cols IN (a,b,c,d,e,f,g,h,i) ) AS unpvt
) tmp
GROUP BY idx, grpidx
ORDER BY idx, grpidx

Oracle 에서는 다중 항목으로 피벗/언피벗 이 가능한데
MSSQL 에서는 그게 안되네요.
좀 더 복잡하게 구현해야 하겠네요.
 

WITH tbl(idx, a, b, c, d, e, f, g, h, i) AS
(
SELECT 1, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000 FROM dual
 UNION ALL 
SELECT 2, 1100, 2200, 3300, 4400, 5500, 6600, 7700, 8800, 9900 FROM dual
)
-- Oracle --
SELECT *
  FROM tbl
 UNPIVOT ( (z1, z2, z3) FOR gb IN ( (a, d, g) AS 1
                                  , (b, e, h) AS 2
                                  , (c, f, i) AS 3
                                  ) )
;

WITH tbl(idx, a, b, c, d, e, f, g, h, i) AS
(
SELECT 1, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000
 UNION ALL 
SELECT 2, 1100, 2200, 3300, 4400, 5500, 6600, 7700, 8800, 9900
)
-- MSSQL --
SELECT *
  FROM (SELECT idx, z
             , CONCAT('z', (ROW_NUMBER() OVER(PARTITION BY idx ORDER BY gb) + 2) / 3) x
             , (ROW_NUMBER() OVER(PARTITION BY idx ORDER BY gb) - 1) % 3 + 1 gb
          FROM tbl
         UNPIVOT (z FOR gb IN (a, b, c, d, e, f, g, h, i)) a
        ) a
 PIVOT (MIN(z) FOR x IN ([z1], [z2], [z3])) a
 ORDER BY idx, gb
;