question

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

SQL Server: Getting NULL value when Transpose rows to columns

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.

aiSyi.png

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

123652-output.png

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

please share a right script. Thanks




sql-server-transact-sql
output.png (8.0 KiB)
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.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered TZacks-2728 commented

Hi @TZacks-2728,

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.

· 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.

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

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 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.

Please have a look at this post https://docs.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

0 Votes 0 ·

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.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered TZacks-2728 commented

Hi @TZacks-2728,

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:

123736-output.png

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.


output.png (7.0 KiB)
· 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.

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.

0 Votes 0 ·