cross table/pivot table?

Question

cross table/pivot table?

yu_f

SSC Rookie

Points: 37
More actions
February 11, 2010 at 3:03 pm

#215563

I have a data in a table like this:
ProductIDAttributeNameAttributeValue
5sizeSmall
5sizeMedium
5sizeLarge
5colorYellow
5colorBlue
5LanguageEnglish
5LanguageChinese
7LanguageEnglish
7LanguageChinese
I want to do cross tab/pivot table thing to make it looks like this:
ProductIDSizeColorLanguage
5smallyellowEnglish
5smallYellowChinese
5 smallBlue English
5smallblue chinese
5MedyellowEnglish
5MedYellowChinese
5 MedBlue English
5Medblue chinese
….
The attribute will be dynamic, means the new ones could be added in the future; and the attribute values will be different based on product, for example, for product 5, size attribute could have small, medium, xlarge, but product 3, size attribute could have small, 2x-large (I have another table to hold all attribute value, for exmaple, size attribute will have all sizes, from small to 7x-large). And product 5 may only have size attribute, but product 5 has size, color, material, etc attributes…
basically I want to have every combination of different attributes for one product.
Please help!
really appreciate it.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

How about changing your data model to be a relational one rather than that ETL stuff?

It's always a PITA to try to get relational transformation out of an object oriented structure simply copied into tables....

btw: Why doesn't your result set include PrdoductId 7?

If you insits on your data model please provide table def and sample data in a ready to use format as described in the first link in my signature. And please include table def and sample data for your table referred to as "to hold all attribute value".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

yu_f SSC Rookie Points: 37 More actions · Answer 2

Thank you so much Lutz.

here is the table and a couple of test records...

CREATE TABLE [dbo].[aspdnsf_Attribute](

[AttributeID] [int] IDENTITY(1,1) NOT NULL,

[AttributeGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttribute_AttributeGUID] DEFAULT (newid()),

[AttributeName] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[AttributePrompt] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CurrentInd] [bit] NOT NULL,

[PortalID] [int] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttribute_PortalID] DEFAULT ((0)),

[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttribute_CreatedOn] DEFAULT (getdate()),

CONSTRAINT [PK_aspdnsf_ProductAttribute] PRIMARY KEY CLUSTERED

(

[AttributeID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[aspdnsf_AttributeValue](

[AttributeValueID] [int] IDENTITY(1,1) NOT NULL,

[AttributeValueGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttributeValue_AttributeValueGUID] DEFAULT (newid()),

[AttributeID] [int] NOT NULL,

[AttributeValue] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[AttributeModifier] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CurrentInd] [bit] NOT NULL,

[PortalID] [int] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttributeValue_PortalID] DEFAULT ((0)),

[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttributeValue_CreatedOn] DEFAULT (getdate()),

CONSTRAINT [PK_aspdnsf_ProductAttributeValue] PRIMARY KEY CLUSTERED

(

[AttributeValueID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[aspdnsf_ProductAttributeValue](

[ProductAttributeValueID] [int] IDENTITY(1,1) NOT NULL,

[ProductAttributeValueGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttributeValue_ProductAttributeValueGUID] DEFAULT (newid()),

[ProductID] [int] NOT NULL,

[AttributeID] [int] NOT NULL,

[AttributeValueID] [int] NOT NULL,

[CurrentInd] [bit] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttributeValue_CurrentInd] DEFAULT ((1)),

[PortalID] [int] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttributeValue_PortalID_1] DEFAULT ((0)),

[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_aspdnsf_ProductAttributeValue_CreatedOn_1] DEFAULT (getdate()),

CONSTRAINT [PK_aspdnsf_ProductAttributeValue_1] PRIMARY KEY CLUSTERED

(

[ProductAttributeValueID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO [aspdnsf_Attribute]([AttributeName],[AttributePrompt])VALUES('Size' ,'Size')

INSERT INTO [aspdnsf_Attribute]([AttributeName],[AttributePrompt])VALUES('Color' ,'Color')

INSERT INTO [aspdnsf_Attribute]([AttributeName],[AttributePrompt])VALUES('Language' ,'Language')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(1,'small')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(1,'medium')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(1,'large')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(2,'yellow')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(2,'blue')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(3,'english')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(3,'chinese')

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(5,1,1)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(5,1,2)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(5,1,3)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(5,2,4)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(5,2,5)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(5,3,6)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(7,1,1)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(7,1,2)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(7,2,3)

INSERT INTO [aspdnsf_ProductAttributeValue]([ProductID],[AttributeID],[AttributeValueID]) VALUES(7,2,5)

LutzM SSC Guru Points: 107049 More actions · Answer 3

Did you actually try your sample data within a test database?

It seems like you didn't...

Please modify your sample data.

Side note: You're on the right track in terms of providing the data! You're almost there. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

yu_f SSC Rookie Points: 37 More actions · Answer 4

that's my bad...

wondering if I can utilize pivot or something else to do the combination, or do I have to do the join/cross join in this case? thanks.

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

CREATE TABLE [dbo].[aspdnsf_Attribute](

[AttributeID] [int] IDENTITY(1,1) NOT NULL,

[AttributeName] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[AttributePrompt] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

INSERT INTO [aspdnsf_Attribute]([AttributeName],[AttributePrompt])VALUES('Size' ,'Size')

INSERT INTO [aspdnsf_Attribute]([AttributeName],[AttributePrompt])VALUES('Color' ,'Color')

INSERT INTO [aspdnsf_Attribute]([AttributeName],[AttributePrompt])VALUES('Language' ,'Language')

CREATE TABLE [dbo].[aspdnsf_AttributeValue](

[AttributeValueID] [int]IDENTITY(1,1) NOT NULL,

[AttributeID] [int] NOT NULL,

[AttributeValue] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(1,'small')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(1,'medium')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(1,'large')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(2,'yellow')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(2,'blue')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(3,'english')

INSERT INTO [aspdnsf_AttributeValue]([AttributeID],[AttributeValue]) VALUES(3,'chinese')

CREATE TABLE [dbo].[aspdnsf_ProductAttributeValue](

[ProductAttributeValueID] [int] IDENTITY(1,1) NOT NULL,

[ProductID] [int] NOT NULL,

[AttributeID] [int] NOT NULL,

[AttributeValueID] [int] NOT NULL