CREATE TABLE elecct ( pid int, prop_name VARCHAR(15),vid int, NAME VARCHAR(20));
insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232483,'IPone')
insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232484,'联想')
insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232486,'DELL
insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232487,'宏基')
insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232490,'PS2
insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232489,'XBOX')
select PID,PROP_NAME,stuff((select ',('+cast(VID as char(7))+','+NAME+')'
from elecct where PID=t.PID and PROP_NAME=t.PROP_NAME for xml path('')),1,1,'')
from elecct t group by PID,PROP_NAME
-------------------------------------
11 手机 (3232483,IPone),(3232484,联想)
12 电脑 (232486,DELL),(3232487,宏基')
13 游戏机 (3232490,PS2),(3232489,XBOX)
;;;;;;;;;;;;;;;;;;;;;;;;;
MYSQL实现
CREATE TABLE elecct ( pid int, prop_name VARCHAR(15),vid int, NAME VARCHAR(20));
insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232483,'IPone')
insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232484,'联想')
insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232486,'DELL
insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232487,'宏基')
insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232490,'PS2
insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232489,'XBOX')
select pid,prop_name,group_concat(concat('(',vid,NAME,')')) from elecct group by pid,prop_name --------------------------------------
11 手机 (3232483,IPone),(3232484,联想)
12 电脑 (232486,DELL),(3232487,宏基')
13 游戏机 (3232490,PS2),(3232489,XBOX)