你的位置:首页 > 数据库

[数据库]MSSQL纵列转横列


   上篇我们说到了横列转纵列,下面就来说下纵列转横列

   1.建表

CREATE TABLE [dbo].[EndLongChangeAcross](  [Id] [INT] IDENTITY(1,1) NOT NULL,  [Name] [NVARCHAR](50) NOT NULL,  [Chinese] [INT] NOT NULL,  [English] [INT] NOT NULL,  [Math] [INT] NOT NULL, CONSTRAINT [PK_EndLongChangeAcross] 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]) ON [PRIMARY]GOALTER TABLE [dbo].[EndLongChangeAcross] ADD CONSTRAINT [DF_EndLongChangeAcross_Chinese] DEFAULT ((0)) FOR [Chinese]GOALTER TABLE [dbo].[EndLongChangeAcross] ADD CONSTRAINT [DF_EndLongChangeAcross_English] DEFAULT ((0)) FOR [English]GOALTER TABLE [dbo].[EndLongChangeAcross] ADD CONSTRAINT [DF_EndLongChangeAcross_Math] DEFAULT ((0)) FOR [Math]GO

2.具体SQL

SELECT Name ,    '语文' AS 'Subject',    Chinese AS 'Score'FROM  dbo.EndLongChangeAcrossUNION ALLSELECT Name ,    '英语',    English AS 'Score'FROM  dbo.EndLongChangeAcrossUNION ALLSELECT Name ,    '数学' ,    Math AS 'Score'FROM  dbo.EndLongChangeAcrossORDER BY Name DESC

具体效果: