Dynamic SQL query to transpose unknown number of columns to rows using unpivot

Question

I have a table that has an unknown number of tasks like so:

+----------+--------+--------+--------+--------+--------+
| CourseID | Task 1 | Task 2 | Task 3 | Task 4 | Task 5 |
+----------+--------+--------+--------+--------+--------+
| EN01     |     15 |     20 |     15 |     25 |     30 |
+----------+--------+--------+--------+--------+--------+

Sometimes there are 5 tasks, sometimes there are more. How do I write a dynamic transpose query using pivot to get this result without having to speficy the column headers specifically other than column header is like 'Task%'

+----------+-------------+------------+
| CourseID | Task Number | Task Total |
+----------+-------------+------------+
| EN01     | Task 1      |         15 |
| EN01     | Task 2      |         20 |
| EN01     | Task 3      |         15 |
| EN01     | Task 4      |         25 |
| EN01     | Task 5      |         30 |
+----------+-------------+------------+

EDIT: Basically I need the reverse of this: Efficiently convert rows to columns in sql server

I can do it manually:

-- Unpivot the table.
 SELECT [Class], TaskNumber, TaskTotal
FROM
   (SELECT [Class], [Task 1], [Task 2], [Task 3], [Task 4]
   FROM [Modules].[dbo].[2018-12ah] where [Given] like '%J:%') p
UNPIVOT
   (TaskTotal FOR TaskNumber IN
      ([Task 1], [Task 2], [Task 3], [Task 4])
)AS Unpivot;
GO

Next step that i'm not sure how to do is dynamically building in tasks 1 to Z so I don't have to specify them in the query.

Thank you

This is very bad DB Design. If possible, Please convert your design to the later one. — Ankit Bajpai, Sep 03 '18 at 06:05
https://dba.stackexchange.com/questions/158461/unpivot-columns-based-on-select https://www.mssqltips.com/sqlservertip/3002/use-sql-servers-unpivot-operator-to-dynamically-normalize-output/ — Paul Maxwell, Sep 03 '18 at 06:06
@AnkitBajpai - I understand and I hear you. It is our plan to retire this old system which is 10 years old and do it properly however I still need to support it for now which is unfortunate. — SSS, Sep 03 '18 at 06:16
try either of the links shown above, either should achieve what you need — Paul Maxwell, Sep 03 '18 at 06:20
Sql Server requires the actual table to have a **fixed** number of columns. Write the unpivot query to run as if **all** of them are always populated, and then add a `where` condition to filter out `null`s. — Joel Coehoorn, Sep 03 '18 at 15:45

Andrea · Answer 1 · 2018-09-03T15:39:30.053

You can use the information in INFORMATION_SCHEMA with dynamic TSQL, but be aware of security implications (i.e. SQL injection):

create table  [dbo].[Test]  ([CourseID] nvarchar(50), [Task 1] int, [Task 2] int, [Task 3] int, [Task 4] int, [Task 5] int) 

insert into [dbo].[Test] select 'EN01', 15,20,15,25,30

declare @sql nvarchar(max) = ''
declare @cols nvarchar(max) = ''

select @cols = @cols +','+ QUOTENAME(COLUMN_NAME)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo' and TABLE_NAME='test' and COLUMN_NAME like 'Task%' 
order by ORDINAL_POSITION

set @cols = substring(@cols, 2, LEN(@cols))

set @sql = @sql + ' select u.[CourseID], u.[TaskNumber], u.[TaskTotal] '
set @sql = @sql + ' from [dbo].[Test] s '
set @sql = @sql + ' unpivot '
set @sql = @sql + ' ( '
set @sql = @sql + '     [TaskTotal] '
set @sql = @sql + '     for [TaskNumber] in ('+ @cols + ') '
set @sql = @sql + ' ) u;'

execute(@sql)

Results with 5 tasks:

If you change table structure adding a new task column:

DROP TABLE [dbo].[Test]
create table  [dbo].[Test]  ([CourseID] nvarchar(50), [Task 1] int, [Task 2] int, [Task 3] int, [Task 4] int, [Task 5] int, [Task 6] int) 
insert into [dbo].[Test] select 'EN01', 15,20,15,25,30,1000

the same query will return this resultset:

Here is an alternative solution if you prefer the CROSS APPLY approach to unpivoting:

create table  [dbo].[Test]  ([CourseID] nvarchar(50), [Task 1] int, [Task 2] int, [Task 3] int, [Task 4] int, [Task 5] int) 

insert into [dbo].[Test] select 'EN01', 15,20,15,25,30 

declare @sql nvarchar(max) = ''
declare @values nvarchar(max) = ''

select @values = @values +',(''' + COLUMN_NAME + ''','+ QUOTENAME(COLUMN_NAME) + ')'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo' and TABLE_NAME='test' and COLUMN_NAME like 'Task%' 
order by ORDINAL_POSITION

set @values = substring(@values, 2, LEN(@values))

set @sql = @sql + ' Select [CourseID], B.* '
set @sql = @sql + ' From [dbo].[Test] '
set @sql = @sql + ' Cross Apply (values '
set @sql = @sql + @values
set @sql = @sql + ' ) B(TaskNumber, TaskTotal) '

execute(@sql)

Thank you for the detailed information. I was able to make similar progress querying the information_schema table to find the columns. I then went to add the query to a view and realised you cannot have variables in a view. So i'm back to the drawing board on how to represent this in a view. What would you suggest? — SSS, Sep 04 '18 at 21:53

Dynamic SQL query to transpose unknown number of columns to rows using unpivot

1 Answers1