数据库 PIVOT 行转列

最新推荐文章于 2024-01-18 09:30:40 发布

lyh1112

最新推荐文章于 2024-01-18 09:30:40 发布

阅读量682

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/lyh1112/article/details/9213567

版权

数据库专栏收录该内容

2 篇文章 1 订阅

订阅专栏

/****** Object: Table [dbo].[SC] Script Date: 06/28/2013 13:55:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SC](
[S#] [varchar](50) NOT NULL,
[C#] [varchar](50) NOT NULL,
[G] [int] NOT NULL,
CONSTRAINT [PK_SC] PRIMARY KEY CLUSTERED
(
[S#] ASC,
[C#] 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 [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S001', N'C004', 77)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S001', N'C006', 85)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S002', N'C002', 72)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S002', N'C004', 85)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S002', N'C006', 59)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S003', N'C001', 48)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S003', N'C002', 68)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S003', N'C003', 52)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S003', N'C004', 80)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S003', N'C005', 56)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S003', N'C006', 62)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S004', N'C001', 70)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S004', N'C002', 71)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S004', N'C003', 72)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S004', N'C005', 73)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S005', N'C004', 89)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S005', N'C006', 88)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S006', N'C001', 81)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S006', N'C002', 82)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S006', N'C003', 83)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S006', N'C005', 84)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S008', N'C001', 80)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S008', N'C002', 90)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S009', N'C001', 81)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S009', N'C002', 72)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S009', N'C003', 63)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S009', N'C004', 54)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S009', N'C005', 65)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S009', N'C006', 48)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S010', N'C001', 55)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S010', N'C002', 72)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S010', N'C003', 88)
INSERT [dbo].[SC] ([S#], [C#], [G]) VALUES (N'S010', N'C005', 56)
/****** Object: Table [dbo].[S] Script Date: 06/28/2013 13:55:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[S](
[S#] [varchar](50) NOT NULL,
[SN] [nvarchar](50) NOT NULL,
[SD] [nvarchar](100) NOT NULL,
[SA] [int] NOT NULL,
CONSTRAINT [PK_S] PRIMARY KEY CLUSTERED
(
[S#] 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 [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S001', N'Thomas Leung', N'VoiceCyber/VoiceCodes', 110)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S002', N'Peter Li', N'VoiceCodes', 112)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S003', N'Danny Wu', N'VoiceCodes', 119)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S004', N'Jason Tan', N'VoiceCodes', 114)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S005', N'Anna Hu', N'VoiceCodes', 1021)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S006', N'Jack Yan', N'VoiceCyber', 1010)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S007', N'Young Yang', N'VoiceCyber', 1025)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S008', N'Bruce Tang', N'VoiceCyber', 1022)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S009', N'Charley Chen', N'VoiceCyber', 1023)
INSERT [dbo].[S] ([S#], [SN], [SD], [SA]) VALUES (N'S010', N'Koala Fang', N'VoiceCyber', 1024)
/****** Object: Table [dbo].[C] Script Date: 06/28/2013 13:55:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[C](
[C#] [varchar](50) NOT NULL,
[CN] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[C#] 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 [dbo].[C] ([C#], [CN]) VALUES (N'C001', N'数据库基础知识')
INSERT [dbo].[C] ([C#], [CN]) VALUES (N'C002', N'操作系统基础知识')
INSERT [dbo].[C] ([C#], [CN]) VALUES (N'C003', N'C#入门与进阶')
INSERT [dbo].[C] ([C#], [CN]) VALUES (N'C004', N'会计基础')
INSERT [dbo].[C] ([C#], [CN]) VALUES (N'C005', N'Oracle系统管理')

INSERT [dbo].[C] ([C#], [CN]) VALUES (N'C006', N'税收基础')

数据库行转列->查询所有学生的全部成绩：

declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + CN from C group by CN
EXEC('SELECT * FROM (SELECT * FROM (SELECT S.SN,C.CN,SC.G FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C#)A)B PIVOT (MAX(G) FOR CN IN ('+@sql+'))D')