POVIT行转列

 

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, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 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,S002,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,2,LEN(@HEADER)-1)
SELECT @HEADER

DECLARE @SQL NVARCHAR(MAX)
SET @SQL=N'SELECT B.SNO,'+@HEADER+' FROM (SELECT [sno],[Subno],[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,'')+',' + SubName FROM [TestDB].[Study].[Subject]
SET @HEADER=SUBSTRING(@HEADER,2,LEN(@HEADER)-1)
SELECT @HEADER

DECLARE @SQL NVARCHAR(MAX)
SET @SQL=N'SELECT B.Sname,'+@HEADER+' FROM (SELECT 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