[原创]sqlserver2005-pivot和unpivot的用法
CREATE TABLE [dbo].[Table1](
[id] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[c1] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[c2] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[c3] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2](
[id] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[val] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
select * from table1
1 aa bb cc
2 dd ee ff
insert into table2(id,val)
select id,val
from
(
select * from table1
)a
UNPIVOT
(
val for DataType in( c1,c2,c3)
)as b;
select * from table2
1 aa
1 bb
1 cc
2 dd
2 ee
2 ff
