SQL Server Convert rows to columns

Question

I am facing issue when trying to convert rows to columns

Here is screenshot how my table looks like with data.

DECLARE @cols AS NVARCHAR(MAX),  
@query  AS NVARCHAR(MAX)  
  
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FieldName)   
                      FROM dbo.DynamicForm  
                      WHERE Ticker='X'  
                      AND ClientCode='Z'  
                      FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)') ,1,1,'');  
print @cols  
set @query = N'SELECT ' + @cols + N' from   
             (  
                select value, fieldname  
                from DynamicForm WHERE Ticker=''X'' AND ClientCode=''Z''  
            ) x  
            pivot   
            (  
                max(value)  
                for FieldName in (' + @cols + N')  
            ) p '  
  
exec sp_executesql @query;  

When i am executing above query then it return one records.

output would look like

OUTPUT would be look like

+-------------+----------------------+-----------------+  
| Last Update |        Broker        |     Analyst     |  
+-------------+----------------------+-----------------+  
| 7/6/2021    |    JMP Securities    | David M Scharf  |  
| 4/28/2021   |  Argus Research Corp | David E Coleman |  
+-------------+----------------------+-----------------+  

See here two records is coming and JMP Securities is getting first records because its orderid is
1. so data should be displayed as per Ticker & client code wise and orderId wise data should be order.

Here sharing script which anyone can use and simulate the issue. script will give you sample data.

CREATE TABLE [dbo].[DynamicForm](  
 [ID] [int] IDENTITY(1,1) NOT NULL,  
 [FieldName] [varchar](100) NULL,  
 [Value] [varchar](100) NULL,  
 [Ticker] [varchar](10) NULL,  
 [ClientCode] [varchar](10) NULL,  
 [Order] [int] NULL  
) ON [PRIMARY]  
GO  
SET IDENTITY_INSERT [dbo].[DynamicForm] ON   
GO  
INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (1, N'Last Update  
', N'4/28/2021  
', N'X', N'Z', 1)  
GO  
INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (2, N'Broker  
', N'Argus Research Corp  
', N'X', N'Z', 1)  
GO  
INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (3, N'Analyst   
', N'David E Coleman  
', N'X', N'Z', 1)  
GO  
INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (4, N'Last Update  
', N'7/6/2021  
', N'X', N'Z', 2)  
GO  
INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (5, N'Broker  
', N'JMP Securities  
', N'X', N'Z', 2)  
GO  
INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (6, N'Analyst   
', N'David M Scharf  
', N'X', N'Z', 2)  
GO  
SET IDENTITY_INSERT [dbo].[DynamicForm] OFF  
GO  

please guide me what to change in sql to get this output. thanks