Q:

Show same columns with different values (and empty values)

i'm quite new to SQL and i'm having this issues.
CREATE TABLE #TempTable
(WeekNr int,
Name varchar(30),
Value int)

INSERT INTO #TempTable VALUES (21,'John',100)
INSERT INTO #TempTable VALUES (21,'Michael',133)
INSERT INTO #TempTable VALUES (21,'Tony',50)
INSERT INTO #TempTable VALUES (22,'John',80)
INSERT INTO #TempTable VALUES (23,'Michael',188)
INSERT INTO #TempTable VALUES (23,'Tony',230)

Table shows this way:
  WeekNr         Name        Value
  21             John        100
  21             Michael     133
  21             Tony        50
  22             John        80
  23             Michael     188
  23             Tony        230

I need to arrange data in this way:
  Name           Vale          Name         Value        Name        Value
  John           100           John          80          Michael     188
  Michael        133                                     Tony        230
  Tony           50

where the first two columns refers to WeekNr being 21, the second two to WeekNr being 22, and then WeekNr being 23.
It would be better if Null values will be included so without skipping any WeekNr. For example if i do something like
  DELETE FROM #TempTable WHERE WeekNr = 22

It should appear something like:
  Name           Vale          Name         Value        Name        Value
  John           100                                     Michael     188
  Michael        133                                     Tony        230
  Tony           50

so it's easy to export data to Excel.
Do i need to use PIVOT or UNPIVOT?

A:

Try this.
SELECT Max([21]) NAME,Max([id21]) Value,Max([22]) Name,Max([id22]) Value,Max([23]) Name,Max([id23]) Value
FROM   (SELECT Row_number()OVER(partition BY WeekNr ORDER BY NAME) rn,
               NAME,value,WeekNr,
               'id' + CONVERT(VARCHAR, WeekNr)      AS weeks
        FROM   #TempTable) a
       PIVOT (Max(NAME)
             FOR WeekNr IN ([21],[22],[23])) piv 
       PIVOT (max(value) 
             FOR weeks IN ([id21],[id22],[id23])) piv1
GROUP BY rn

If you want the code to work dynamically then try this.
DECLARE @cols     VARCHAR(max)='',
        @cols1    VARCHAR(max)='',
        @aggcols  VARCHAR(max)='',
        @aggcols1 VARCHAR(max)='',
        @sql      NVARCHAR(max)

SELECT @cols += ',[' + CONVERT(VARCHAR(30), weeknr)+']',
       @cols1 += ',[id' + CONVERT(VARCHAR(30), weeknr)+']',
       @aggcols += ',max([' + CONVERT(VARCHAR(30), weeknr)+ ']) Name',
       @aggcols1 += ',max([id' + CONVERT(VARCHAR(30), weeknr)+ ']) Value'
FROM   (SELECT DISTINCT WeekNr
        FROM   #TempTable) A 

select @cols= RIGHT(@cols,len(@cols)-1)
select @cols1= RIGHT(@cols1,len(@cols1)-1)

select @aggcols= RIGHT(@aggcols,len(@aggcols)-1)
select @aggcols1= RIGHT(@aggcols1,len(@aggcols1)-1)

set @sql ='SELECT '+@aggcols+','+@aggcols1+'
FROM   (SELECT Row_number()OVER(partition BY WeekNr ORDER BY NAME) rn,
               NAME,value,WeekNr,
               ''id'' + CONVERT(VARCHAR, WeekNr)      AS weeks
        FROM   #TempTable) a
       PIVOT (Max(NAME)
             FOR WeekNr IN ('+@cols+')) piv 
       PIVOT (max(value) 
             FOR weeks IN ('+@cols1+')) piv1
GROUP BY rn'

exec sp_executesql @sql

