如题,比如有一个表UserInfo的数据如下:
name age
jake 23
peter 24
如何返回结果如下:
name:jake age:23
name:peter age:24
注:返回只有一个字段
name age
jake 23
peter 24
如何返回结果如下:
name:jake age:23
name:peter age:24
注:返回只有一个字段
解决方案 »
- 小的分组问题,路过的进来帮下。谢谢
- 高手帮个忙!!很急!!sql安装时出现的错误!!
- SET IDENTITY_INSERT 表 ON
- (高手请进)sql2000 整型类型与日期类型比较的问题
- 关于MSDE2000的问题
- 用查询分析器拷贝一个表,对你来说太简单了,帮帮忙!
- 请教:int类型的字段如何实现A是B的子串?(在SQL语句中)
- 一个百思不得其解的问题。。关于SQL Server2000的触发器。。
- access一个日期时间字段是短格式的,用什么SQL语句改成长格式的??
- 小菜鸟求助大佬们,如何才能使SQL查询中结果为空的显示为0呢?具体情况如下:
- 求一SQL语句,见内页。这边请。。
- sql如何使标识列按某一列递增
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[age] [int])
INSERT INTO [tb]
SELECT 'jake','23' UNION ALL
SELECT 'peter','24'--SELECT * FROM [tb]-->SQL查询如下:--SELECT * FROM [tb]
SELECT col='name:'+name+' age:'+LTRIM(age) FROM TB
/*
col
--------------------------------
name:jake age:23
name:peter age:24(2 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[age] [int])
INSERT INTO [tb]
SELECT 'jake','23' UNION ALL
SELECT 'peter','24'--SELECT * FROM [tb]-->SQL查询如下:
IF OBJECT_ID('P_TEST') IS NOT NULL
DROP PROC P_TEST
GO
CREATE PROC P_TEST
@tbname sysname,
@c1 nvarchar(100)=NULL,
@c2 int=NULL
AS
DECLARE @S NVARCHAR(4000)
SELECT @S='
SELECT col=''name:''+name+'' age:''+LTRIM(age)
FROM ['+@tbname+']
WHERE name = ISNULL(@c1,name)
AND @c2=ISNULL(@c2,age)'
EXEC SP_EXECUTESQL @S,N'@c1 nvarchar(100),@c2 int',@c1,@c2
GOEXEC P_TEST 'tb','jake',23
/*
col
--------------------------------
name:jake age:23(1 行受影响)
*/
那么就返回B表的所有数据,格式就是我上面说的那种格式
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[age] [int])
INSERT INTO [tb]
SELECT 'jake','23' UNION ALL
SELECT 'peter','24'--SELECT * FROM [tb]-->SQL查询如下:
IF OBJECT_ID('P_TEST') IS NOT NULL
DROP PROC P_TEST
GO
CREATE PROC P_TEST
@dbname sysname,
@tbname sysname
AS
exec('
SELECT *
FROM ['+@dbname+']..['+@tbname+']'
)
GOEXEC P_TEST mydb,tb
/*
name age
---------- -----------
jake 23
peter 24(2 行受影响)
*/
--------------------------------
name:jake age:23
name:peter age:24
create table a
(tname char(10),
tage samllint
)insert
into a(tname,tage)
select 'jake','23' union all
select 'peter','24'create proc p_pro
as
@dname sysname
@tname sysname
exec('
SELECT *
FROM ['+@dbname+']..['+@tbname+']'
)
GO
-- Author :feixianxxx(poofly)
-- Version:
-- Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
----------------------------------------------------------------*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[age] [int])
INSERT INTO [tb]
SELECT 'jake','23' UNION ALL
SELECT 'peter','24'
goIF OBJECT_ID('P_TEST') IS NOT NULL
DROP PROC P_TEST
GO
CREATE PROC P_TEST
@dbname varchar(10),
@tbname varchar(10)
AS
declare @s varchar(8000),@s2 varchar(8000)
select @s2=isnull(@s2,'')+''''+name+':''+rtrim('+name+')+'' ''+'
from sys.columns
where OBJECT_ID=OBJECT_ID(''+@dbname+'..'+@tbname+'')
select @s2=STUFF(@s2,len(@s2),1,'') exec('select '+@s2 +' as col from tb')
GO
--执行
EXEC P_TEST 'test','tb'
/*
col
---------------------------------
name:jake age:23
name:peter age:24 */
create proc p_1(@db varchar(100),@tb varchar(100))
as
begin
declare @sql varchar(8000)
set @sql = 'select '
select @sql=@sql+''''+name+':''+rtrim(' +name+
') +'' ''+' from syscolumns where id=object_id(@db+'..'+@tb)
set @sql = left(@sql,len(@sql)-1)+'D from '+@db+'..'+@tb
exec(@sql)
end