question

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

SQL Server How to Transpose Data

have a table where I store column name and its value ticker & client wise. i need to convert rows to column.

This is my table with data. screen shot attached.
122038-wqo.png

Here i tried this sql which is throwing error for duplicate values in field name. i got this code from this post https://stackoverflow.com/a/15745076/9359783

But their code is not working for my scenario. please guide me what i need to alter in code.

 DECLARE @cols AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX)
    
 select @cols = STUFF((SELECT ',' + QUOTENAME(FieldName) 
                     from DynamicForm WHERE Ticker='X' AND ClientCode='Z'
                     group by FieldName, id,Ticker,ClientCode
                     order by id
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,1,'')
    
    
 set @query = N'SELECT ' + @cols + N' from 
              (
                 select value, FieldName
                 from DynamicForm WHERE Ticker=''X'' AND ClientCode=''Z''
             ) x
             pivot 
             (
                 max(value)
                 for FieldName in (' + @cols + N')
             ) p '
    
 exec sp_executesql @query;

OUTPUT would be look like

 +-------------+----------------------+-----------------+
 | Last Update |        Broker        |     Analyst     |
 +-------------+----------------------+-----------------+
 | 7/6/2021    |    JMP Securities    | David M Scharf  |
 | 4/28/2021   |  Argus Research Corp | David E Coleman |
 +-------------+----------------------+-----------------+  

See here two records is coming and JMP Securities is getting first records because its orderid is 1. so data should be displayed as per Ticker & client code wise and orderId wise data should be order.

Here is script which help you to get data.

 CREATE TABLE [dbo].[DynamicForm](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [FieldName] [varchar](100) NULL,
     [Value] [varchar](100) NULL,
     [Ticker] [varchar](10) NULL,
     [ClientCode] [varchar](10) NULL,
     [Order] [int] NULL
 ) ON [PRIMARY]
 GO
 SET IDENTITY_INSERT [dbo].[DynamicForm] ON 
 GO
 INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (1, N'Last Update
 ', N'4/28/2021
 ', N'X', N'Z', 1)
 GO
 INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (2, N'Broker
 ', N'Argus Research Corp
 ', N'X', N'Z', 1)
 GO
 INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (3, N'Analyst 
 ', N'David E Coleman
 ', N'X', N'Z', 1)
 GO
 INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (4, N'Last Update
 ', N'7/6/2021
 ', N'X', N'Z', 2)
 GO
 INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (5, N'Broker
 ', N'JMP Securities
 ', N'X', N'Z', 2)
 GO
 INSERT [dbo].[DynamicForm] ([ID], [FieldName], [Value], [Ticker], [ClientCode], [Order]) VALUES (6, N'Analyst 
 ', N'David M Scharf
 ', N'X', N'Z', 2)
 GO
 SET IDENTITY_INSERT [dbo].[DynamicForm] OFF
 GO






sql-server-transact-sql
wqo.png (14.9 KiB)
· 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.

You'll need get a distinct list to form your columns and then you'll need to have "Order" as a pivot point otherwise you'll only get the max of each column

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

Hi @TZacks-2728,

I tried with different methods but failed. I have to add condition 'order=1' and remove the group part in stuff.

Please refer below updated one:

 DECLARE @cols AS NVARCHAR(MAX),
  @cols1 AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)
    
  select @cols = STUFF((SELECT  ',' + QUOTENAME(FieldName) 
                      from DynamicForm 
   WHERE Ticker='X' AND ClientCode='Z' and [order]=1
   order by id
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
     
 select @cols1 = STUFF((SELECT ',max(' + QUOTENAME(FieldName) +') over (partition by Ticker,ClientCode,[order]) '+ QUOTENAME(FieldName) 
                      from DynamicForm 
   WHERE Ticker='X' AND ClientCode='Z' and [order]=1
   order by id
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')   
        
  set @query = N'SELECT distinct ' + @cols1 + N' from 
               (
                  select *
                  from DynamicForm WHERE Ticker=''X'' AND ClientCode=''Z''
              ) x
              pivot 
              (
                  max(value)
                  for FieldName in (' + @cols + N')
              ) p '
    
  exec sp_executesql @query;

Output:
122225-output.png

You could check the difference between the two stuffs by executing 'print @query'.

If we did not add the second stuff using max function , we would get the result like below.

122269-before.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 (3.4 KiB)
before.png (8.1 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.

Thank you so much. i found another working version sharing here.

 DECLARE @SQL nvarchar(MAX),
         @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
    
 SET @SQL = N'SELECT ' + STUFF((SELECT N',' + @CRLF + N'       ' +
                                       N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN Value END) AS ' + QUOTENAME(FieldName)
                                FROM dbo.DynamicForm
                                GROUP BY FieldName
                                ORDER BY MIN(ID)
                                FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +
            N'FROM dbo.DynamicForm' + @CRLF +
            N'WHERE Ticker = @Ticker' + @CRLF +
            N'  AND ClientCode = @ClientCode' + @CRLF + 
            N'GROUP BY [Order]' + @CRLF + --ORDER is a reserved keyword, and should not be used for object names
            N'ORDER BY [Order];'; --ORDER is a reserved keyword, and should not be used for object names
    
 DECLARE @Ticker varchar(10) = 'X',
         @ClientCode varchar(10) = 'Z';
    
 --Print @SQL; -- Your best friend
 EXEC sys.sp_executesql @SQL, N'@Ticker varchar(10), @ClientCode varchar(10)', @Ticker, @ClientCode;

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered

You may not need a PIVOT. Try this:

 ;WITH CTE_Last_Update AS (
     SELECT [FieldName], [Value], [Order]
     FROM [dbo].[DynamicForm]
     WHERE [FieldName] = 'Last Update'
 ),
 CTE_Last_Broker AS (
     SELECT [FieldName], [Value], [Order]
     FROM [dbo].[DynamicForm]
     WHERE [FieldName] = 'Broker'
 ),
 CTE_Last_Analyst AS (
     SELECT [FieldName], [Value], [Order]
     FROM [dbo].[DynamicForm]
     WHERE [FieldName] = 'Analyst'
 )
    
 SELECT c1.[Value] AS [Last Update], c2.[Value] AS [Broker], c3.[Value] AS [Analyst]
 FROM CTE_Last_Update AS c1
 INNER JOIN CTE_Last_Broker AS c2 ON c1.[Order] = c2.[Order]
 INNER JOIN CTE_Last_Analyst AS c3 ON c1.[Order] = c3.[Order];
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 edited

Hi @TZacks-2728,

I found that there was a duplicate post in https://docs.microsoft.com/en-us/answers/questions/508156/sql-server-convert-rows-to-columns.html.
So I combined them so that we could discuss more intensively here.

Please refer below and check whether it is working:

 DECLARE @cols AS NVARCHAR(MAX),
 @cols1 AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX)
        
 SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FieldName) 
         FROM dbo.DynamicForm
         WHERE Ticker='X'
         AND ClientCode='Z'
         FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)') ,1,1,'');
     
 SELECT @cols1 = STUFF((SELECT DISTINCT ',max(' + QUOTENAME(FieldName) +') over (partition by Ticker,ClientCode,[order]) '+ QUOTENAME(FieldName) 
         FROM dbo.DynamicForm
         WHERE Ticker='X'
         AND ClientCode='Z'
         FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)') ,1,1,'');
    
 set @query = N'SELECT distinct ' + @cols1 + N' from 
 (
     select *
     from DynamicForm WHERE Ticker=''X'' AND ClientCode=''Z''
 ) x
 pivot 
 (
     max(value)
     for FieldName in (' + @cols + N')
 ) p '
    
 exec sp_executesql @query;

Output:

122086-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 (3.4 KiB)
· 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.

Thank you so much for the answer.
why two stuff has been used not clear.
what this line is doing not clear STUFF((SELECT DISTINCT ',max(' + QUOTENAME(FieldName) +') over (partition by Ticker,ClientCode,[order]) '+ QUOTENAME(FieldName)

please help me to understand how your code is working. still column order not correct. please see my post there i mention what would be column order. Order column should be used to arrange records and ID column should be used to order which field should come first. thanks

0 Votes 0 ·