SqlServer列传行+动态sql

来源：互联网发布：九阴绝学金身升级数据编辑：程序博客网时间：2024/05/21 07:10

数据表

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)

阅读全文

0 0