I currently have a database which is commonly used within our organization. We have a major issues which no one can seem to figure out so here it is.
I have two tables one called Cardholders and the other one is CustomFields. I have done an inner join on the tables to combine the fields into the same view. I then created a temp view table called "Pivot Temp" which is attached to the picture below(seems like new users can't upload pictures!).
I have a person who has multiple entries because he has many custom fields. I would like to know how can we pivot all the fields so they can be represented into ONE row.
In this new view table I have unique_id, LastName, FirstName, Active, AcitvationDate, ExpirationDate, CardCode, CustomFieldLabel, CustomFieldValue, AccessLevel.
In this case Pivot the CustomFieldLabel_TX to be a title and have the customFieldValue_TX as the value.
Currently there is one record per custom field value. For example John Doe looks like this.
I would like to pivot these tables so it says:
Any help is appreciated and if you have questions about the tables or fields please ask.