行と列の入替ができました。
↓
SELECT
ct,
COALESCE ([1], 0) '1',
COALESCE ([2], 0) '2',
COALESCE ([3], 0) '3',
COALESCE ([4], 0) '4',
COALESCE ([5], 0) '5'
FROM
(
SELECT
ct,
id,
item
FROM
(
SELECT
id,
ct1,
ct2,
ct3
FROM
test
) p UNPIVOT (item FOR ct IN (ct1, ct2, ct3)) AS unpvt
) AS A PIVOT (min (item) for id In ([1], [2], [3], [4], [5])) Base
====================================================================
参考
PIVOT と UNPIVOT の使用
SQLServerで複数行を１行にしたい。 - QA@IT
====================================================================
同じ結果を得られます。row_numberを使用
SELECT
ct,
COALESCE([1],0) '1行目' ,
COALESCE([2],0) '2行目',
COALESCE([3],0) '3行目',
COALESCE([4],0) '4行目',
COALESCE([5],0) '5行目'
FROM
(
SELECT row_number() over(partition by ct order by ct) num,ct,item
FROM (
SELECT ct,item
FROM
(SELECT id,ct1,ct2,ct3
FROM test) p
UNPIVOT
(item FOR ct IN
(ct1, ct2, ct3)
) AS unpvt
) AS A
) AS B
PIVOT
(
min(item) for num In ([1],[2],[3],[4],[5])
) Base;
====================================================================
未整形
====================================================================
未整形
SELECT id,ct1,ct2,ct3 FROM test
SELECT ct,id,item FROM (
SELECT id,ct1,ct2,ct3 FROM test
) p UNPIVOT (item FOR ct IN (ct1, ct2, ct3)) AS unpvt
SELECT ct,COALESCE ([1], 0) '1',COALESCE ([2], 0) '2',COALESCE ([3], 0) '3',COALESCE ([4], 0) '4',COALESCE ([5], 0) '5'
FROM (
SELECT ct,id,item FROM (
SELECT id,ct1,ct2,ct3 FROM test
) p UNPIVOT (item FOR ct IN (ct1, ct2, ct3)) AS unpvt
) AS A PIVOT (min (item) for id In ([1], [2], [3], [4], [5])) Base
====================================================================
CREATE TABLE [dbo].[test](
====================================================================
SELECT 'CT1' AS ID, [1],[2],[3],[4],[5]
FROM
(SELECT ID,CT1 FROM test) AS A
PIVOT (MIN(CT1) FOR ID IN ([1],[2],[3],[4],[5])) AS B
====================================================================
====================================================================
CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [datetime] NULL,
[CT1] [int] NULL,
[CT2] [int] NULL,
[CT3] [int] NULL
) ON [PRIMARY];
TRUNCATE TABLE test;
INSERT INTO test VALUES ('2015/02/01',101,102,503);
INSERT INTO test VALUES ('2015/02/02',201,202,403);
INSERT INTO test VALUES ('2015/02/03',301,302,303);
INSERT INTO test VALUES ('2015/02/04',401,402,203);
INSERT INTO test VALUES ('2015/02/05',501,502,103);
====================================================================