Unpivot Period Table

Question

In a database, it contains a Table

  ID   Name   MTH201401UN   MTH201401LC   MTH201312UN  MTH201312LC  MTH201311UN  MTH201311LC

   1    A      200            300          400           500            600        700

Want a unpivoted table like below:

  ID   Name   Period     UN  LC

  1     A   MTH201401  200  300 
  1     A   MTH201312  400  500 
  1     A   MTH201311  600  700 

Main thing the period is not fixed but MTH part is fixed how can I do this which can take the periods without mention it particullarly?

Answer 1

This may help you: This assumes that LC and UN are fixed

SAMPLE DATA

create table temp(
    ID int,
    Name varchar(10),
    MTH201401UN int,
    MTH201401LC int,
    MTH201312UN int,
    MTH201312LC int,
    MTH201311UN int,
    MTH201311LC int
)
insert into temp
select 1, 'A', 200, 300, 400, 500, 600, 700

SOLUTION USING DYNAMIC SQL

declare @sql varchar(4000) = ''

select @sql = @sql + '
select
    Id
    ,Name' + '
    ,''' +  column_name + ''' as Period' + '
    ,' +  column_name + 'LC as LC' + '
    ,' +  column_name + 'UN as UN' + char(10) +
'from temp
' + 
case 
    when rn < cc then 'union all'
    else ''
end
from (
    select
        + column_name as column_name,
        row_number() over(order by column_name) as rn,
        count(*) over(partition by (select null)) as cc
    from (
        select distinct 
            substring(column_name, 0, len(column_name) - 1) as column_name      
        from information_schema.columns
        where 
            table_name = 'temp'
            and column_name not in('ID','Name')
    )a
)t

print @sql

exec(@sql)

drop table temp