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

Answer 1

Hi @T.Zacks ,

Please refer below:

DECLARE @cols AS NVARCHAR(MAX),  
@cols1 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,'');  
   
SELECT @cols1 = STUFF((SELECT DISTINCT ',max(' + QUOTENAME(FieldName) +') over (partition by Ticker,ClientCode,[order]) '+ QUOTENAME(FieldName)   
        FROM dbo.DynamicForm  
        WHERE Ticker='X'  
        AND ClientCode='Z'  
        FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)') ,1,1,'');  
  
set @query = N'SELECT distinct ' + @cols1 + N' from   
(  
    select *  
    from DynamicForm WHERE Ticker=''X'' AND ClientCode=''Z''  
) x  
pivot   
(  
    max(value)  
    for FieldName in (' + @cols + N')  
) p '  
  
exec sp_executesql @query;  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.