|
?
CREATE TABLE [Study].[Student](
?[Sno] [varchar](10) NOT NULL,
?[Sname] [nvarchar](20) NOT NULL,
?[ClassID] [varchar](10) NOT NULL,
PRIMARY KEY CLUSTERED
(
?[Sno] 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
CREATE TABLE [Study].[Score](
?[ScoreID] [int] IDENTITY(1,1) NOT NULL,
?[sno] [varchar](10) NOT NULL,
?[Subno] [varchar](10) NOT NULL,
?[scoreNumber] [int] NULL,
PRIMARY KEY CLUSTERED
(
?[ScoreID] ASC
)WITH (PAD_INDEX? = OFF,ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:? ForeignKey [FK__Score__sno__0DAF0CB0]??? Script Date: 05/17/2013 11:08:32 ******/
ALTER TABLE [Study].[Score]? WITH CHECK ADD FOREIGN KEY([sno])
REFERENCES [Study].[Student] ([Sno])
GO
/****** Object:? ForeignKey [FK__Score__Subno__0EA330E9]??? Script Date: 05/17/2013 11:08:32 ******/
ALTER TABLE [Study].[Score]? WITH CHECK ADD FOREIGN KEY([Subno])
REFERENCES [Study].[Subject] ([Subno])
GO
SELECT SNO,S001,S002,S003
FROM(
?SELECT ?[sno],[Subno],[scoreNumber] FROM [TestDB].[Study].[Score]
)AS H
PIVOT
(
?MAX([scoreNumber]) FOR [Subno] IN (S001,S003)
)AS PVT
GO
--2、连接其他表
DECLARE @HEADER NVARCHAR(MAX)
SELECT @HEADER=ISNULL(@HEADER,'')+',' + SubName FROM [TestDB].[Study].[Subject]
SET @HEADER=SUBSTRING(@HEADER,2,LEN(@HEADER)-1)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL=N'SELECT B.SNO,B.Sname,'+@HEADER+' FROM (SELECT S2.SNO,S2.Sname,S3.SubName,S1.scoreNumber FROM [TestDB].[Study].[Score] AS S1
JOIN [TestDB].[Study].[Student]? AS S2 ON S1.[sno] = S2.[Sno]
JOIN [TestDB].[Study].[Subject] AS S3 ON S1.[Subno] = S3.[Subno]) AS H PIVOT (
MAX([scoreNumber]) FOR SubName IN ('+@HEADER+')
) AS B'
exec (@SQL)
go
/*动态行转列*/
--1、不需要连接其他表
DECLARE @HEADER NVARCHAR(MAX)
SELECT @HEADER=ISNULL(@HEADER,' + Subno FROM [TestDB].[Study].[Subject]
SET @HEADER=SUBSTRING(@HEADER,LEN(@HEADER)-1)
SELECT @HEADER
DECLARE @SQL NVARCHAR(MAX)
SET @SQL=N'SELECT B.SNO,'+@HEADER+' FROM (SELECT [sno],[scoreNumber] FROM [TestDB].[Study].[Score]) AS H PIVOT (
MAX([scoreNumber]) FOR SUBNO IN ('+@HEADER+')
) AS B'
exec (@SQL)
GO
--连接其他表
DECLARE @HEADER NVARCHAR(MAX)
SELECT @HEADER=ISNULL(@HEADER,LEN(@HEADER)-1)
SELECT @HEADER
DECLARE @SQL NVARCHAR(MAX)
SET @SQL=N'SELECT B.Sname,'+@HEADER+' FROM (SELECT S2.Sname,S1.scoreNumber FROM [TestDB].[Study].[Score] AS S1
JOIN [TestDB].[Study].[Student]? AS S2 ON S1.[sno] = S2.[Sno]
JOIN [TestDB].[Study].[Subject] AS S3 ON S1.[Subno] = S3.[Subno]) AS H PIVOT (
MAX([scoreNumber]) FOR SubName IN ('+@HEADER+') ) AS B' exec (@SQL) GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
