x

Dynamically Unpivot Columns

I have another task to unpivot a table with dynamic column name, can anyone can demonstrate the approach.

Thank you in advance.

alt text

img1.jpg (49.7 kB)
more ▼

asked Sep 06, 2016 at 03:44 AM in Default

avatar image

genuiForex
61 4

Just wondering whether you've had any luck googling this yet? It's a very common question. Here's just one example. http://dba.stackexchange.com/questions/48393/passing-column-names-dynamically-to-unpivot Once you've had a go, post back with your findings.

Sep 06, 2016 at 04:44 AM GPO

Hi GPO,

Yes, actually i've tried it and some other scripts but getting error saying "The type of column "TRANTYPE" conflicts with the type of other columns specified in the UNPIVOT list"

Attached output result, and sample data in txt file.

Sep 06, 2016 at 05:35 AM genuiForex
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

From the original comment:

There are 2 problems in your script: 1): the list of columns to unpivot includes the TXDATE and TRANTYPE which should never happen (this is what causes the type mismatch); 2): the select list must include all columns, not just the TXDATE and TRANTYPE because if you do that then all you get is 2 columns in your results, but you need 4 (TXDATE, TRANTYPE, data representing the column value and d representing the column name). To make the script work, please modify the part generating the column list to exclude the first 2 columns, and also select all columns coming out of your pivot, not just first 2. Here is the complete script which works as expected:

 declare @colsUnPivot nvarchar(max);
 declare @query nvarchar(max);

 select 
     @colsUnPivot = stuff((
         select ',' + quotename(name) from sys.columns c
         where [object_id] = object_id(N'dbo.AccountingData') and column_id > 2
         for xml path('')), 1, 1, ''
     ),
     @query = 'select u.* from dbo.AccountingData
     unpivot (data for d in (' + @colsUnPivot + ')) u'

 exec sp_executesql @query;

Additional information and solutions to resolve the issue with different data types:

If any of the columns participating in the pivot list differ from others by data type then the script displayed above will not work as written. This is by design, i.e. all columns must be of the same type if you do decide to use the UNPIVOT to unpivot your data. What I mean is that you don't have to use it, there is also a technique to use the cross apply values instead. In order to quickly view whether the data types of any of the columns are of different data type, run the select below:

 select
     f.name ColumnName, f.system_type_name DataType
     from sys.dm_exec_describe_first_result_set(N'select * from dbo.AccountingData', null, 0) f;

While it is possible to get the same information from sys.columns, I find the sys.dm\_exec\_describe\_first\_result\_set to be very useful because it works not only for tables but for any other selects as well. By the way, this table valued function was mentioned and used by @Phil Factor in his excellent article titled Stairway to Exploring Database Metadata Level 2: Using the Dynamic Online Catalog to Solve Problems published this morning on the sqlservercentral.com website.

Look at the DataType column values from the third row down to observe whether there are any differences.

Here are both solutions:

Solution with UNPIVOT which requires all columns to be of the same data type:

 declare @colsUnPivot nvarchar(max);
 declare @columnList nvarchar(max);
 declare @query nvarchar(max);
 
 select 
      @colsUnPivot = stuff((
          select ',' + quotename(name) from sys.columns c
          where [object_id] = object_id(N'dbo.AccountingData') and column_id > 2
          for xml path('')), 1, 1, ''
      ),
       @columnList = stuff((
          select 
             case when column_id <=2 then ',' + quotename(name)
             else ',cast(' + quotename(name) + ' as decimal(10, 2)) ' + quotename(name)
         end  
          from sys.columns c
          where [object_id] = object_id(N'dbo.AccountingData')
          for xml path('')), 1, 1, ''
      ),
      @query = 'select u.* 
         from (
             select ' + @columnList + ' from dbo.AccountingData
         ) src
      unpivot (data for d in (' + @colsUnPivot + ')) u';

 exec sp_executesql @query;

The extra variable is used to populate the column list. It takes care of casting each column participating in unpivot to the same data type, decimal(10, 2) in this case.

Below is the solution with CROSS APPLY VALUES which does not need the columns participating in unpivot to be of the same data type, it will work regardless of whether the data types are same or not. The script looks a bit simpler, does not need separate variables for column list and list for unpivot, and performance-wise it is probably close to the the one with UNPIVOT if not better. The idea is to select the rows from the original table cross applying with the pair of column value, column name combinations from the same table:

 declare @columnList nvarchar(max);
 declare @query nvarchar(max);
 
 select 
     @columnList = stuff((
     select
         ',(a.' + quotename(name) + ', ''' + name + ''')'
         from sys.columns c
          where [object_id] = object_id(N'dbo.AccountingData') and column_id > 2
          for xml path('')), 1, 1, ''
      ),
      @query = 'select
         a.TXDATE, a.TranType, cap.*
         from dbo.AccountingData a cross apply (
         values ' + @columnList + '
         ) cap(data, d)
         '
 exec sp_executesql @query;

As per @GPO suggestion, it makes sense to select @query instead of or before executing to see how the generated dynamic script looks like.

Hope this helps.

Oleg

more ▼

answered Sep 07, 2016 at 02:48 PM

avatar image

Oleg
18.9k 3 7 28

@Oleg Thank you, i got it correctly.

Sep 08, 2016 at 12:36 AM genuiForex
(comments are locked)
10|1200 characters needed characters left
img2.jpg (45.7 kB)
sampledata.txt (19.7 kB)
more ▼

answered Sep 06, 2016 at 05:38 AM

avatar image

genuiForex
61 4

@Oleg Appreciate your response, after running the script im getting this error

The type of column "0104" conflicts with the type of other columns specified in the UNPIVOT list.

Sep 07, 2016 at 02:20 AM genuiForex

Change exec sp_executesql @query; to PRINT sp_executesql @query; ...and post the code back here so we can see what SQL is being generated.

Sep 07, 2016 at 07:24 AM GPO

@GPO here's the output

select u.* from dbo.AccountingData unpivot (data for d in ([0002],[0011],[0013],[0012],[0004],[0005],[0008],[0006],[0009],[0007],[0014],[0017],[0021],[0023],[0025],[0030],[0032],[0038],[0039],[0037],[0044],[0033],[0048],[0036],[0056],[0045],[0049],[0057],[0059],[0060],[0062],[0069],[0065],[0066],[0063],[0071],[0075],[0073],[0077],[0080],[0085],[0088],[0086],[0091],[0055],[0001],[0010],[0018],[0016],[0019],[0026],[0031],[0040],[0042],[0041],[0043],[0047],[0050],[0051],[0015],[0054],[0064],[0046],[0068],[0072],[0070],[0076],[0078],[0081],[0079],[0084],[0082],[0092],[0093],[0098],[0095],[0101],[0102],[0003],[0020],[0029],[0061],[0067],[0083],[0090],[0106],[0104],[0107],[0108],[0110],[0109],[0105],[0113],[0112],[0103],[0024],[0094],[0099],[0096],[0034],[0087],[0100],[0028],[0053],[0035],[0022],[0052],[0097],[0027])) u

Sep 07, 2016 at 09:10 AM genuiForex

@genuiForex The problem is that the columns to be pivoted are of different data types. It is possible that majority of the columns (the ones subjected to unpivot) are decimal while some of them are int. All columns should be of the same type or else they need to be cast as some suitable type. Let me assume that all the columns named in 000# format are meant to be decimal, say, decimal(10, 2). I can convert my comment to answer (so I have more space to work with) and post the complete solution which will work based on this assumption about the data types. Give me about 30 minutes to do it.

Sep 07, 2016 at 01:25 PM Oleg

@genuiForex I added the answer with 2 solutions, both work as expected, so please use any of the 2. The answer went into moderation for approval, I hope it will become available later today. Sadly, my answers are often routed to moderation because I tend to like making the short stories long.

Sep 07, 2016 at 02:43 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x114
x63
x58
x28

asked: Sep 06, 2016 at 03:44 AM

Seen: 182 times

Last Updated: Sep 08, 2016 at 12:36 AM

Copyright 2017 Redgate Software. Privacy Policy