Can you elaborate more and tell me the steps to do so?

I am using the below sql query:

SELECT [PivotData].[Name] AS Name,    

[PivotData].[Email] AS Email

FROM (

              SELECT {FieldData}.[TextValue], {Field}.[Label]

              FROM {Field} INNER JOIN {FieldData} ON {Field}.[Id] = {FieldData}.[FieldId]    

            ) AS SourceTable    

           PIVOT (

                          MAX([TextValue])        

                           FOR [Label] IN ([Name], [Email])    

                        ) AS PivotData; 

where field table is shown below:

The query shows the test output as shown below:

it does not show all the names and emails, it shows for only one user. Can anyone tell what was missing in the query above or can anyone tell the alternative query to fetch all the records of user name and email.

can you share an oml with datamodel and this sql in it, I'll have a look

Ok,

i see what the problem is :

in your pivot, you are using an aggregation function, much like you would in for example a group by.

The set of records your aggregation function gets executed on, is determined by the non-pivoted columns you include.  You are not having any non-pivoted columns, so he is selecting the MAX value for both the name and the email on the whole table.

You have to add the thing that will identify each line of your result set as an extra attribute in the inner query.  I don't know your exact datamodel for the fielddata table, but you should have somewhere in there the concept of 'records', so you should add that distinguishing thing to your inner query.

See attached oml for an example.

with this datamodel

this is the sql, notice the yellow part is crucial to get a line in the result for each of your records

Dorine

P.S. the actual fieldnames are here hardcoded, just to show you how pivot works, so example works if you do have fields with the names 'Name' and 'Hobby' defined.  

If you would want to make a generic form (which I have already pointed out in other post, might not be a good plan), you'll of course have to produce the FOR fieldname in ..... clause based on your metamodel or something, and pass into the sql as a parameter.

I have downloaded and tested this .oml file.

The test output shows null value. Test the query once.

SELECT Name, HobbyFROM (SELECT {FieldInRecord}.[RecordId], {Field}.[Name] as fieldname, {FieldInRecord}.[TextValue] as fieldvalueFROM {FieldInRecord}JOIN {Field}  ON {FieldInRecord}.[FieldId] = {Field}.[Id]) AS datatable PIVOT(  MIN(fieldvalue)  FOR fieldname IN ([Name],[Hobby])) AS pivottable

This is the query I see in the sql section which is not working.

it is working:

 have you entered data in your entities ? are there fields with the name 'Name' and 'Hobby', are there records, did you give those records some field values for 'Name' and 'Hobby' ?

Here I was entered the fields and records. Where to enter the names and hobbies in this application?

that should be obvious really from the sql : it is looking for Field.Name of "Name" and "Hobby", so not "Field 1", "Field 2", ...

this example data will give you a result :

But really, I want to iterate again, if you are struggling with this, please , as a beginner, start with easier stuff, dynamic forms are not a trivial thing to tackle, you will run in to much more challenges after getting this pivot query to do what you want it to do.

Dorine

your fielddata.id in that inner sql makes it return a result row for each field data row, that should not be in there, instead it has to be the identifier of a single record (= the thing that holds multiple rows together to represent a single person)

From your image that would be FieldData.RecordId, I think

This query does not hold image column. I need to add here as to display image there is a different table.

The image you share at beginning of this post shows a column named RecordId

From the image you share, you need to involve RecordId into your query.
The output is not what you expected because the data does not bind to recordID.

Can you share your Entity Diagram?