SqlServer列传行+动态sql

数据表

CREATE TABLE [dbo].[GuangJunOddsInfo](

[GameID] [varchar](20) NOT NULL,
[CanSaiID] [varchar](20) NOT NULL,
[CanSaiName] [varchar](50) NOT NULL,
[Odds] [decimal](7, 3) NOT NULL,
[UpDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_GuangJunOddsInfo] PRIMARY KEY CLUSTERED 
(
[GameID] ASC,
[CanSaiID] 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
--添加数据


INSERT INTO [dbo].[GuangJunOddsInfo]
           ([GameID]
           ,[CanSaiID]
           ,[CanSaiName]
           ,[Odds]
           ,[UpDateTime])
     VALUES
           (<GameID, varchar(20),>
           ,<CanSaiID, varchar(20),>
           ,<CanSaiName, varchar(50),>
           ,<Odds, decimal(7,3),>
           ,<UpDateTime, datetime,>)
GO
--存储过程+动态sql+行转列
create proc UpdateGuangJun_CanShuID
AS
declare @a varchar(max)
set @a=' '
select @a=@a+cansaiid+',' from GuangJunOddsInfo group by cansaiid order by cansaiid
select @a=left(@a,len(@a)-1)
set @a='alter VIEW GuangJunOddsInfo_View AS select 
    GameID,'+@a+'
from 
    (select gameid,cansaiid,(CanSaiName+''[''+CAST(odds AS VARCHAR(10))+'']'') AS CanSaiName from GuangJunOddsInfo) a 
pivot 
     (max(CanSaiName) for cansaiID in('+@a+'))b'
print @a
exec(@a)