if object_id('[a]') is not null drop table [a] go create table [a](NUM varchar(5),Value decimal(10,4)) insert [a] select 'A', 10.0000 union all select 'B' , 20.0000 select *,id=identity(int,1,1) into #t from aselect a.num,a.value,b.num ,b.value from #t a,#t b where a.id=b.id-1num value num value ----- --------------------------------------- ----- --------------------------------------- A 10.0000 B 20.0000(1 行受影响)
/*--------------------------------- -- Author : htl258(Tony) -- Date : 2009-09-07 11:24:13 -- 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 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/ --> 生成测试数据表:tbIf not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([NUM] nvarchar(1),[Value] decimal(18,4)) Insert [tb] Select N'A',10.0000 union all Select N'B',20.0000 Go --Select * from [tb]-->SQL查询如下: DECLARE @s VARCHAR(8000) SELECT @s=ISNULL(@s+'/','')+LTRIM(num)+' '+LTRIM(VALUE) FROM dbo.tb SELECT @s /* -------------------------- A 10.0000/B 20.0000(1 行受影响) */
SELECT @STR=ISNULL(@STR+' /','')+CONVERT(VARCHAR(50),NUM)+' '+CONVERT(VARCHAR(50),[VALUE]) FROM TB
SELECT @STR
if object_id('[a]') is not null drop table [a]
go
create table [a](NUM varchar(5),Value decimal(10,4))
insert [a] select
'A', 10.0000 union all select
'B' , 20.0000
select *,id=identity(int,1,1) into #t from aselect a.num,a.value,b.num ,b.value from #t a,#t b where a.id=b.id-1num value num value
----- --------------------------------------- ----- ---------------------------------------
A 10.0000 B 20.0000(1 行受影响)
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-07 11:24:13
-- 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 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([NUM] nvarchar(1),[Value] decimal(18,4))
Insert [tb]
Select N'A',10.0000 union all
Select N'B',20.0000
Go
--Select * from [tb]-->SQL查询如下:
DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s+'/','')+LTRIM(num)+' '+LTRIM(VALUE) FROM dbo.tb
SELECT @s
/*
--------------------------
A 10.0000/B 20.0000(1 行受影响)
*/