I have two table. one table has field name and another table has field name & value too. i am trying to transpose rows to columns but getting Null for few rows. wrong output is coming.
Here is my table schema & data which help anyone to simulate the issue.
CREATE TABLE [dbo].[tblValuationFieldValue](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ticker] [varchar](10) NULL,
[ClientCode] [varchar](10) NULL,
[GroupName] [varchar](10) NULL,
[FieldName] [varchar](100) NULL,
[FieldValue] [varchar](100) NULL,
[RecOrder] [int] NULL,
[InsertedOn] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblValuationSubGroup] Script Date: 8/14/2021 6:14:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblValuationSubGroup](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ticker] [varchar](10) NULL,
[ClientCode] [varchar](10) NULL,
[GroupName] [varchar](10) NULL,
[FieldName] [varchar](100) NULL,
[FieldOrder] [int] NULL,
[InsertedOn] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblValuationFieldValue] ON
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [RecOrder], [InsertedOn]) VALUES (1, N'G', N'SNFLD', N'Group1', N'Last Update', N'2021-08-14', 1, CAST(N'2021-08-14T04:50:46.270' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [RecOrder], [InsertedOn]) VALUES (2, N'G', N'SNFLD', N'Group1', N'Broker', N'Barclays Capital', 2, CAST(N'2021-08-14T04:50:46.287' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [RecOrder], [InsertedOn]) VALUES (3, N'G', N'SNFLD', N'Group2', N'EV / EBITDA', N'1.6x', 1, CAST(N'2021-08-14T04:50:46.290' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [RecOrder], [InsertedOn]) VALUES (4, N'G', N'SNFLD', N'Group2', N'EV / Revenue', N'5.0x', 2, CAST(N'2021-08-14T04:50:46.290' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [RecOrder], [InsertedOn]) VALUES (5, N'G', N'SNFLD', N'Group1', N'Last Update', N'2021-08-15', 1, CAST(N'2021-08-14T05:31:56.800' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [RecOrder], [InsertedOn]) VALUES (6, N'G', N'SNFLD', N'Group1', N'Broker', N'Jeffry', 2, CAST(N'2021-08-14T05:31:56.800' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[tblValuationFieldValue] OFF
GO
SET IDENTITY_INSERT [dbo].[tblValuationSubGroup] ON
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (1, N'G', N'SNFLD', N'Group1', N'Last Update', 1, CAST(N'2021-08-13T07:02:31.890' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (2, N'G', N'SNFLD', N'Group1', N'Broker', 2, CAST(N'2021-08-13T07:02:44.817' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (6, N'G', N'SNFLD', N'Group2', N'EV / EBITDA', 1, CAST(N'2021-08-13T07:51:05.763' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (4, N'G', N'SNFLD', N'Group2', N'EV / Revenue', 2, CAST(N'2021-08-13T07:03:33.990' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[tblValuationSubGroup] OFF
GO
ALTER TABLE [dbo].[tblValuationFieldValue] ADD CONSTRAINT [DF_tblValuationFieldValue_InsertedOn] DEFAULT (getdate()) FOR [InsertedOn]
GO
ALTER TABLE [dbo].[tblValuationSubGroup] ADD CONSTRAINT [DF_tblValuationSubGroup_InsertedOn] DEFAULT (getdate()) FOR [InsertedOn]
GO
This script i am trying which is not giving right output.
DECLARE @Ticker VARCHAR(10)
DECLARE @ClientCode VARCHAR(10)
DECLARE @GroupName VARCHAR(10)
SET @Ticker='G'
SET @ClientCode='SNFLD'
SET @GroupName='Group1'
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT f.ID,f.ticker,f.ClientCode,f.GroupName,' + STUFF((SELECT N',' + @CRLF + N' ' +
N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN FieldValue END) AS ' + QUOTENAME(FieldName)
FROM tblValuationSubGroup g
WHERE ticker=@Ticker AND ClientCode=@ClientCode AND GroupName=@GroupName
GROUP BY FieldName
ORDER BY MIN(FieldOrder)
FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +
N'FROM tblValuationFieldValue f' + @CRLF +
N'WHERE f.Ticker = '''+@Ticker+''' AND f.ClientCode = '''+@ClientCode+''' AND f.GroupName='''+@GroupName+'''
GROUP BY f.ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder ORDER BY f.RecOrder';
EXEC sys.sp_executesql @SQL
screen shot given of output where NULL is coming.

Expected output will be
+--------+--------+-------------+-----------+-------------+------------------+
| ID | Ticker | ClientCode | GroupName | Last Update| Broker |
+--------+--------+-------------+-----------+-------------+------------------+
| 1 | G | SNFLD | Group1 | 2021-08-14 | Barclays Capital |
| 2 | G | SNFLD | Group1 | 2021-08-15 | Jeffry |
+--------+--------+-------------+-----------+-------------+------------------+
I have two table tblValuationSubGroup & tblValuationFieldValue i want to fetch field name from tblValuationSubGroup table and order field name value by FieldOrder field in table.
this table tblValuationFieldValue has field name and value too.
Now i want to show the value ticker, clientcode and Group name wise.
1) Field Order will be based on FieldOrder column of tblValuationSubGroup table.
2) records order will be based on RecOrder column of tblValuationFieldValue table.
Now there 2 fields called Last Update & Broker. so they are coming as column header. in reality there would be more fields name for Ticker, clientcode & GroupName wise. so if there would be more value will be saved in FieldName column of two table tblValuationSubGroup & tblValuationFieldValue then those fields should come as a column header.
I am not very good in sql. So please suggest me what to change in my existing code as a result i will get desired output. Thanks
EDIT 1
my actual data
CREATE TABLE [dbo].[tblValuationFieldValue](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ticker] [varchar](10) NULL,
[ClientCode] [varchar](10) NULL,
[GroupName] [varchar](10) NULL,
[FieldName] [varchar](100) NULL,
[FieldValue] [varchar](100) NULL,
[InsertedOn] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblValuationSubGroup] Script Date: 8/16/2021 10:17:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblValuationSubGroup](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ticker] [varchar](10) NULL,
[ClientCode] [varchar](10) NULL,
[GroupName] [varchar](10) NULL,
[FieldName] [varchar](100) NULL,
[FieldOrder] [int] NULL,
[InsertedOn] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblValuationFieldValue] ON
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (1, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'5/7/2021', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (2, N'GRPN', N'GRPN', N'Group1', N'Broker', N'Barclays Capital', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (3, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Underweight', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (4, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Sell', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (5, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$35.00', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (6, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'5/12/2021', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (7, N'GRPN', N'GRPN', N'Group1', N'Broker', N'Credit Suisse', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (8, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Neutral', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (9, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Hold', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn]) VALUES (10, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$42.00', CAST(N'2021-08-16T09:33:35.723' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[tblValuationFieldValue] OFF
GO
SET IDENTITY_INSERT [dbo].[tblValuationSubGroup] ON
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (1, N'GRPN', N'GRPN', N'Group1', N'Last Update', 1, CAST(N'2021-08-16T04:44:19.540' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (2, N'GRPN', N'GRPN', N'Group1', N'Broker', 2, CAST(N'2021-08-16T04:44:34.310' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (3, N'GRPN', N'GRPN', N'Group1', N'Rating', 3, CAST(N'2021-08-16T04:44:46.810' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (4, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', 4, CAST(N'2021-08-16T04:45:01.383' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (5, N'GRPN', N'GRPN', N'Group1', N'Target Price', 5, CAST(N'2021-08-16T04:45:29.750' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (6, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', 1, CAST(N'2021-08-16T04:45:49.907' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (7, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', 2, CAST(N'2021-08-16T04:46:02.647' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (8, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', 3, CAST(N'2021-08-16T04:46:13.190' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (9, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', 4, CAST(N'2021-08-16T04:46:24.373' AS DateTime))
GO
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn]) VALUES (10, N'GRPN', N'GRPN', N'Group2', N'EV/GP', 5, CAST(N'2021-08-16T04:46:37.143' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[tblValuationSubGroup] OFF
GO
ALTER TABLE [dbo].[tblValuationFieldValue] ADD CONSTRAINT [DF_tblValuationFieldValue_InsertedOn] DEFAULT (getdate()) FOR [InsertedOn]
GO
ALTER TABLE [dbo].[tblValuationSubGroup] ADD CONSTRAINT [DF_tblValuationSubGroup_InsertedOn] DEFAULT (getdate()) FOR [InsertedOn]
GO
When i run your script then i a getting wrong output
DECLARE @Ticker VARCHAR(10)
DECLARE @ClientCode VARCHAR(10)
DECLARE @GroupName VARCHAR(10)
SET @Ticker='GRPN'
SET @ClientCode='GRPN'
SET @GroupName='Group1'
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT ROW_NUMBER() over (order by (select 1)) ID,f.ticker,f.ClientCode,f.GroupName,' + STUFF((SELECT N',' + @CRLF + N' ' +
N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN FieldValue END) AS ' + QUOTENAME(FieldName)
FROM tblValuationSubGroup g
WHERE ticker=@Ticker AND ClientCode=@ClientCode AND GroupName=@GroupName
GROUP BY FieldName
ORDER BY MIN(FieldOrder)
FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +
N'FROM (select (ID-ROW_NUMBER() over (partition by ticker,ClientCode,GroupName order by id)) groupID,* from tblValuationFieldValue' + @CRLF +
N'WHERE Ticker = '''+@Ticker+''' AND ClientCode = '''+@ClientCode+''' AND GroupName='''+@GroupName+''') f' + @CRLF +
N'GROUP BY f.ticker,f.ClientCode,f.GroupName ,groupID ';
EXEC(@SQL)
i am getting one records but there are two records now in table and it could be more records than 2 records.
here is screen shot attached which i got after running your script

1) No records is coming for Barclays Capital rathet only one records is coming which is Credit Suisse
please share a right script. Thanks
