Creating a new table base on existing table data

Question

Creating a new table base on existing table data

Robert Taylor-292383

Newbie

Points: 3
More actions
January 10, 2006 at 6:44 am

#166679

Hi all,
I have a table that contains data as follows
"Object","Property","PropertyValue"
Ball,Colour,Red
Ball,Shape,round
Ball,Size,Small
Ball,CanFly,No
Ball,Floats,No
Bat,Colour,Blue
Bat,Shape,batshape
Bat,Size,Medium
Bat,CanFly,No
Bat,Floats,No
I would like to be able to constuct another table & populate according ly.
"Object","Colour","Shape","Size","CanFly","Floats"
Ball,Red,Round,Small,No,No
Bat,Blue,Batshape,Medium,No,No
All the properties are consistant accross each object & all the properties are strings, there are about 30 "properies" but 1000's of objects.
Any help greatly appreciated.

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

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

peterhe SSChampion Points: 11469 More actions · Answer 1

What you are trying to do is to pivot the data in your table. The following code will do this (correct any syntax error):

DECLARE @Properties TABLE (PID int identity NOT NULL, PName nvarchar(50) NOT NULL)

INSERT @Properties (PName) SELECT DISTINCT Property FROM YOurTable

DECLARE @PID int

DECLARE @sql nvarchar(4000)

SET @sql=N'SELECT Object,'

SELECT @PID = MIN(PID) FROM @Properties

WHILE @PID IS NOT NULL

BEGIN

SELECT @PName = PName FROM @Properties WHERE PID = @PID

SELECT @sql = @sql + nchar(13) + N' MAX(CASE Property WHEN ''' + @PName + ''' THEN PropertyValue ELSE NULL END) AS [' + @PName + '],'

SELECT @PID = MIN(PID) FROM @Properties WHERE PID>@PID

END

SET @sql=LEFT(@SQL,LEN(@SQL)-1)+nchar(13)+'FROM YOurTable GROUP BY Object ORDER BY Object'

PRINT @sql

EXEC(@SQL)

IF you have too many proeprties, the @sql may be overfolow. In this case you need to define mulitple variables like @SQL1, @SQL2 and intialize them to blank string in the begining.

and IF LEN(@SQL)>3500, SET @SQL1=@SQL1+'MAX(CASE...'

Robert Taylor-292383 Newbie Points: 3 More actions · Answer 2

Thanks for such a quick response, i will try this out tomorrow.

Robert Taylor-292383 Newbie Points: 3 More actions · Answer 3

Thanks! that 'pivots' my data perfectly,

The only thing is how do i create a new table matching this output?

My goal is to create a 2nd table that can be used for searching thru, this searching will be performed offline on a different server.

peterhe SSChampion Points: 11469 More actions · Answer 4

You can SELECT the pivoted data into a table:

SET @sql=LEFT(@SQL,LEN(@SQL)-1)+nchar(13)+

' INTO YourSecondTableName '+

'FROM YOurTable GROUP BY ...