SQL Server 2000行列问题

[lsj_zrp]

表1
sid col1 col2 col3 col4 col5........
s01 0 1 0 0 0 ........


如何得到
s01 col1 0
s01 col2 1
s01 col3 0
s01 col4 0
s01 col5 0
...

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-15 21:44:54
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([sid] [nvarchar](10),[col1] [int],[col2] [int],[col3] [int],[col4] [int],[col5] [int])
INSERT INTO [tb]
SELECT 's01','0','1','0','0','0'

--SELECT * FROM [tb]

-->SQL查询如下:
declare @s varchar(8000)
select @s=isnull(@s+' union all select sid,['+name+'] from tb','select sid,col=['+name+'] from tb') 
from syscolumns
where id=object_id('tb') and name <>'sid'
exec(@s)
/*
sid        col
---------- -----------
s01        0
s01        1
s01        0
s01        0
s01        0

(5 行受影响)
*/

[SQL77]

SELECT SID,'COL1',COL1 FROM TB
UNION ALL
SELECT SID,'COL2',COL2 FROM TB
.......

[永生天地]

第一种

select sid , 'col1' as col,col1 as value from tb union all
select sid , 'col2' as col,col2 as value from tb union all
select sid , 'col3' as col,col3 as value from tb union all
select sid , 'col4' as col,col4 as value from tb union all
select sid , 'col5' as col,col5 as value from tb
.....

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-15 21:44:54
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([sid] [nvarchar](10),[col1] [int],[col2] [int],[col3] [int],[col4] [int],[col5] [int])
INSERT INTO [tb]
SELECT 's01','0','1','0','0','0'

--SELECT * FROM [tb]

-->SQL查询如下:
declare @s varchar(8000)
select @s=isnull(@s+' union all select sid,'''+name+''',['+name+'] from tb','select sid,colname='''+name+''',col=['+name+'] from tb') 
from syscolumns
where id=object_id('tb') and name <>'sid'
exec(@s)
/*
sid        colname col
---------- ------- -----------
s01        col1    0
s01        col2    1
s01        col3    0
s01        col4    0
s01        col5    0

(5 行受影响)
*/

modify

[dawugui]

CREATE TABLE tb(sid varchar(10),col1 int,col2 int,col3 int,col4 int,col5 int)
INSERT INTO tb values('s01',0,1,0,0,0)
go

--如果你的数据固定只有这五列，用静态SQL。
select sid , 'col1' col , col1 val from tb
union all
select sid , 'col2' col , col2 val from tb
union all
select sid , 'col3' col , col3 val from tb
union all
select sid , 'col4' col , col4 val from tb
union all
select sid , 'col5' col , col5 val from tb
/*
sid        col  val         
---------- ---- ----------- 
s01        col1 0
s01        col2 1
s01        col3 0
s01        col4 0
s01        col5 0

（所影响的行数为 5 行）
*/

--如果你的数据不固定，用动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select sid , [col] = ' + quotename(Name , '''') + ' , [val] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'sid' and ID = object_id('tb') --表名tb，不包含列名为id的其它列
order by colid asc
exec(@sql + ' order by sid ')
/*
sid        col  val         
---------- ---- ----------- 
s01        col1 0
s01        col2 1
s01        col3 0
s01        col4 0
s01        col5 0
*/

drop table tb