Sql Server中实现行数据转为列显示

2022-09-12 14:20小5聊基础 Sql Server

这篇文章主要介绍了Sql Server中实现行数据转为列显示，文章基于Sql语句的相关资料展开如何实现数据转为列显的操作过程，需要的小伙伴可以参考一下

场景：行数据的某列值想作为字段列显示

1、效果如下

2、解决方案

使用pivot进行行转列，以及结合分组

3、代码如下

				?

									select * from(

									    select DeptName,InputCode

									    from FWD_Department

									    group by DeptName,InputCode

									) as a

									pivot(

									  max(InputCode) for DeptName in([随访中心],[全院],[家庭化产房],[妇科二],妇科一)

									) piv

创建表：

				?

									USE [test]

									GO

									/****** Object:  Table [dbo].[FWD_Department]    Script Date: 2022/3/11 14:50:39 ******/

									SET ANSI_NULLS ON

									GO

									SET QUOTED_IDENTIFIER ON

									GO

									SET ANSI_PADDING ON

									GO

									CREATE TABLE [dbo].[FWD_Department](

									    [id] [int] IDENTITY(1,1) NOT NULL,

									    [DeptName] [varchar](50) NOT NULL,

									    [InputCode] [nchar](10) NULL,

									 CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED 

									(

									    [DeptName] 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

									SET ANSI_PADDING OFF

									GO

表记录添加：

				?

									insert into one(DeptName,InputCode)

									values('随访中心','SFZX')

									insert into one(DeptName,InputCode)

									values('全院','QY')

									insert into one(DeptName,InputCode)

									values('家庭化产房','JTHCF')

									insert into one(DeptName,InputCode)

									values('妇科二','FKE')

									insert into one(DeptName,InputCode)

									values('妇科一','FKY')