--test data
create table #test
(
seq int,
brh varchar(20),
[order] varchar(20)
)
insert into #test values (1 , '3001', '001')
insert into #test values (2 , '3001', '102')
insert into #test values (3 , '3001', '003')
insert into #test values (1 , '3002', '304')
insert into #test values (2 , '3002', '955')
insert into #test values (3 , '3002', '206')
insert into #test values (4 , '3002', '607')
insert into #test values (5 , '3002', '101')
insert into #test values (6 , '3002', '301')
insert into #test values (7 , '3002', '221')
insert into #test values (1 , '3003', '331')
insert into #test values (2 , '3003', '551') 要將數據整合成如下格式:
brh = #test.brh ;
new_column = #test.seq + ')' + #test.[order] + char(9) or char(10); brh new_column
3001 1)001 CHAR(9) 2)102 CHAR(9) 3)003 CHAR(10)
3002 1)304 CHAR(9) 2)955 CHAR(9) 3)206 CHAR(10)
3002 4)607 CHAR(9) 5)101 CHAR(9) 6)301 CHAR(10)
3002 7)221 CHAR(9)
3001 1)331 CHAR(9) 2)551 CHAR(9)
(
brh varchar(8),
[order_no] varchar(20),
seq int
)
set nocount on
insert into #temp values('3101' , '001',1)
insert into #temp values('3101' , '002',2)
insert into #temp values('3101' , '003',3)
insert into #temp values('3101' , '004',4)
insert into #temp values('3101' , '005',5)
insert into #temp values('3101' , '006',6)
insert into #temp values('3102' , '123',1)
insert into #temp values('3102' , '008',2)
insert into #temp values('3102' , '009',3)
insert into #temp values('3102' , '101',4)
insert into #temp values('3103' , '102',1)
insert into #temp values('3103' , '113',2)
insert into #temp values('3104' , '145',1)
go
select brh,
[order_no]=(select rtrim(seq)+')'+[order_no]+'char(9)'+' ' from #temp
where brh=a.brh and (seq-1)/3=(a.seq-1)/3 for xml path(''))
from #temp a group by brh,(seq-1)/3/*
brh order_no
-------- -----------------------------------------
3101 1)001char(9) 2)002char(9) 3)003char(9)
3101 4)004char(9) 5)005char(9) 6)006char(9)
3102 1)123char(9) 2)008char(9) 3)009char(9)
3102 4)101char(9)
3103 1)102char(9) 2)113char(9)
3104 1)145char(9)
Line 3: Incorrect syntax near 'xml'.
ssp2009 你好 ,請問爲什麽我運行你的語句會報錯?我的是sqlserver 2000
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
USE master;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOcreate table A
(
seq int,
brh varchar(20),
[order] varchar(20)
)
insert into A values (1 , '3001', '001')
insert into A values (2 , '3001', '102')
insert into A values (3 , '3001', '003')
insert into A values (1 , '3002', '304')
insert into A values (2 , '3002', '955')
insert into A values (3 , '3002', '206')
insert into A values (4 , '3002', '607')
insert into A values (5 , '3002', '101')
insert into A values (6 , '3002', '301')
insert into A values (7 , '3002', '221')
insert into A values (1 , '3003', '331')
insert into A values (2 , '3003', '551')
go
SELECT MAX(brh) AS brh
,MAX(CASE WHEN CharCount=1 THEN ss ELSE NULL END)+' '
+ISNULL(MAX(CASE WHEN CharCount=2 THEN ss ELSE NULL END),'')+' '
+ISNULL(MAX(CASE WHEN CharCount=3 THEN ss ELSE NULL END),'') AS aa
FROM
(
SELECT brh,cast(seq AS nvarchar(10))+')'+[order]+' '+CharNumber AS ss,CharCount,seq,CharCountss
FROM
(
SELECT *,
CASE when seq%3=0 THEN 'char(10)' ELSE 'char(9)' END AS CharNumber,
CASE when seq%3=0 THEN 3 ELSE seq%3 END AS CharCount,
CASE when seq/3=0 THEN LTRIM(brh)+'0' ELSE LTRIM(brh)+LTRIM((seq-1)/3) END AS CharCountss FROM A
) AS B
)AS BB GROUP BY CharCountssgo
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO
/*
brh aa
-------------------- --------------------------------------------------------------------------------------------------------------------------
3001 1)001 char(9) 2)102 char(9) 3)003 char(10)
3002 1)304 char(9) 2)955 char(9) 3)206 char(10)
3002 4)607 char(9) 5)101 char(9) 6)301 char(10)
3002 7)221 char(9)
3003 1)331 char(9) 2)551 char(9)
*/
use tempdb
Go
if object_id('test') Is Not Null
Drop Table testcreate table test
(
seq int,
brh varchar(20),
[order] varchar(20)
)
insert into test values (1 , '3001', '001')
insert into test values (2 , '3001', '102')
insert into test values (3 , '3001', '003')
insert into test values (1 , '3002', '304')
insert into test values (2 , '3002', '955')
insert into test values (3 , '3002', '206')
insert into test values (4 , '3002', '607')
insert into test values (5 , '3002', '101')
insert into test values (6 , '3002', '301')
insert into test values (7 , '3002', '221')
insert into test values (1 , '3003', '331')
insert into test values (2 , '3003', '551')/*
brh new_column
3001 1)001 CHAR(9) 2)102 CHAR(9) 3)003 CHAR(10)
3002 1)304 CHAR(9) 2)955 CHAR(9) 3)206 CHAR(10)
3002 4)607 CHAR(9) 5)101 CHAR(9) 6)301 CHAR(10)
3002 7)221 CHAR(9)
3001 1)331 CHAR(9) 2)551 CHAR(9)
*/
Go
If object_id('fn_StrMerge') is not null
Drop function fn_StrMerge
Go
Create function fn_StrMerge
(
@brh varchar(20),
@seq int
)
returns varchar(2000)
As
Begin
Declare @Str varchar(2000)
Select @Str =isnull(@Str+' ','')+Rtrim(seq)+')'+[order]+' CHAR('+Case seq%3 When 0 Then '10' Else '9' End +')' From test Where brh=@brh And seq >(@seq/3-sign((@seq%3)^1))*3 And seq<=(@seq/3-sign((@seq%3)^1))*3+3
Return @Str
EndGo
Select Distinct brh,dbo.fn_StrMerge(brh,seq)
From Test
Where dbo.fn_StrMerge(brh,seq) Is Not null