SQL Server: How to convert rows to columns

Question

I have a table where I store column name and its value ticker & client wise.

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

Here i tried this sql which is throwing error for duplicate values in field name.

This is because your GROUP BY is on FieldName, id,Ticker,ClientCode. You are therefore telling the RDBMS you want a row for every distinct group of those columns, and very clearly that would result in multiple rows for the same value of FieldName.

Very likely the GROUP BY and ORDER BY shouldn't be there at all:

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,'');

---

Now we have sample data, I can provide a full solution. Personally, as well, I would use a conditional aggregate, rather than the restrictive PIVOT operator, and build my entire statement in one go. I continue to use FOR XML PATH as I assume you used it (rather than STRING_AGG) due to being on SQL Server 2016 or prior.

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT ' + STUFF((SELECT N',' + @CRLF + N'       ' +
                                      N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN Value END) AS ' + QUOTENAME(FieldName)
                               FROM dbo.DynamicForm
                               GROUP BY FieldName
                               ORDER BY MIN(ID)
                               FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +
           N'FROM dbo.DynamicForm' + @CRLF +
           N'WHERE Ticker = @Ticker' + @CRLF +
           N'  AND ClientCode = @ClientCode' + @CRLF + 
           N'GROUP BY [Order]' + @CRLF + --ORDER is a reserved keyword, and should not be used for object names
           N'ORDER BY [Order];'; --ORDER is a reserved keyword, and should not be used for object names

DECLARE @Ticker varchar(10) = 'X',
        @ClientCode varchar(10) = 'Z';

--Print @SQL; -- Your best friend
EXEC sys.sp_executesql @SQL, N'@Ticker varchar(10), @ClientCode varchar(10)', @Ticker, @ClientCode;

db<>fiddle

Answer 2

If you change the query which assigns values to @Col variable as below added your query will work. To define a table columns they should be identical. No table can have two columns with same name. That was your error given by the code. But I am not sure about your expected output. As I see a where clause which filters data I hope your answer will have only one row.

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FieldName) 
                from DynamicForm                    -              
        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;