显示具有不同值（和空值）的相同列

i'm quite new to SQL and i'm having this issues.

我对SQL很新，我遇到了这个问题。

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.

前两列引用WeekNr为21，第二列为WeekNr为22，然后WeekNr为23。

It would be better if Null values will be included so without skipping any WeekNr. For example if i do something like

如果将Null值包含在内而不跳过任何WeekNr会更好。例如，如果我做的事情

  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?

所以很容易将数据导出到Excel。我需要使用PIVOT还是UNPIVOT？

3 个解决方案

#1

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

#2

This is not something you are supposed to do with SQL. Don't do it with SQL, it would be complex to develop and maintain.

这不是你应该用SQL做的事情。不要使用SQL，开发和维护会很复杂。

SQL helps you retrieve your data, you are able to do some kind of presentation stuff, but as soon as it becomes too complex, you know you are having the wrong approach. You should do this kind of presentation in your front end application

SQL可以帮助您检索数据，您可以做某种演示文稿，但是一旦它变得太复杂，您就会知道您的方法是错误的。您应该在前端应用程序中执行此类演示

#3

I'm not an expert in SQL Server either, as a matter a fact, I'm fairly new to it. I don't think this is possible in any easy way. The best thing I can think of is by running three different queries, where you would have a Where condition stating something like this:

我也不是SQL Server的专家，事实上，我对它很新。我认为这不可能以任何简单的方式进行。我能想到的最好的事情是运行三个不同的查询，在这里你会有一个Where条件，说明这样的事情：

SELECT Name, Value
FROM #TempTable
WHERE WeekNr = 21
GO

SELECT Name, Value
FROM #TempTable
WHERE WeekNr = 22
GO

SELECT Name, Value
FROM #TempTable
WHERE WeekNr = 23
GO

Then when you would run the whole query, it would give you three windows of data, but not organized exactly the way you want it. The problem is that if you have hundreds or thousands of those WeekNr values, this becomes almost impossible to implement and run that way.

然后，当您运行整个查询时，它会为您提供三个数据窗口，但不会按照您希望的方式进行组织。问题是，如果你有数百或数千个WeekNr值，这几乎不可能实现并以这种方式运行。

#1