Online: 8259
Online: 8259
Online: 8259
Many a times, our database table structure are not in proper ways where our data are stored into rows and columns format where rows are only data.
Look at this database table design.
In PersonalDetailsData table, we are storing specific attributes of a person into DataName rows and its value in DataValue rows. To identify the person for which these data are stored, we have a PersonalDetailsId column that is the foreign key of primary table PersonalDetails.
Using SELECT record from database table gives data in above format that is very tough to present and understand to the end user.
The solution of this problem is to convert the unique DataName values into columns and give its values as DataValue in each rows like bellow
To do this, we need to use PIVOT clause like below.
In the above query, we are selecting the Height, [Weight], Eyes and PersonalDetailsId from subquery formed with the help of PARTITION BY clause.
The first set of selected statement adds a columns starting with ‘param…’ by partitioning the columns of the PersonalDetailsData that has alias PD.
Now the next set of highlighted statement select the DataValue for each DataName written into PersonalDetailsData table.
All this is shown in below picture. The first result is the raw data of PersonaDetailsData table and the 2nd result set is converted data.
The second result set is easy to understand and present to the end user.
