best wait to transpose all elements of a column into ColumnFields

[Julamotte]

Hello,

I would like to know if there is an easy way to transform the following table

CarId | PropertyName | PropertyValue
1 | Size | XL
1 | Name | Porsche
1 | Color | Red
2 | Size | XS
2 | Name | Smart
3 | Name | Audi

into

CarId | Size | Name | Color
1 | XL | Porsche | Red
2 | XS | Smart |
3 | | Audi |

with MSSQL 2005 (i am using Studio Express)

This a kind of crossTabling.

until now here is what i am doing:

I create a table why just 1 empty column (CarId)

For each CarId,
I put the cardId in CarId Column
For each couple (PropertyName,PropertyValue)
i test whether the column 'PropertyName' exists. If no, i create it
then i put the 'PropertyValue' in the box (x='CardId',y='PropertyName')

Thank you

Julien

[Celt.Max]

Well... let the first table has a name of "tblOld" and the second one "tblNew". As you said - fill the tblNew with CarId. But the new table has to have also other columns, I mean Size, Name and Color. It's quite easy then:

UPDATE tblNew 
SET Size = (SELECT TOP 1 PropertyValue FROM tblOld TBO WHERE TBO.CarId = tblNew.CarId AND TBO.PropertyName = 'Size')

UPDATE tblNew 
SET Name = (SELECT TOP 1 PropertyValue FROM tblOld TBO WHERE TBO.CarId = tblNew.CarId AND TBO.PropertyName = 'Name')

UPDATE tblNew 
SET Color = (SELECT TOP 1 PropertyValue FROM tblOld TBO WHERE TBO.CarId = tblNew.CarId AND TBO.PropertyName = 'Color')

If in the original table the property didn't exist, it will come out as NULL value in the new one. This is not the clearest solution, but it works and is easy to understand.

[cgyrob]

Why don't you insert the records into the new table with something similar to this. It will create one row per carId and fill in the appropriate property values.

Insert into newTable(CarId, Size, Name, Color)
Select a.CarId,
(Select Propertyvalue from oldTable b where b.PropertyName = 'Size' and b.CarId = a.CarId),
(Select Propertyvalue from oldTable b where b.PropertyName = 'Name' and b.CarId = a.CarId),
(Select Propertyvalue from oldTable b where b.PropertyName = 'Color' and b.CarId = a.CarId)
From oldTable a
Group by a.CarId

output would be

CarId	Size	Name	Color
1	XL	Porsche	Red
2	XS	Smart	NULL
3	NULL	Audi	NULL