question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked TZacks-2728 commented

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

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.














sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered TZacks-2728 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
TZacks-2728 avatar image
0 Votes"
TZacks-2728 answered TZacks-2728 commented

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 @ErlandSommarskog .

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

1 Vote 1 ·

Sir thanks for your guide line.

0 Votes 0 ·