1

Discussing with an coworker about data structures and I was reminded I use to convert from "Schema A" to "Schema B", is this considered "normalization/de-normalization" or schema conversion or what? Any insight would be appreciated.

"Schema A"

TableA
ID KEY Value
------------
1  A   23
1  B   250
2  B   100

TableB
Key Description
---------------
A   Age
B   Weight

"Schema B"

TableC
ID Age  Weight
--------------
1  23   250
2  NULL 100
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
TSQL_Noob
  • 187
  • 1
  • 1
  • 12
  • I took the liberty to edit the title. Hope you agree with the edit. – usr Apr 03 '13 at 22:06
  • Schema A is called an EAV schema (EntityAttributeValue). Schema B is a "normal" 3 column table. The operation is called PIVOT(-ing) in SQL terms, or TRANSPOSE in mathematics (and SAS) – wildplasser Apr 03 '13 at 22:07
  • 1
    As @usr just reminded me, they are both normalised. If you're interested in which might suit your need best this might help you. http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question – PeteGO Apr 03 '13 at 22:22

1 Answers1

0

This is called pivoting.

Lacking a better reference I'll link to SQL Server's implementation of PIVOT.

The reverse is called unpivot.

You don't need special SQL operators to execute them. Pivot can be implemented using aggregates and unpivot can be implemented by joining a table of constants.

usr
  • 168,620
  • 35
  • 240
  • 369