question

genuiForex avatar image
genuiForex asked

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][1] [1]: /storage/temp/3658-img1.jpg
pivotdynamic-sqldynamiccolumns
img1.jpg (48.6 KiB)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
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.
0 Likes 0 ·
genuiForex avatar image genuiForex commented ·
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.
0 Likes 0 ·
Oleg avatar image
Oleg answered
**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][1] 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][2] 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 [1]: https://msdn.microsoft.com/en-us/library/ff878258.aspx [2]: http://www.sqlservercentral.com/articles/Stairway+Series/145317/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

genuiForex avatar image genuiForex commented ·
@Oleg Thank you, i got it correctly.
0 Likes 0 ·
genuiForex avatar image
genuiForex answered
![alt text][1] [link text][2] [1]: /storage/temp/3662-img2.jpg [2]: /storage/temp/3661-sampledata.txt

img2.jpg (44.7 KiB)
sampledata.txt (19.2 KiB)
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@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.
1 Like 1 ·
genuiForex avatar image genuiForex commented ·
@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.
0 Likes 0 ·
GPO avatar image GPO commented ·
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.
0 Likes 0 ·
genuiForex avatar image genuiForex commented ·
@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
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.