SQL Server: Getting NULL value when Transpose rows to columns

Question

SQL Server: Getting NULL value when Transpose rows to columns

T.Zacks 3,996

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.

Field Order will be based on FieldOrder column of tblValuationSubGroup table.
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

No records is coming for Barclays Capital rathet only one records is coming which is Credit Suisse

please share a right script. Thanks

Accepted answer

2 additional answers

Your answer

Answer 1

Hi @T.Zacks ,

Actually I have one concern about the result of ID in your expected output.

Per my understanding, the ID could be 1 and 5 according to your sample data of tblValuationFieldValue table.

Please refer below:

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  min(id) 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 sys.sp_executesql @SQL

Output:

ID	ticker	ClientCode	GroupName	Last Update	Broker  
1	G	SNFLD	Group1	2021-08-14	Barclays Capital  
5	G	SNFLD	Group1	2021-08-15	Jeffry

If your expected output of ID still should be 1 and 2, please also refer below:

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  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 sys.sp_executesql @SQL

Output:

ID	ticker	ClientCode	GroupName	Last Update	Broker  
1	G	SNFLD	Group1	2021-08-14	Barclays Capital  
2	G	SNFLD	Group1	2021-08-15	Jeffry

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.

T.Zacks 3,996 Reputation points

2021-08-16T15:30:17.667+00:00

Madam when i run your script on actual data then got unexpected result. i paste actual sample data. please see my EDIT 1 section in my post.

waiting for your help. thanks

Answer 2

Hi @T.Zacks ,

It is recommended for you to post a new question for your actual data since my query was working with your original sample data and also to avoid cutting our original discussion chain.

Please refer below with your actual data and check whether it is working.

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 ROW_NUMBER() over (partition by ticker,ClientCode,GroupName,FieldName 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)

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.

T.Zacks 3,996 Reputation points

2021-08-17T05:55:32.577+00:00

I test the script with actual data and it is perfectly working. the change i found in this line.

N'FROM (select (ROW_NUMBER() over (partition by ticker,ClientCode,GroupName,FieldName order by id)) groupID,* from tblValuationFieldValue' + @CRLF +

FieldName included for partition by

Thanks a lot.
T.Zacks 3,996 Reputation points

2021-08-27T16:08:53.3+00:00

Madam please have a look at this post https://learn.microsoft.com/en-us/answers/questions/530954/sql-server-how-to-sort-by-field-name-when-field-is.html

i stuck for a issue. please help me. thanks

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

This seems more or less impossible to me. How would you know that
Last Update = 2021-08-14 Broker= Barclays Capital go together on one hand, and Last Update = 2021-08-15 and Broker = Jeffry on the other? Yeah, the InsertedOn column gives some idea, but it seems precarious to build logic on a timestamp.

Unless your actual data has an identifier that holds the entries together, I don't think this is doable.

And maybe even better, LastUpdate and Broker should be columns in a properly designed table. Then it may even start to appear simple.

T.Zacks 3,996 Reputation points

2021-08-14T17:39:48.727+00:00

Please have a look at this post https://learn.microsoft.com/en-us/answers/questions/508441/how-to-transpose-data.html

where @MelissaMa-MSFT answer but there was a single table where field name and field value was stored there. in this case i have two table. one table tblValuationSubGroup has field name and one table tblValuationFieldValue has field name and value too.

can't we enhance the script composed by @MelissaMa-MSFT ?

please share your suggestion once again. thanks
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-08-14T18:08:02.12+00:00

Again, there is nothing in the data which tells us which Last_updated that goes with which Broker. That's a major roadblock, which makes the question about the second table moot.

If Melissa wants to give this whirl despite my objections when she is back at work on Monday, I can't stop her. But from where I sit, this is not a solvable problem as you have presented the problem.

Share via

SQL Server: Getting NULL value when Transpose rows to columns

2 additional answers

Your answer