TSQL Pivot - Collapsing Columns to Rows

Question

Using PIVOT/UNPIVOT I was able to get from here...

Period       Store1         Store2
--------------------------------------
Jan15        123            456
Feb15        789            101
Mar15        112            131
Apr15        415            161

...to here, dynamically (no matter how many stores my current script successfully PIVOTs to the below set...

Store        Jan15          Feb15          Mar15          Apr15
---------------------------------------------------------------
Store1       123            789            112            415
Store2       456            101            131            161

using this script:

DECLARE @colsUnpivot AS NVARCHAR(MAX), 
         @colsPivot as  NVARCHAR(MAX), @query  AS NVARCHAR(MAX)        

/* @colsUnpivot gets list of Stores */
select @colsUnpivot = COALESCE(@colsUnpivot +', ', '') + QUOTENAME(A.name)
from (select name 
        from sys.columns 
       where object_id = object_id('mytable') and name <> 'Period') A

/* @colsPivot gets list of Periods */
select @colsPivot = COALESCE(@colsPivot +', ', '') + QUOTENAME(B.Period)
  from (select distinct period 
          from StoreMetrics) B

set @query 
  = 'select store, '+@colsPivot+'
      from
      (
        select period, store, value
        from mytable
        unpivot
        (
          value for store in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        max(value)
        for period in ('+@colsPivot+')
      ) piv'

exec(@query)

...however, this is the actual expected result set:

Store        Period         Value
--------------------------------------
Store1       Jan15          123            
Store1       Feb15          789
Store1       Mar15          112
Store1       Apr15          415
Store2       Jan15          456
Store2       Feb15          101
Store2       Mar15          131
Store2       Apr15          161

Either from the original dataset or my first pivot result, how can I dynamically collapse (has to be dynamic since period entries will keep changing) all columns into row entries for each combination of element/period?

Answer 1

One possible solution, without pivot/unpivot:

create table #tab(
    Store  nvarchar(50),
    period nvarchar(50),
    value int
)

declare @ColumnName table(columnName nvarchar(50))

insert into @ColumnName (columnName)
select  A.name
from    (
            select  name
            from    sys.columns
            where   object_id = object_id('mytable')
                    and name <> 'Period'
        ) A

declare @Column nvarchar(50),
        @sql    nvarchar(4000)

while (select  count(*) from    @ColumnName) > 0
begin
    set @Column = (select  top 1 columnName from @ColumnName);
    set @sql = '
           insert into #tab 
           select ''' + @Column + ''',Period , sum(' + @Column + ')
           from mytable
           group by ' + @Column + ',Period'


    exec(@sql);

    delete from @ColumnName
    where   columnName = @Column
end

select  *
from    #tab

drop table #tab