怎样将表中的字段作为每一条记录输出

[coffeewar]

如有下表:

productID productName categoryID departmentID

1000 TCL/3000彩电 4 1
1001 海尔彩电 5 1
1002 LG彩电 6 1
1003 格力空调 10 3
1004 奥克斯空调 11 3

现想得到 productID=1000这条记录,想显示出如下效果.或在存储过程中返回dataReader

字段名 字段值
productID 10000
productName TCL/3000彩电
categoryID 4
departmentID 1

[leeboyan]

交叉表，参见：
http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.3122522

[lillyguo1119]

可以重新建一个数据结构表，然后动态拼装。

[welyngj]

select 'productID', productID from tbl where productID=1000
union all
select 'productname ', productname from tbl where productID=1000
union all
select 'categoryID', categoryID from tbl where productID=1000
union all
select 'departmentID', departmentID from tbl where productID=1000

[progress99]

参见

1.

Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)

declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)

drop table test


2.

if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P')
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
end

go

　


----------------分析
declare @tablename varchar(200)
set @tablename='table1'
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
select @insertsql
--exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
drop table #tmp
end
end

[pbsql]

select 'productID' 字段名,cast(productID as varchar) 字段值 from t where productID=1000
union all
select 'productName' 字段名,cast(productName as varchar) 字段值 from t where productID=1000
union all
select 'categoryID' 字段名,cast(categoryID as varchar) 字段值 from t where productID=1000
union all
select 'departmentID' 字段名,cast(departmentID as varchar) 字段值 from t where productID=1000

[welyngj]

create procedure pro_tbl( @productID int)
as
select 'productID', productID from tbl where productID=@productID
union all
select 'productname ', productname from tbl where productID=@productID
union all
select 'categoryID', categoryID from tbl where productID=@productID
union all
select 'departmentID', departmentID from tbl where productID=@productID

go

exec pro_tbl 1000

[zjcxc]

select 字段名='productID',字段值=cast(productID as varchar)
from 表 where productID='10000'
union all
select 字段名='productName',字段值=productName
from 表 where productID='10000'
union all
select 字段名='categoryID',字段值=cast(categoryID as varchar)
from 表 where productID='10000'
union all
select 字段名='departmentID',字段值=cast(departmentID as varchar)
from 表 where productID='10000'

[zjcxc]

--查询的存储过程
create proc p_qry
@productID char(4)
as
declare @s varchar(8000),@tj varchar(100)
select @s='',@tj=' from 表 where productID='''+@productID+''''
select @s=@s+' union all select 字段名='''+name
+''',字段值=cast(['+name+'] as varchar)'+@tj
from syscolumns where id=object_id('表')
set @s=substring(@s,12,8000)
exec(@s)
go

--调用测试
exec p_qry '1004'

[zjcxc]

--改一下顺序
--查询的存储过程
create proc p_qry
@productID char(4)
as
declare @s varchar(8000),@tj varchar(100)
select @s='',@tj=' from 表 where productID='''+@productID+''''
select @s=@s+' union all select 字段名='''+name
+''',字段值=cast(['+name+'] as varchar)'+@tj
from syscolumns where id=object_id('表')
order by colid
set @s=substring(@s,12,8000)
exec(@s)
go

[zjcxc]

--测试

--测试数据
create table 表(productID char(4),productName varchar(20),categoryID int,departmentID int)
insert 表 select '1000','TCL/3000彩电',4,1
union all select '1001','海尔彩电',5,1
union all select '1002','LG彩电',6,1
union all select '1003','格力空调',10,3
union all select '1004','奥克斯空调',11,3
go

--查询的存储过程
create proc p_qry
@productID char(4)
as
declare @s varchar(8000),@tj varchar(100)
select @s='',@tj=' from 表 where productID='''+@productID+''''
select @s=@s+' union all select 字段名='''+name
+''',字段值=cast(['+name+'] as varchar)'+@tj
from syscolumns where id=object_id('表')
order by colid
set @s=substring(@s,12,8000)
exec(@s)
go

--调用测试
exec p_qry '1004'
go

--删除测试
drop table 表
drop proc p_qry

/*--测试结果
字段名 字段值
------------ ------------------------------
productID 1004
productName 奥克斯空调
categoryID 11
departmentID 3

（所影响的行数为 4 行）
--*/

[zjcxc]

我的存储过程的好处是,表结构发生变化时,不用修改存储过程就可以适应变化

[coffeewar]

谢谢各位,让我仔细看看。

[coffeewar]

邹建老大，这句语句 select @s='',@tj=' from 表 where productID='''+@productID+''''
为什么要用三个单引号还有四个单引号的是怎么回事啊，看不懂，郁闷

[zjcxc]

凡是在字符串中出现的',都要用两个. 所以就变成这样

[coffeewar]

如果 productID 为int 型时,语句该怎么写

[coffeewar]

我把这句select @s='',@tj=' from 表 where productID='''+@productID+''''改成
select @s='',@tj=' from 表 where productID='+'cast('+@productID+' as int)'
好像可以查询出来,但又有错误发生.

[coffeewar]

不好意思，没错误的。可以了。

[zjcxc]

--自己改好了?

select @s='',@tj=' from 表 where productID='+cast(@productID as int)

[coffeewar]

8000个字符不够用怎么办啊

[coffeewar]

谢谢各位,问题解决,结分。