see
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955also see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=3914&CAT_ID=3&FORUM_ID=5&Forum_Title=Developer&Topic_Title=Reverse+pivot+table
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955also see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=3914&CAT_ID=3&FORUM_ID=5&Forum_Title=Developer&Topic_Title=Reverse+pivot+table
解决方案 »
- 关于类型转化的问题,存入数据库
- 简单的正则验证
- SqlParameter的一个疑问,代码是这样的
- DBhelper里的带存储过程的输入输出参数增删改查的方法
- 跨网段,如何获取客户端的MAC地址!求助!
- 帮忙介绍一下关于C#架构方面的书籍,谢谢,来者有分................
- 一个关于文件上传的小问题
- 重新安装单击版CRYSTAL 9后出现的问题
- 一F5运行就 试图运行项目时出错 无法启动调试 绑定句柄无效
- 100分,100分,100分问题,不骗人(只是发错地了,请高手看看),最先答对的给
- 哪里有asp.net的书下载,要vs.net方面编的,不是记事本编的,而且要vb.net的,好事出有因100分
- 为什么我的aspx运行的时候网页上一点东西都看不见呢?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=3914&CAT_ID=3&FORUM_ID=5&Forum_Title=Developer&Topic_Title=Reverse+pivot+table
should be exactly what you want, or try
DECLARE @sql varchar(4000), @table varchar(50)SELECT @sql = '', @table = 'yourTable'
SELECT @sql = @sql + 'SELECT NO, Convert(varchar(50),' + column_name + ') AS T FROM ' + table_name + ' UNION '
FROM information_schema.columns
WHERE table_name=@table AND column_name<>'ID'
SELECT @sql = Left(@sql,Len(@sql)-5)
print @sql
EXEC (@sql)
SELECT @sql = @sql + 'SELECT NO, Convert(varchar(50),' + column_name + ') AS T FROM ' + table_name + ' UNION '
FROM information_schema.columns
WHERE table_name=@table AND column_name<>'NO'
SELECT @sql = Left(@sql,Len(@sql)-5)
EXEC (@sql)
DECLARE @sql varchar(4000), @table varchar(50)SELECT @sql = '', @table = 'yourTable'
SELECT @sql = @sql + 'SELECT NO, Convert(varchar(50),' + name + ') AS T FROM ' + @table + ' UNION '
FROM syscolumns
WHERE object_name(id)=@table AND name<>'NO'
SELECT @sql = Left(@sql,Len(@sql)-5)
EXEC (@sql)
gocreate table yourtable (NO int, T01 int, T02 int, T03 int, T04 int, T05 int, T06 int, T07 int, T08 int)insert into yourtable values(1, 10, 12, 15, 18, 17, 20, 21, 23)insert into yourtable values(2, 8, 8, 10, 5, 4, 7, 9, 12)--select * from yourtableDECLARE @sql varchar(4000), @table varchar(50)SELECT @sql = '', @table = 'yourTable'
SELECT @sql = @sql + 'SELECT NO, Convert(varchar(50),' + name + ') AS T FROM ' + @table + ' UNION '
FROM syscolumns
WHERE object_name(id)=@table AND name<>'NO'
SELECT @sql = Left(@sql,Len(@sql)-5)
EXEC (@sql)
select sum('字段名') case '1' as,
.....
sql2000帮助有例子,哈哈
我用的是SQL SERVER 2000 呀﹐行嗎﹖
163 AA000039 2001-01-01 08:30:00.000 NULL 0 NULL
117 AA000070 2001-01-01 08:30:00.000 NULL 0 NULL
178 AA000053 2001-01-01 08:30:00.000 NULL 0 NULL
195 AA000085 2001-01-01 08:30:00.000 NULL 0 NULL
192 AA000082 2001-01-01 08:30:00.000 NULL 0 NULL
101 AA000055 2001-01-01 08:30:00.000 NULL 0 NULL
162 AA000038 2001-01-01 08:30:00.000 NULL 0 NULL
189 AA000079 2001-01-01 08:30:00.000 NULL 0 NULL
185 AA000078 2001-01-01 08:30:00.000 NULL 0 NULL
183 AA000001 2001-01-01 08:30:00.000 NULL 0 NULL
180 AA000054 2001-01-01 08:30:00.000 NULL 0 NULL
202 AA000092 2001-01-01 08:30:00.000 NULL 0 NULL
160 AA000036 2001-01-01 08:30:00.000 NULL 0 NULL
165 AA000041 2001-01-01 08:30:00.000 NULL 0 NULL
203 AA000004 2001-01-01 08:30:00.000 NULL 0 NULL
149 AA000025 2001-01-01 08:30:00.000 NULL 0 NULL
156 AA000032 2001-01-01 08:30:00.000 NULL 0 NULL
158 AA000034 2001-01-01 08:30:00.000 NULL 0 NULL
190 AA000080 2001-01-01 08:30:00.000 NULL 0 NULL
198 AA000088 2001-01-01 08:30:00.000 NULL 0 NULL
175 AA000050 2001-01-01 08:30:00.000 NULL 0 NULL
115 AA000068 2001-01-01 08:30:00.000 NULL 0 NULL
116 AA000069 2001-01-01 08:30:00.000 NULL 0 NULL
176 AA000051 2001-01-01 08:30:00.000 NULL 0 NULL
205 AA000093 2001-01-01 08:30:00.000 NULL 0 NULL
136 AA000013 2001-01-01 08:30:00.000 NULL 0 NULL
思歸大哥,我想要將其轉換為
empkey empno T R
的這種形式。
還有好多字段還有寫出來﹐我寫的時候出現了@sql變量長度不夠﹐我加到8000了﹐最大了﹐可還是不行
您看看有什么好的辦法嗎﹖真的很麻煩您了﹗不甚感激﹗﹗﹗﹗﹗﹗﹗﹗
select empkey, empno, t01 as t, r01 as r
from yourtable
union
select empkey, empno, t02 as t, r02 as r
from yourtable