SqlServer数据行转列

准备表和插入数据

CREATE TABLE [dbo].[Test](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Type] [nvarchar](30) NOT NULL,
	[Status] [nvarchar](30) NOT NULL,
	[AppNo] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_DocumentSet] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [unique_Test_Type_AppNo] UNIQUE NONCLUSTERED 
(
	[Type] ASC,
	[AppNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into Test
select "type1", "status_a", "100021"
union all
select "type2", "status_b", "100021"
union all
select "type1", "status_b", "100022"
union all
select "type2", "status_c", "100022"
union all
select "type1", "status_a", "100023"
union all
select "type2", "status_c", "100023"
union all
select "type1", "status_e", "100024"
union all
select "type2", "status_n", "100024"
union all
select "type1", "status_b", "100025"
union all
select "type2", "status_y", "100025"
union all
select "type1", "status_e", "100026"
union all
select "type2", "status_c", "100027"
GO

---

查询当前数据

select * from Test

查询结果：

---

当前数据行转列

select AppNo, max(case [Type] when N"type1" then status end) as "Type1 status", 
 max(case [Type] when N"type2" then status end) as "Type2 status"
FROM [dbo].[Test]
group by AppNo

查询结果：