问一个数据查询的问题

[jzinfo]

产品表：
productID,productName,producSpec

P001,产品1，23x23x43


-----------------------


产品供应商关系表
productid,vendorID,vendorname,price

P001,C001,供应商1，20.00
P001,C002,供应商2，19.50
P001,C003,供应商3，20.50

我现在想查询产品后在一行中显示这些记录

P001,产品1，23x23x43,C001,供应商1，20.00,C002,供应商2，19.50,C003,供应商3，20.50

如何做呢？

[pengxuan]

if object_id('tb1') is not null
   drop table tb1
go
create table tb1(productID varchar(10),productName varchar(10),producSpec varchar(20))
go
insert into tb1
select 'P001','产品1','23x23x43'
if object_id('tb2') is not null
   drop table tb2
go
create table tb2(productid varchar(10),vendorID varchar(10),vendorname varchar(10),price numeric(9,2))
go
insert into tb2
select 'P001','C001','供应商1',20.00 union all
select 'P001','C002','供应商2',19.50 union all
select 'P001','C003','供应商3',20.50
go
if object_id('uf_linkstr') is not null
   drop function uf_linkstr
go
create function uf_linkstr (@productid varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=productid+','+productName+','+producSpec from tb1 where productid=@productid
select @str=isnull(@str+',','')+vendorID+','+vendorname+','+cast(price as varchar(10)) from tb2 inner join tb1 on tb2.productid=tb1.productID
return @str
end
go
select dbo.uf_linkstr(productid) from tb2 group by productid

[--小F--]

--合并表
/****************************************************************************************************************************************************** 
合并分拆表数据 

整理人：中国风(Roy) 

日期:2008.06.06 
******************************************************************************************************************************************************/ 

--> --> (Roy)生成測試數據 

if not object_id('Tab') is null 
    drop table Tab 
Go 
Create table Tab([Col1] int,[Col2] nvarchar(1)) 
Insert Tab 
select 1,N'a' union all 
select 1,N'b' union all 
select 1,N'c' union all 
select 2,N'd' union all 
select 2,N'e' union all 
select 3,N'f' 
Go 

合并表: 

SQL2000用函数: 

go 
if object_id('F_Str') is not null 
    drop function F_Str 
go 
create function F_Str(@Col1 int) 
returns nvarchar(100) 
as 
begin 
    declare @S nvarchar(100) 
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 
    return @S 
end 
go 
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab 

go 

SQL2005用XML: 

方法1: 

select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') 
from 
    (select distinct COl1 from Tab) a 
Cross apply 
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 

方法2: 

select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) 
from 
    (select distinct COl1 from Tab) a 
cross apply 
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE) 
                .query(' <Tab> 
                {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} 
                {concat("",string(/Tab[last()]/@COl2))} 
                </Tab>') 
                )b 

SQL05用CTE: 

;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) 
,Roy2 as 
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) 
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 


生成结果: 
/* 
Col1        COl2 
----------- ------------ 
1          a,b,c 
2          d,e 
3          f 

(3 行受影响) 
*/ 

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('产品表') IS NOT NULL
  DROP TABLE 产品表
GO
CREATE TABLE 产品表(productID varchar(10),productName varchar(10),producSpec varchar(40))
go
insert into 产品表
select  'P001','产品1','23x23x43' 
go
IF OBJECT_ID('产品供应商关系表 
') IS NOT NULL
  DROP TABLE 产品供应商关系表 

GO
CREATE TABLE 产品供应商关系表 
(productID varchar(10),vendorID varchar(10),vendorname varchar(40),price int )
go
insert into 产品供应商关系表 select 
'P001','C001','供应商1',20.00 union all select 
'P001','C002','供应商2',19.50 union all select 
'P001','C003','供应商3',20.50 
go
CREATE FUNCTION dbo.f_tb(@id varchar(10)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + vendorID +vendorname +cast(price as varchar(10))FROM 产品供应商关系表 WHERE productID=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
select a.productID,productName,producSpec,
信息=dbo.f_tb(a.productID)
from 产品表 a,产品供应商关系表 b
where a.productID=b.productID
group by a.productID,productName,producSpec 
/*------------
3 行受影响)
productID  productName producSpec                               信息
---------- ----------- ---------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
P001       产品1         23x23x43                                 C001供应商120,C002供应商219,C003供应商320,C001供应商120,C002供应商219,C003供应商320
-------*/

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('产品表') IS NOT NULL
  DROP TABLE 产品表
GO
CREATE TABLE 产品表(productID varchar(10),productName varchar(10),producSpec varchar(40))
go
insert into 产品表
select  'P001','产品1','23x23x43' 
go
IF OBJECT_ID('产品供应商关系表 
') IS NOT NULL
  DROP TABLE 产品供应商关系表 

GO
CREATE TABLE 产品供应商关系表 
(productID varchar(10),vendorID varchar(10),vendorname varchar(40),price int )
go
insert into 产品供应商关系表 select 
'P001','C001','供应商1',20.00 union all select 
'P001','C002','供应商2',19.50 union all select 
'P001','C003','供应商3',20.50 
go
alter FUNCTION dbo.f_tb(@id varchar(10),@productName varchar(40),@producSpec varchar(40))  
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = @id +','+@productName +','+@producSpec 
    SELECT @str = @str + ',' + vendorID +','+vendorname+',' +cast(price as varchar(10))FROM 产品供应商关系表 WHERE productID=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
select 
信息=dbo.f_tb(a.productID,productName,producSpec)
from 产品表 a,产品供应商关系表 b
where a.productID=b.productID
group by a.productID,productName,producSpec 
/*------------
(3 行受影响)
信息
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
001,产品1,23x23x43,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20

(1 行受影响)

-------*/

[feixianxxx]

--这是2种输出格式 LZ自己挑吧

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('产品表') IS NOT NULL
  DROP TABLE 产品表
GO
CREATE TABLE 产品表(productID varchar(10),productName varchar(10),producSpec varchar(40))
go
insert into 产品表
select  'P001','产品1','23x23x43' 
go
IF OBJECT_ID('产品供应商关系表 
') IS NOT NULL
  DROP TABLE 产品供应商关系表 

GO
CREATE TABLE 产品供应商关系表 
(productID varchar(10),vendorID varchar(10),vendorname varchar(40),price int )
go
insert into 产品供应商关系表 select 
'P001','C001','供应商1',20.00 union all select 
'P001','C002','供应商2',19.50 union all select 
'P001','C003','供应商3',20.50 
go
create  FUNCTION dbo.f_tb(@id varchar(10)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + vendorID +','+vendorname +','+cast(price as varchar(10))FROM 产品供应商关系表 WHERE productID=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
select a.productID,productName,producSpec,
信息=dbo.f_tb(a.productID)
from 产品表 a,产品供应商关系表 b
where a.productID=b.productID
group by a.productID,productName,producSpec 
/*
3 行受影响)
productID  productName producSpec                               信息
---------- ----------- ---------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
P001       产品1         23x23x43                                 C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2
*/


IF OBJECT_ID('产品表') IS NOT NULL
  DROP TABLE 产品表
GO
CREATE TABLE 产品表(productID varchar(10),productName varchar(10),producSpec varchar(40))
go
insert into 产品表
select  'P001','产品1','23x23x43' 
go
IF OBJECT_ID('产品供应商关系表 
') IS NOT NULL
  DROP TABLE 产品供应商关系表 

GO
CREATE TABLE 产品供应商关系表 
(productID varchar(10),vendorID varchar(10),vendorname varchar(40),price int )
go
insert into 产品供应商关系表 select 
'P001','C001','供应商1',20.00 union all select 
'P001','C002','供应商2',19.50 union all select 
'P001','C003','供应商3',20.50 
go
create FUNCTION dbo.f_tb(@id varchar(10),@productName varchar(40),@producSpec varchar(40))  
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = @id +','+@productName +','+@producSpec 
    SELECT @str = @str + ',' + vendorID +','+vendorname+',' +cast(price as varchar(10))FROM 产品供应商关系表 WHERE productID=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
select 
信息=dbo.f_tb(a.productID,productName,producSpec)
from 产品表 a,产品供应商关系表 b
where a.productID=b.productID
group by a.productID,productName,producSpec 
/*------------
(3 行受影响)
信息
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
001,产品1,23x23x43,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20,C001,供应商1,20,C002,供应商2,19,C003,供应商3,20

(1 行受影响)

-------*/

[claro]

。。。

[spsx99vj]

declare @str varchar(200)
create table #temp
(
productID varchar(10),
productName varchar(10),
producSpec varchar(10)
)
insert into #temp(productID,productName,producSpec)
values('P001','产品1','23x23x43')
select @str=productID+','+productName+','+producSpec+',' from #temp
create table #t
(
productid varchar (10),
vendorID varchar(10),
vendorname varchar(10),
price decimal(13,2)
)
insert into #t(productid,vendorID,vendorname,price)
select 'P001','C001','供应商1',20.00
insert into #t(productid,vendorID,vendorname,price)
select 'P001','C002','供应商2',19.50
insert into #t(productid,vendorID,vendorname,price)
select 'P001','C003','供应商3',20.50

select @str=@str+a.vendorID+','+a.vendorname+','+convert(varchar,a.price)+',' from (select b.vendorID,b.vendorname,b.price from
#temp a,#t b where a.productID=b.productid) a
select substring(@str,1,len(@str)-1)
drop table #temp
drop table #t