上篇我们说到了纵列转横列,这篇讲下横列转纵列,具体代码:
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]GO
SET IDENTITY_INSERT [dbo].[EndLongChangeAcross] ON GO
INSERT [dbo].[EndLongChangeAcross] ([Id], [Name], [Chinese], [English], [Math]) VALUES (1, N'张三', 89, 12, 82)
GO
INSERT [dbo].[EndLongChangeAcross] ([Id], [Name], [Chinese], [English], [Math]) VALUES (2, N'李四', 78, 45, 24)
GO
SET IDENTITY_INSERT [dbo].[EndLongChangeAcross] OFF
GO
ALTER TABLE [dbo].[EndLongChangeAcross] ADD CONSTRAINT [DF_EndLongChangeAcross_Chinese] DEFAULT ((0)) FOR [Chinese]
GO
ALTER TABLE [dbo].[EndLongChangeAcross] ADD CONSTRAINT [DF_EndLongChangeAcross_English] DEFAULT ((0)) FOR [English]
GO
ALTER TABLE [dbo].[EndLongChangeAcross] ADD CONSTRAINT [DF_EndLongChangeAcross_Math] DEFAULT ((0)) FOR [Math]
GO
2.具体SQL
SELECT Name ,
'语文' AS 'Subject',
Chinese AS 'Score'
FROM dbo.EndLongChangeAcross
UNION ALL
SELECT Name ,
'英语',
English AS 'Score'
FROM dbo.EndLongChangeAcross
UNION ALL
SELECT Name ,
'数学' ,
Math AS 'Score'
FROM dbo.EndLongChangeAcross
ORDER BY Name DESC
具体效果: