自定义属性、值，行转列

Question

查询的结果
GID GNumber GName         AName VName
3 p003 仿古牛仔裤 尺码 34寸
3 p003 仿古牛仔裤 颜色 蓝色
4 p004 西装短裤 尺码 34寸
4 p004 西装短裤 颜色 蓝色
5 p005 7分裤         尺码 32寸
5 p005 7分裤         颜色 白色怎么转成
GID GNumber GName 尺码 颜色
3 p003 仿古牛仔裤 34寸 蓝色
4 p004 西装短裤 34寸 蓝色
5 p005 7分裤 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

Answer 1

--> 测试数据：[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 all
select 3,'p003','仿古牛仔裤','颜色','蓝色' union all
select 4,'p004','西装短裤','尺码','34寸' union all
select 4,'p004','西装短裤','颜色','蓝色' union all
select 5,'p005','7分裤','尺码','32寸' union all
select 5,'p005','7分裤','颜色','白色'declare @str varchar(max)
set @str=''
select @str=@str+','+[AName]+'=max(case when [AName]='+quotename([AName],'''')
    +' then [VName] else '''' end)'
from test
group by [AName]
exec('select [GNumber],[GName]'+@str+' from test group by [GNumber],[GName]')/*
GNumber GName 尺码 颜色
---------------------------------------
p005 7分裤 32寸 白色
p003 仿古牛仔裤 34寸 蓝色
p004 西装短裤 34寸 蓝色
*/

Answer 2

2楼，不懂sql2005，搜索了，看了不是很明白
3楼，谢谢，解决部分问题自己搞弄了这么一个存储过程，希望大家指导一下
感觉写的一大堆很乱，将来数据量大了（商品1000，属性记录不大于10000）的话会不会没有效率
----------------------------------------------
create procedure GoodsShow
@cid int
as
set nocount on
declare @attField varchar(200),@sql varchar(2000)
set @attField=''
set @sql=''
select @attField =@attField+ AName + ',' from (select AName from Attribute where ACID=@cid) as temp
set @attField=left(@attField,len(@attField)-1)


set @sql = 'select GID,GNumber,GName,' + @attField + ' from (
SELECT  GID,GNumber,GName,AName,VName from 
(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=' + cast(@cid as varchar) + '
) as tt 
) as [SourceTable]
PIVOT (
   max(VName) FOR AName IN (' + @attField + ')
) AS [PivotTable]'
exec(@sql)
go
--------------------------------------------------
exec GoodsShow 1   --类别1，手机
exec GoodsShow 2   --类别2，服装

Answer 3

是的，商品类别、类别属性、属性值都是自定义的，
有的类别有10个属性，有的只有3个
GID         GNumber    GName      屏幕尺寸       操作系统       手机制式
----------- ---------- ---------- ---------- ---------- ----------
1           p001       IPhone4    3.2寸       ios        GSM
2           p002       G10        3.5寸       Android    GSM
GID         GNumber    GName      尺码         颜色
----------- ---------- ---------- ---------- ----------
3           p003       仿古牛仔裤      34寸        蓝色
4           p004       西装短裤       34寸        蓝色
5           p005       7分裤        32寸        白色因为属性值要显示到前台给用户筛选搜索
所以要把属性转成列好久没有搞过sql了，只学过点sql2000，对新的特性PIVOT等 的不是很明白