X行数为X列数[重复]

This question already has an answer here:

这个问题在这里已有答案:

• Simple way to transpose columns and rows in Sql? 8 answers

在Sql中转换列和行的简单方法? 8个答案

I need your help again for the following. Please note that I am using SQL SERVER 2008. I have a table (below) where we got Number of jobs done from the "Pickup" to the "Dropoff". The requirements are (output format mentioned below). 1) Get all the unique postcodes regardless of them being dropoffs or pickups. This will give us all the postcodes. 2) Present the count of jobs done from PICKUP to the corresponding DROPOFF. For example, we pick passengers up from SE18 postcode twice and took them to SE18 once and SE19 once. It will be cleared from the REQUIRED OUTPUT table.

以下我需要你的帮助。请注意我正在使用SQL SERVER 2008.我有一个表(下面)我们得到了从“Pickup”到“Dropoff”完成的工作数。要求是(下面提到的输出格式)。 1)获取所有独特的邮政编码,无论它们是下降或拾取。这将给我们所有的邮政编码。 2)将从PICKUP完成的作业计数提供给相应的DROPOFF。例如,我们从SE18邮政编码中挑选乘客两次,并将其带到SE18一次,SE19一次。它将从REQUIRED OUTPUT表中清除。

TABLE:
JobID        Pickup       Dropoff
====================================
1            SE18         SE18
2            SE18         SE19
3            SE2          SE18
4            SE28         SE2
5            AL1          SE7
6            BR1          SE10
7            NW1          TW16
8            AL1          SE18
9            BR6          AL1
10           E6           BR1
.            .            .
.            .            .
.            .            .

The require output is as the following:

require输出如下:

REQUIRED OUTPUT

     SE18   SE2   SE28   AL1   BR1   NW1   BR6   E6   SE19  SE7  SE10  TW16 ..
     =========================================================================
SE18   1     -      -     -     -     -     -     -     1    0    0     0
SE2    1     -      -     -     -     -     -     -     -    -    -     -         
SE28   -     1      -     -     -     -     -     -     -    -    -     -          
AL1    1     -      -     -     -     -     -     -     -    1    -     -       
BR1    -     -      -     -     -     -     -     -     -    -    1     -        
NW1    -     -      -     -     -     -     -     -     -    -    -     1      
BR6    -     -      -     1     -     -     -     -     -    -    -     -       
E6     -     -      -     -     1     -     -     -     -    -    -     -       
SE19   -     -      -     -     -     -     -     -     -    -    -     -        
SE7    -     -      -     -     -     -     -     -     -    -    -     -       
SE10   -     -      -     -     -     -     -     -     -    -    -     -      
TW16   -     -      -     -     -     -     -     -     -    -    -     -      
.
.
.

Many thanks in advance. Kind regards

提前谢谢了。亲切的问候

1 个解决方案

#1

It sounds like this is what you are looking for. If you want to hard-code the values, your query would look like this:

听起来这就是你要找的东西。如果要对值进行硬编码,则查询将如下所示:

select *
from
(
  select pickup, 
    dropoff,
    dropoff d
  from yourtable
) x
pivot
(
  count(d) 
  for dropoff in ([SE18], [SE2], [SE28], [Al1], [BR1],
              [NW1], [BR6], [E6], [SE19], [SE7],
              [SE10], [TW16])
) p

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

If you have an unknown number of values, then you can use dynamic sql to pivot the values:

如果您具有未知数量的值,则可以使用动态sql来旋转值:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Dropoff) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT pickup, ' + @cols + ' from 
             (
                select pickup, dropoff, 
                  dropoff as countdropoff
                from yourtable
            ) x
            pivot 
            (
                count(countdropoff)
                for dropoff in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo