Consider the following account and customer tables:cust_tbl
cust_id title e_first_name e_last_name address1 .
0 MR Martin Ma .
1 MR Kirs Cheung .
2 MR Ricky Chan .
3 MR Tom Kwan .
4 MR CDefault CDefault .
5 MRS Mary Mok .
. . . . . acc_grp_cust_tbl
acc_group Cust_id1 Cust_id2 Cust_id3 Cust_id4
1400 0 1 2
1500 3 4
1600 5
. . . . .
. . . . .The acc_grp_cust_tbl table is responsible to store the customer ID, and the
cust_tbl table is responsible to store customer personal information such as name,
address, etc… Please write a SQL query in order to provide following result.ACC_GROUP PAYEENAMES
1400 Ma Martin/Cheung Kris/Chan Ricky
1500 Kwan Tom/Corporate Default Corporate Default
1600 Mok Mary
. .
. .
Hint: it's a bad design all the way. We can use many-to-many or one-to-many to avoid
the SQL puzzle, but we must deal with that, please think about how to translate
the columns to rows, and the character conjunction using SQL Functions.
Answer:
谢谢您的关注!
cust_id title e_first_name e_last_name address1 .
0 MR Martin Ma .
1 MR Kirs Cheung .
2 MR Ricky Chan .
3 MR Tom Kwan .
4 MR CDefault CDefault .
5 MRS Mary Mok .
. . . . . acc_grp_cust_tbl
acc_group Cust_id1 Cust_id2 Cust_id3 Cust_id4
1400 0 1 2
1500 3 4
1600 5
. . . . .
. . . . .The acc_grp_cust_tbl table is responsible to store the customer ID, and the
cust_tbl table is responsible to store customer personal information such as name,
address, etc… Please write a SQL query in order to provide following result.ACC_GROUP PAYEENAMES
1400 Ma Martin/Cheung Kris/Chan Ricky
1500 Kwan Tom/Corporate Default Corporate Default
1600 Mok Mary
. .
. .
Hint: it's a bad design all the way. We can use many-to-many or one-to-many to avoid
the SQL puzzle, but we must deal with that, please think about how to translate
the columns to rows, and the character conjunction using SQL Functions.
Answer:
谢谢您的关注!
http://blog.airnews.cn/blog.asp?name=xgwang
请说中文:)
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+(e_last_name+' '+e_first_name) from cust_tbl where cust_id=@Col1
return stuff(@sql,1,1,'')
end
go
Create table cust_tbl(cust_id int, title nvarchar(10), e_first_name nvarchar(20), e_last_name nvarchar(20))
insert into cust_tbl select 0,'MR','Martin','Ma.'
union all select 1,'MR','Kirs','Cheung.'
union all select 2,'MR','Ricky','Cheung.'
union all select 3,'MR','Tom','Cheung.'
union all select 4,'MRS','Cdefault','CDefault.'
Create table acc_grp_cust_tbl(acc_group int, Cust_id1 int, Cust_id2 int, Cust_id3 int, Cust_id4 int)
insert into acc_grp_cust_tbl select 1400,0,1,2,3
union all select 1500,3,4,null,null
union all select 1600,4,null,null,null--select * from cust_tbl
--select * from acc_grp_cust_tblselect acc_group,isnull(dbo.fc_str(cust_id1),'')+'/'
+isnull(dbo.fc_str(cust_id2),'')+'/'
+isnull(dbo.fc_str(cust_id3),'')+'/'
+isnull(dbo.fc_str(cust_id4),'')
from acc_grp_cust_tbl
drop table cust_tbl
drop table acc_grp_cust_tbl
drop function dbo.fc_str-------------------
The reault just as below:
--------------------------------
1400 Ma. Martin/Cheung. Kirs/Cheung. Ricky/Cheung. Tom
1500 Cheung. Tom/CDefault. Cdefault//
1600 CDefault. Cdefault///
Actually ,This result is not your format,But maybe you can do it by your self,id it?
参见我的blog
http://blog.airnews.cn/more.asp?name=xgwang&id=167