SQL Server How to Transpose Data

Question

have a table where I store column name and its value ticker & client wise. i need to convert rows to column.

This is my table with data. screen shot attached.

Here i tried this sql which is throwing error for duplicate values in field name. i got this code from this post https://stackoverflow.com/a/15745076/9359783

But their code is not working for my scenario. please guide me what i need to alter in code.

DECLARE @cols AS NVARCHAR(MAX),  
@query  AS NVARCHAR(MAX)  
  
select @cols = STUFF((SELECT ',' + QUOTENAME(FieldName)   
                    from DynamicForm WHERE Ticker='X' AND ClientCode='Z'  
                    group by FieldName, id,Ticker,ClientCode  
                    order by id  
            FOR XML PATH(''), TYPE  
            ).value('.', 'NVARCHAR(MAX)')   
        ,1,1,'')  
  
  
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;  

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 is script which help you to get 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  

Answer 1

Hi @T.Zacks ,

I tried with different methods but failed. I have to add condition 'order=1' and remove the group part in stuff.

Please refer below updated one:

DECLARE @cols AS NVARCHAR(MAX),  
 @cols1 AS NVARCHAR(MAX),  
 @query  AS NVARCHAR(MAX)  
  
 select @cols = STUFF((SELECT  ',' + QUOTENAME(FieldName)   
                     from DynamicForm   
  WHERE Ticker='X' AND ClientCode='Z' and [order]=1  
  order by id  
             FOR XML PATH(''), TYPE  
             ).value('.', 'NVARCHAR(MAX)')   
         ,1,1,'')  
   
select @cols1 = STUFF((SELECT ',max(' + QUOTENAME(FieldName) +') over (partition by Ticker,ClientCode,[order]) '+ QUOTENAME(FieldName)   
                     from DynamicForm   
  WHERE Ticker='X' AND ClientCode='Z' and [order]=1  
  order by id  
             FOR XML PATH(''), TYPE  
             ).value('.', '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:

You could check the difference between the two stuffs by executing 'print @Query '.

If we did not add the second stuff using max function , we would get the result like below.

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.

Answer 2

You may not need a PIVOT. Try this:

;WITH CTE_Last_Update AS (
    SELECT [FieldName], [Value], [Order]
    FROM [dbo].[DynamicForm]
    WHERE [FieldName] = 'Last Update'
),
CTE_Last_Broker AS (
    SELECT [FieldName], [Value], [Order]
    FROM [dbo].[DynamicForm]
    WHERE [FieldName] = 'Broker'
),
CTE_Last_Analyst AS (
    SELECT [FieldName], [Value], [Order]
    FROM [dbo].[DynamicForm]
    WHERE [FieldName] = 'Analyst'
)

SELECT c1.[Value] AS [Last Update], c2.[Value] AS [Broker], c3.[Value] AS [Analyst]
FROM CTE_Last_Update AS c1
INNER JOIN CTE_Last_Broker AS c2 ON c1.[Order] = c2.[Order]
INNER JOIN CTE_Last_Analyst AS c3 ON c1.[Order] = c3.[Order];

Answer 3

Hi @T.Zacks ,

I found that there was a duplicate post in https://learn.microsoft.com/en-us/answers/questions/508156/sql-server-convert-rows-to-columns.html.
So I combined them so that we could discuss more intensively here.

Please refer below and check whether it is working:

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.