SQL SERVER: How to sort by field name when field is dynamic

Question

SQL SERVER: How to sort by field name when field is dynamic

T.Zacks 3,996

Here i am pasting my schema and sample data which help people to simulate the issue.

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,  
 [ColumnWidth] [decimal](18, 2) NULL  
) ON [PRIMARY]  
  
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,  
 [RecOrder] [int] NULL,  
 [Comments] [varchar](200) NULL  
) ON [PRIMARY]  


INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (150, N'GRPN', N'GRPN', N'Group1', N'Last Update', 1, CAST(N'2021-08-26T04:44:05.500' AS DateTime), CAST(30.00 AS Decimal(18, 2)))  
GO  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (151, N'GRPN', N'GRPN', N'Group1', N'Broker', 2, CAST(N'2021-08-26T04:44:19.497' AS DateTime), CAST(35.00 AS Decimal(18, 2)))  
GO  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (152, N'GRPN', N'GRPN', N'Group1', N'Rating', 3, CAST(N'2021-08-26T04:44:29.867' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
GO  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (153, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', 4, CAST(N'2021-08-26T04:44:44.153' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
GO  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (154, N'GRPN', N'GRPN', N'Group1', N'Target Price', 5, CAST(N'2021-08-26T04:44:57.640' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (166, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', 2, CAST(N'2021-08-26T05:13:39.040' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
GO  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (167, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', 3, CAST(N'2021-08-26T05:13:49.127' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
GO  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (168, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', 4, CAST(N'2021-08-26T05:14:25.820' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
GO  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (169, N'GRPN', N'GRPN', N'Group2', N'EV/GP', 5, CAST(N'2021-08-26T05:14:36.707' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
INSERT [dbo].[tblValuationSubGroup] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldOrder], [InsertedOn], [ColumnWidth]) VALUES (165, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', 1, CAST(N'2021-08-26T05:13:24.947' AS DateTime), CAST(20.00 AS Decimal(18, 2)))  
  

  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1341, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'08/09/2021', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1342, N'GRPN', N'GRPN', N'Group1', N'Broker', N'Ascendiant Capital Markets, LLC', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1343, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Buy', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1344, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Buy', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1345, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$40.00', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1346, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'08/06/2021', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1347, N'GRPN', N'GRPN', N'Group1', N'Broker', N'Goldman', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1348, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Sell', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1349, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Sell', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1350, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$23.50', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 2, N'Goldman test1')  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1351, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'08/06/2021', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1352, N'GRPN', N'GRPN', N'Group1', N'Broker', N'Barclays Capital', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1353, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Underweight', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1354, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Sell', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1355, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$30.00', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1356, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'08/09/2021', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1357, N'GRPN', N'GRPN', N'Group1', N'Broker', N'J.P. Morgan', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1358, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Neutral', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1359, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Hold', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1360, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$40.00', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1361, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'08/09/2021', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1362, N'GRPN', N'GRPN', N'Group1', N'Broker', N'Wedbush Securities Inc.', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1363, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Neutral', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1364, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Hold', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 5, N'Wedbush Securities Inc.')  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1365, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$33.00', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 5, N'Wedbush Securities Inc. add amount')  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1366, N'GRPN', N'GRPN', N'Group1', N'Last Update', N'05/12/2021', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1367, N'GRPN', N'GRPN', N'Group1', N'Broker', N'Credit Suisse', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1368, N'GRPN', N'GRPN', N'Group1', N'Rating', N'Neutral', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1369, N'GRPN', N'GRPN', N'Group1', N'Equivalent Rating', N'Hold', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1370, N'GRPN', N'GRPN', N'Group1', N'Target Price', N'$38.00', CAST(N'2021-08-26T04:49:27.297' AS DateTime), 6, N'Credit Suisse test')  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1521, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1522, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1523, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1524, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1525, N'GRPN', N'GRPN', N'Group2', N'EV/GP', N'n/a', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 1, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1526, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1527, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', N'7.4x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1528, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', N'53.2x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1529, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1530, N'GRPN', N'GRPN', N'Group2', N'EV/GP', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 2, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1531, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1532, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1533, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1534, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1535, N'GRPN', N'GRPN', N'Group2', N'EV/GP', N'n/a', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 3, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1536, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1537, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', N'9.2x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1538, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1539, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', N'5.0x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1540, N'GRPN', N'GRPN', N'Group2', N'EV/GP', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 4, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1541, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', N'1.4x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1542, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', N'11.2x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1543, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', N'36.7x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1544, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1545, N'GRPN', N'GRPN', N'Group2', N'EV/GP', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 5, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1546, N'GRPN', N'GRPN', N'Group2', N'EV / Revenue', N'0.8x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1547, N'GRPN', N'GRPN', N'Group2', N'EV / EBITDA', N'6.5x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1548, N'GRPN', N'GRPN', N'Group2', N'Pro Forma P/E', N'45.5x', CAST(N'2021-08-26T05:45:42.670' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1549, N'GRPN', N'GRPN', N'Group2', N'P/FCF (Levered)', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 6, NULL)  
GO  
INSERT [dbo].[tblValuationFieldValue] ([ID], [Ticker], [ClientCode], [GroupName], [FieldName], [FieldValue], [InsertedOn], [RecOrder], [Comments]) VALUES (1550, N'GRPN', N'GRPN', N'Group2', N'EV/GP', NULL, CAST(N'2021-08-26T05:45:42.670' AS DateTime), 6, NULL)

HERE is my SQL where i like to add a order by clause on FieldName column of tblValuationSubGroup table.
Broker is a value stored in the column of FieldName which is a column of tblValuationSubGroup table

ALTER Proc USP_GetValuationValue        
(        
 @Ticker VARCHAR(10),        
 @ClientCode VARCHAR(10),        
 @GroupName VARCHAR(10)        
)        
AS        
     DECLARE @SQL nvarchar(MAX),      
             @CRLF nchar(2) = NCHAR(13) + NCHAR(10);      
                  
     SET @SQL = N'SELECT  min(id) ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder,' + 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 * from tblValuationFieldValue' + @CRLF +      
     N'WHERE Ticker = '''+@Ticker+'''  AND ClientCode = '''+@ClientCode+''' AND GroupName='''+@GroupName+''') f' + @CRLF +      
     N'GROUP BY f.ticker,f.ClientCode,f.GroupName,f.RecOrder ';       
                
EXEC sys.sp_executesql @SQL

Please see the last line in dynamic sql.

N'GROUP BY f.ticker,f.ClientCode,f.GroupName,f.RecOrder ';

Here i want to add Order by where i like to mention value like Broker. tblValuationSubGroup has column called FieldName where i store field name. one of the field name is Broker on which i want to do the ascending order sorting.

please guide me how to add order by clause at the end where order will be based on Broker. Broker is valeu for FieldName column of tblValuationSubGroup table.

please help me with sample code.

T.Zacks 3,996 Reputation points

2021-08-28T15:51:54.563+00:00

https://learn.microsoft.com/en-us/answers/questions/257458/how-can-i-insert-dynamic-sql-data-into-temp-table.html

Accepted answer

1 additional answer

Your answer

T.Zacks 3,996 Reputation points

2021-08-28T15:51:54.563+00:00

https://learn.microsoft.com/en-us/answers/questions/257458/how-can-i-insert-dynamic-sql-data-into-temp-table.html

Answer 1

If I understand this correctly:

CREATE OR  ALTER Proc USP_GetValuationValue      
     (      
      @Ticker VARCHAR(10),      
      @ClientCode VARCHAR(10),      
      @GroupName VARCHAR(10)      
     )      
     AS      
          DECLARE @SQL nvarchar(MAX),    
                  @CRLF nchar(2) = NCHAR(13) + NCHAR(10);    

          SET @SQL = N'SELECT  min(ID) ID,f.Ticker,f.ClientCode,f.GroupName,f.RecOrder,' + 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 * from tblValuationFieldValue' + @CRLF +    
          N'WHERE Ticker = @Ticker AND ClientCode = @ClientCode AND GroupName= @GroupName) f' + @CRLF +    
          N'GROUP BY f.Ticker,f.ClientCode,f.GroupName,f.RecOrder ';

          IF charindex('AS [Broker]', @SQL) > 0
             SET @SQL += @CRLF + N'ORDER BY Broker';
PRINT @SQL
     EXEC sys.sp_executesql @SQL, N'@Ticker varchar(10), @ClientCode varchar(10), @GroupName varchar(10)',
                                  @Ticker, @ClientCode, @GroupName

The key here is that in the ORDER BY clause you can use the aliases defined in the SELECT. This is different to WHERE and GROUP BY. This is because a query is logically evaluated in this order: FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY.

I added the test with charindex, in case Broker would not be present among the fields, so that the query explodes in this case.

I also took the opportunity to clean up the dynamic SQL. Inlining parameter values is very bad for many reasons, and I've replaced this with a parameterised statement. As you can see, this makes the code easier to read.

T.Zacks 3,996 Reputation points

2021-08-28T15:58:35.52+00:00

Sir thank you so much for your sample code. i test it and it worked. thanks

Answer 2

i have done this job this way

CREATE Proc USP_GetValuationValue            
(            
 @Ticker VARCHAR(10),            
 @ClientCode VARCHAR(10),            
 @GroupName VARCHAR(10)            
)            
AS            
	DECLARE @SPID VARCHAR(MAX)    
	DECLARE @SQL nvarchar(MAX),          
	@CRLF nchar(2) = NCHAR(13) + NCHAR(10);          
	SELECT @SPID=CAST(@@SPID AS VARCHAR)    
                      
     SET @SQL = N'SELECT * INTO ##Tmp1_'+@SPID+' FROM (SELECT  min(id) ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder,' + 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 * from tblValuationFieldValue' + @CRLF +          
     N'WHERE Ticker = '''+@Ticker+'''  AND ClientCode = '''+@ClientCode+''' AND GroupName='''+@GroupName+''') f' + @CRLF +          
     N'GROUP BY f.ticker,f.ClientCode,f.GroupName,f.RecOrder) X';           
                    
 --EXEC sys.sp_executesql @SQL     
    
      
 EXEC(@SQL)    
 EXEC('select * from ##Tmp1_'+@SPID+' ORDER BY Broker')    
 EXEC('DROP TABLE IF EXISTS ##Tmp1_'+@SPID)

I Put the data into global temporary table whose name will be dynamic with session id and from there i issue select with order by Broker. it worked too.

Thanks a lot Sir @Erland Sommarskog .

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-08-28T17:10:16.77+00:00

That was a lot more complicated.

Essentially, all you need to add to the original query is

` ORDER BY Broker

And if you are dead sure that there always be a Broker value in the source table, you can do this. I did not want to assume this, whence my extra test.

And you should retain the parameterised statement that I gave you and use that in the future. That is also a lot easier to use!
T.Zacks 3,996 Reputation points

2021-08-30T07:11:39.277+00:00

Sir thanks for your guide line.

Share via

SQL SERVER: How to sort by field name when field is dynamic

1 additional answer

Your answer