|
?
上篇我们说到了横列转纵列，下面就来说下纵列转横列
?
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
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
?
?
具体效果：
|