五一快乐，高手请进，求一ＳＱＬ语句

[marcus008]

有四张表:(以下的关系可体现为:硬件＞＞硬盘＞＞金钻１００Ｇ（里面有属性转速／重量))
A类别表(ptreeid为０是为父类)
id ptreeid name
1 0 硬件
2 1 硬盘
3 1 内存

B产品表(ptreeid为类别表中的ＩＤ)
id ptreeid name price
10 2 金钻100G 500
11 2 金钻200G 600

C产品属性表（产品属性表,ptreeid为类别表中的ＩＤ，当为２时，则代表硬盘中的属性）
id ptreeid attribute_id attribute_name
100 2 100 转速
101 2 101 重量

D产品属性内容表（产品属性具体内容表,ptreeid为类别表中的ＩＤ，productid为Ｂ产品表的ＩＤ，attribute_id为属性表中ID，attribute_content则代表内容）
id ptreeid productid attribute_id attribute_content
1 2 10 100 7200
2 2 10 101 500
3 2 11 100 1400
4 2 11 101 600



现在想用一条ＳＱＬ语句，生成一个新表，格式如下：前面的内容都是跟产品表(B)中的一样的，只是要加多一个details里面包含出该产品所包含的全部属性及内容）

id ptreeid name price details
10 2 金钻100G 500 转速 7200 重量 500
11 2 金钻200G 600 转速 1400 重量 600

[liangCK]

写个函数.

[liangCK]

create table 类别表(id int,ptreeid int,name varchar(10))
insert into 类别表 select 1 ,   0    ,  '硬件' 
insert into 类别表 select 2 ,   1    ,  '硬盘' 
insert into 类别表 select 3 ,   1    ,  '内存' 

create table 产品表(id int,ptreeid int,name varchar(10),price int)
insert into 产品表 select 10 ,  2     ,  '金钻100G' ,   500 
insert into 产品表 select 11 ,  2     ,  '金钻200G' ,   600 

create table 产品属性表(id int,ptreeid int,attribute_id int,attribute_name varchar(10))
insert into 产品属性表 select 100  ,  2     ,   100       ,     '转速' 
insert into 产品属性表 select 101  ,  2     ,   101       ,     '重量'     

create table 产品属性内容表(id int,ptreeid int,productid int,attribute_id int,attribute_content varchar(20))
insert into 产品属性内容表 select 1 ,  2     ,   10        ,     100       ,     '7200' 
insert into 产品属性内容表 select 2 ,  2     ,   10        ,     101       ,     '500' 
insert into 产品属性内容表 select 3 ,  2     ,   11        ,     100       ,     '1400' 
insert into 产品属性内容表 select 4 ,  2     ,   11        ,     101       ,     '600' 
go
create function dbo.f_str
(
  @id int,
  @ptreeid int
)
    returns varchar(100)
as
begin
  declare @re varchar(100)
  set @re=''

  select @re=@re+','+a.attribute_name+' '+attribute_content
  from 产品属性表 a 
    join 产品属性内容表 b
      on a.id=b.attribute_id and a.attribute_id=b.attribute_id
  where a.ptreeid=@ptreeid and b.productid=@id

  return stuff(@re,1,1,'')
end
go

select b.*,dbo.f_str(b.id,b.ptreeid) details
from 类别表 a
  join 产品表 b
     on a.id=b.ptreeid
where a.name='硬盘'

go
drop table 类别表,产品表,产品属性表,产品属性内容表
drop function f_str

/*
id          ptreeid     name       price           details      
----------- ----------- ---------- ----------- ---------------
10          2           金钻100G     500         转速 7200,重量 500
11          2           金钻200G     600         转速 1400,重量 600

（所影响的行数为 2 行）

*/

[hery2002]

B产品表(ptreeid为类别表中的ＩＤ) 
id ptreeid   name       price 
10   2       金钻100G    500 
11   2       金钻200G    600 

C产品属性表（产品属性表,ptreeid为类别表中的ＩＤ，当为２时，则代表硬盘中的属性） 
id   ptreeid  attribute_id  attribute_name 
100    2        100            转速 
101    2        101            重量            

D产品属性内容表（产品属性具体内容表,ptreeid为类别表中的ＩＤ，productid为Ｂ产品表的ＩＤ，attribute_id为属性表中ID，attribute_content则代表内容） 
id ptreeid  productid  attribute_id    attribute_content 
1   2        10             100            7200 
2   2        10             101            500 
3   2        11             100            1400 
4   2        11             101            600 

create table B (id int,ptreeid int, cname nvarchar(200),price money)
insert into B values(10,2,'金钻100G',500)
insert into B values(11,2,'金钻200G',600)
go
create table C(id int, ptreeid int,attribute_id int, attribute_name nvarchar(200))
go
insert into C values(100,2,100 ,'转速')
insert into C values(101,2,101 ,'重量')
go
create table D(id int,ptreeid int,productid int, attribute_id int,attribute_content nvarchar(200))
go
insert into D values(1,2,10,100,7200)
insert into D values(2,2,10,101,500)
insert into D values(3,2,11,100,7200)
insert into D values(4,2,11,101,600)
go

select b.*,F.attr_content from b
left join (
SELECT productid,
           MAX( CASE seq WHEN 1 THEN attr_content ELSE '' END ) + ', ' +          
           MAX( CASE seq WHEN 2 THEN attr_content ELSE '' END ) as attr_content
      FROM ( SELECT p1.productid, p1.attr_content,
                    ( SELECT COUNT(*) 
                        FROM ( select d.productid,c.attribute_name+'  '+d.attribute_content as attr_content from c,d where c.id = d.attribute_id ) p2
                       WHERE p2.productid = p1.productid
                         AND p2.attr_content <= p1.attr_content )
               FROM (select d.productid,c.attribute_name+'  '+d.attribute_content as attr_content from c,d where c.id = d.attribute_id) p1 ) D ( productid, attr_content, seq )
     GROUP BY productid 
) F on b.id = F.productid

/*
id          ptreeid     cname    price                 attr_content
----------- ----------- -------- --------------------- ---------------------------
10          2           金钻100G    500.00                重量  500, 转速  7200
11          2           金钻200G    600.00                重量  600, 转速  7200

(2 row(s) affected)

*/

[hery2002]

select b.*,F.attr_content from b
left join (
SELECT productid,
           MAX( CASE seq WHEN 1 THEN attr_content ELSE '' END ) + ' ' +          
           MAX( CASE seq WHEN 2 THEN attr_content ELSE '' END ) as attr_content
      FROM ( SELECT p1.productid, p1.attr_content,
                    ( SELECT COUNT(*) 
                        FROM ( select d.productid,c.attribute_name+'  '+d.attribute_content as attr_content from c,d where c.id = d.attribute_id ) p2
                       WHERE p2.productid = p1.productid
                         AND p2.attr_content >= p1.attr_content )
               FROM (select d.productid,c.attribute_name+'  '+d.attribute_content as attr_content from c,d where c.id = d.attribute_id) p1 ) D ( productid, attr_content, seq )
     GROUP BY productid 
) F on b.id = F.productid

/*
id          ptreeid     cname     price                 attr_content
----------- ----------- -------- --------------------- -----------------------
10          2           金钻100G  500.00                转速  7200 重量  500
11          2           金钻200G  600.00                转速  7200 重量  600

(2 row(s) affected)

*/

[hery2002]

参考:
Concatenating row values in Transact-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
or
http://blog.csdn.net/hery2002/archive/2008/04/13/2288347.aspx

[liangCK]

楼上很强大．．

[marcus008]

太历害了.
谢谢两位.
谢谢您们了.