X number of Rows into X number of Columns

Question

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.

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:

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

I've used Google to search the answer, It looks like it is something to do with PIVOTing. As I have quite a lot of postcodes, it is hard to do a hard coded entry for each column. Looking for an easier solution. — user1001493, Oct 30 '12 at 15:45
@user1001493 Stackoverflow has some minor tricks using the sys.columns table to help make the PIVOTs easier. I believe they also use STUFF. — Adam Wenger, Oct 30 '12 at 15:58

score 3 · Accepted Answer · answered Oct 30 '12 at 22:12

3

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

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

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

answered Oct 30 '12 at 22:12

Taryn

242,637
56
362
405

Hi bluefeet, Thanks for your help. I am getting an error Incorrect syntax near '(' on the line "SET @Query = 'Select...(.' Actually I have changed the above query a lot and have tweeked it as per the requirement. I printed off the query and where the first @@Cols is used, i got a complete list, however, in the second one "for dropoff in (' + @@cols + '); I am not getting a full list of postcodes. Can you help me out here, please? Kind regards – user1001493 Oct 31 '12 at 14:20
@user1001493 can you edit the sql fiddle -- http://sqlfiddle.com/#!3/44fc1/1 -- with your code and then post the new link here? It is difficult to say what the issue might be. – Taryn Oct 31 '12 at 14:39
Hi Bluefeet, thanks again please find the link below, this tool is quite interesting. http://sqlfiddle.com/#!3/03694/2 Kind regards – user1001493 Oct 31 '12 at 17:19
1

@user1001493 see this fiddle -- http://sqlfiddle.com/#!3/03694/7 -- It appears your problem is you are using the `countdropoff` as both the aggregate and to select the `cols` from. You need to change the column part to `B` -- Here is a non-dynamic version to compare it too -- http://sqlfiddle.com/#!3/67c5a/5 – Taryn Oct 31 '12 at 17:25
Thanks Bluefeet it's now working correctly, much appreciated. Bless you. – user1001493 Nov 03 '12 at 13:03
@user1001493 if an answer on here is helpful to you, then be sure to accept it via the checkmark on the left. It will help future visitors know the accepted answer and you will get reputation from doing it. – Taryn Nov 04 '12 at 12:18

X number of Rows into X number of Columns

1 Answers1