大力﹐快幫幫我﹐交叉表的問題﹐請問如何將橫行轉換成豎行排列﹖散分﹐急﹗﹗﹗﹗﹗﹗﹗﹗

[dahroswell]

如表A
NO T01 T02 T03 T04 T05 T06 T07 T08
1 10 12 15 18 17 20 21 23
2, 8 8 10 5 4 7 9 12
請問如何把它轉換成
NO T
1 10
1 12
1 15
1 18
1 17
1 20
1 21
1 23
2 8
2 8
2 10
2 5
2 4
2 7
2 9
2 12
求各路高手幫忙﹐多謝各位了﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗
一定要給我實例喲!!

[saucer]

see
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955

also 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

[flew]

sql语句就能解决。以前看到过类似状况

gz

[webdiyer]

晕，刚发了个问题，一看这个帖子还以为是发到SQL Server版呢？大力不会来这吧，他已经解决过这样的问题了，我给你帖到地址，在这：http://expert.csdn.net/Expert/topicview.asp?id=1964358

[dahroswell]

各位大哥﹐你們給我的實例跟我要的剛好相反﹐你們的我以前都見過的﹐
我的是要將"橫"轉換成"列"的

[jianglinchun]

哦，好东西，先收藏——不过目前用不到。

[saucer]

the example in
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)

[saucer]

sorry, try

create 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 yourtable

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<>'NO'
SELECT @sql = Left(@sql,Len(@sql)-5)
EXEC (@sql)

[dahroswell]

請問思歸大俠﹐information_schema.columns 是什么線程信息﹐用什么替代﹐謝謝﹗﹗﹗

[saucer]

it is a system view, supported by SQL Server 2000, or try


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)

[dahroswell]

不行呀﹐可還是不行﹐我太笨了﹐麻煩你﹗﹗

[saucer]

what database are you using? if you are using SQL Server, open Query Analyzer and run the following

use tempdb
go

create 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 yourtable

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)

[gong1]

select sum('字段名') case '1' as,
.....
sql2000帮助有例子，哈哈

[dahroswell]

我把我整個表copy給您﹐您 幫我看看好嗎﹖
我用的是SQL SERVER 2000 呀﹐行嗎﹖

[dahroswell]

Empkey empno t01 t02 r01 r02
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了﹐最大了﹐可還是不行
您看看有什么好的辦法嗎﹖真的很麻煩您了﹗不甚感激﹗﹗﹗﹗﹗﹗﹗﹗

[dahroswell]

它返回了這樣一個錯誤Invalid length parameter passed to the substring function.

[saucer]

if that is all you have, you can simply do


select empkey, empno, t01 as t, r01 as r
from yourtable
union
select empkey, empno, t02 as t, r02 as r
from yourtable

[dahroswell]

哇﹐您太聰明﹐歷害了﹐佩服佩服﹗﹗﹗