自定义属性、值，行转列,该怎么处理

自定义属性、值，行转列

查询的结果
GIDGNumberGName ANameVName
3p003仿古牛仔裤尺码34寸
3p003仿古牛仔裤颜色蓝色
4p004西装短裤尺码34寸
4p004西装短裤颜色蓝色
5p0057分裤 尺码32寸
5p0057分裤 颜色白色

怎么转成
GIDGNumberGName尺码颜色
3p003仿古牛仔裤34寸蓝色
4p004西装短裤34寸蓝色
5p0057分裤32寸白色

/******************** 类别表Category ***********************/
create table Category
(
CID int primary key identity(1,1),
CName nvarchar(10) not null
)

insert Category values('手机')
insert Category values('服装')

/******************** 类别属性表Attribute（用户自定义） ***********************/
create table Attribute
(
AID int primary key identity(1,1),
ACID int foreign key references Category(CID),
AName nvarchar(10) not null
)

insert Attribute values(1, '屏幕尺寸')
insert Attribute values(1, '操作系统')
insert Attribute values(1, '手机制式')
insert Attribute values(2, '尺码')
insert Attribute values(2, '颜色')

/******************** 属性值表AttDefaultValue（用户自定义） ***********************/
create table AttDefaultValue
(
VID int primary key identity(1,1),
AID int foreign key references Attribute(AID) not null,
VName nvarchar(10) not null
)

insert AttDefaultValue values(1,'3.2寸')
insert AttDefaultValue values(1,'3.5寸')
insert AttDefaultValue values(1,'4.0寸')
insert AttDefaultValue values(1,'4.3寸')

insert AttDefaultValue values(2,'ios')
insert AttDefaultValue values(2,'wp7.5')
insert AttDefaultValue values(2,'Android')
insert AttDefaultValue values(2,'symbian')

insert AttDefaultValue values(3,'CDMA')
insert AttDefaultValue values(3,'GSM')

insert AttDefaultValue values(4,'32寸')
insert AttDefaultValue values(4,'34寸')
insert AttDefaultValue values(4,'36寸')

insert AttDefaultValue values(5,'蓝色')
insert AttDefaultValue values(5,'黑色')
insert AttDefaultValue values(5,'白色')
insert AttDefaultValue values(5,'红色')

/******************** 商品表Goods ***********************/
create table Goods
(
GID int primary key identity(1,1),
CategoryID int foreign key references Category(CID) not null,
GNumber nvarchar(10) not null,
GName nvarchar(10) not null
)

insert Goods values(1,'p001','IPhone4')
insert Goods values(1,'p002','G10')

insert Goods values(2,'p003','仿古牛仔裤')
insert Goods values(2,'p004','西装短裤')
insert Goods values(2,'p005','7分裤')

/******************** 属性值记录表AttRecord ***********************/
create table AttRecord
(
RID int primary key identity(1,1),
RGID int foreign key references Goods(GID) not null,
RValueID int foreign key references AttDefaultValue(VID) not null
)

insert AttRecord values(1,1)
insert AttRecord values(1,5)
insert AttRecord values(1,10)

insert AttRecord values(2,2)
insert AttRecord values(2,7)
insert AttRecord values(2,10)

insert AttRecord values(3,12)
insert AttRecord values(3,14)

insert AttRecord values(4,12)
insert AttRecord values(4,14)

insert AttRecord values(5,11)
insert AttRecord values(5,16)


select G.GID,G.GNumber,G.GName,AB.AName,DV.VName from Goods G
inner join AttRecord AR on G.GID=AR.RGID
inner join AttDefaultValue DV on AR.RValueID=DV.VID
inner join Attribute AB on AB.AID=DV.AID where G.CategoryID=2

[解决办法]
动态行转列

搜索行转列精华
[解决办法]

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([GID] int,[GNumber] varchar(4),[GName] varchar(10),[AName] varchar(4),[VName] varchar(4))insert [test]select 3,'p003','仿古牛仔裤','尺码','34寸' union allselect 3,'p003','仿古牛仔裤','颜色','蓝色' union allselect 4,'p004','西装短裤','尺码','34寸' union allselect 4,'p004','西装短裤','颜色','蓝色' union allselect 5,'p005','7分裤','尺码','32寸' union allselect 5,'p005','7分裤','颜色','白色'declare @str varchar(max)set @str=''select @str=@str+','+[AName]+'=max(case when [AName]='+quotename([AName],'''')    +' then [VName] else '''' end)'from testgroup by [AName]exec('select [GNumber],[GName]'+@str+' from test group by [GNumber],[GName]')/*GNumber    GName    尺码    颜色---------------------------------------p005    7分裤    32寸    白色p003    仿古牛仔裤    34寸    蓝色p004    西装短裤    34寸    蓝色*/