Member Avatar for 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

  • 3 Contributors
  • 2 Replies
  • 108 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by cgyrob

Dani AI

Generated

Both and show the common per-column subquery/update pattern. That works fine for a small, fixed set of properties, but when the property list can change or you want a single concise query, use SQL Server's PIVOT or a dynamic PIVOT. SQL Server 2005 supports PIVOT, which turns PropertyName rows into columns and lets you aggregate PropertyValue in one pass.

A static PIVOT is useful when you know the target columns up front:

SELECT CarId, [Size], [Name], [Color]
FROM
(
  SELECT CarId, PropertyName, PropertyValue FROM tblOld
) AS src
PIVOT
(
  MAX(PropertyValue) FOR PropertyName IN ([Size],[Name],[Color])
) AS p;

If property names are not known ahead of time, build the column list at runtime (STUFF + FOR XML PATH is the usual approach), then assemble and execute the PIVOT SQL via sp_executesql. The dynamic route avoids schema changes and automates new property columns, but remember to parameterize/validate any injected text to avoid risks.

Practical notes:

  • PIVOT needs an aggregate; use MAX/MIN to pick one value if duplicates exist, or pre-aggregate/ROW_NUMBER to choose a preferred row.
  • PropertyValue will be treated as a single SQL type in the pivoted columns; explicitly CAST/CONVERT if values can be numeric/dates and you need consistent types.
  • NULLs mean "absent property"—use COALESCE if you need empty strings.
  • Add an index on (CarId, PropertyName) to speed the transform on large tables.
  • If attributes are mostly stable, prefer a normalized wide table (explicit columns) for clarity and type safety; keep the EAV model only when flexibility is essential.

These suggestions complement the per-column methods already shown and provide scalable, maintainable options for SQL Server 2005.

Recommended Answers

All 2 Replies

Member Avatar for 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.

Member Avatar for 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
Edited by cgyrob because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.