subject_41551.htm

vc

<p>
序号：41551 发表者：wxf 发表日期：2003-05-27 17:52:41
<br>主题：入门者求助
<br>内容：请问各位高手，可不可以把某个表中某个列的行值变成某个视图的列？如表A中有个列名叫A_L,在此列下有三行A_L_1,A_L_2,A_L_3,现在把它转换成视图VA中的三个列列名为A_L_1,A_L_2,A_L_3。能实现吗？
<br><a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p>
<hr size=1>
<blockquote><p>
回复者：kf 回复日期：2003-05-28 00:16:50
<br>内容：对不起我不太懂但我是这样想的：<BR>create view VA<BR>as<BR>select @a_L_1,@_L_2,@a_L_3 <BR>where @a_L_1=(select top 1 * from A)<BR>and (@a_L_2 in (select top 2 * from A) and @a_L_1&lt;&gt;@a_L_2)<BR>and (@a_L_3 in (select top 3 * from A) <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and @a_L_3&lt;&gt;a_L_2<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and @a_L_3&lt;&gt;a_L_1)<BR>
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：wxf 回复日期：2003-05-28 13:29:02
<br>内容：非常感谢kf的指教，不过要是表A中的行是要动态增长的，可不可以在相关视图中的列也可自动增长呢？
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
<font color=red>答案被接受</font><br>回复者：kf 回复日期：2003-05-29 06:43:55
<br>内容：我在网上找到了个遍文章：<BR>http://www.csdn.net/develop/article/18/18622.shtm<BR>我摘录了其中的内容，原版式的运行图片哦。<BR>去看看吧。<BR>建表：<BR><BR>在查询分析器里运行：<BR><BR>CREATE TABLE [Test] (<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [id] [int] IDENTITY (1, 1) NOT NULL ,<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Source] [numeric](18, 0) NULL <BR><BR>) ON [PRIMARY]<BR><BR>GO<BR><BR>INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)<BR><BR>INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)<BR><BR>INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)<BR><BR>INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)<BR><BR>INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)<BR><BR>INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)<BR><BR>INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)<BR><BR>Go<BR>--用于：交叉表的列数是确定的<BR><BR>select name,sum(case subject when '数学' then source else 0 end) as '数学',<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sum(case subject when '英语' then source else 0 end) as '英语',<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sum(case subject when '语文' then source else 0 end) as '语文' <BR><BR>from test <BR><BR>group by name<BR><BR> <BR><BR> <BR><BR>--用于：交叉表的列数是不确定的<BR><BR>declare @sql varchar(8000)<BR><BR>set @sql = 'select name,'<BR><BR> <BR><BR>select @sql = @sql + 'sum(case subject when '''+subject+''' <BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;then source else 0 end) as '''+subject+''','<BR><BR>&nbsp;&nbsp;from (select distinct subject from test) as a<BR><BR> <BR><BR>select @sql = left(@sql,len(@sql)-1) + ' from test group by name'<BR><BR>exec(@sql)<BR><BR>go<BR><BR> <BR><BR> <BR>
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：wxf 回复日期：2003-05-29 08:38:26
<br>内容：问题已基本解决了，多谢kf的指点
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>