SQL, using rank to generate columns

Question

I have a list of occupants of a property and need to manipulate the data so it instead shows the property as one row with each additional occupant appearing in a new column.

Here is what I've managed to do so far:

with RANKING AS 
(   Select 
    Postcode
    , Number
    , Occupant
    , RANK() OVER
    (Partition by Postcode order by Occupant) as [Rank]

from Reporting.dbo.Test --order by [Rank] desc
)

The query in RANKING outputs the following table:

Postcode | Number | Occupant | Rank
AA001AA  |  12    |    D     |  1
AA001AA  |  12    |    E     |  2
AA001AA  |  12    |    K     |  3
AA001AA  |  12    |    M     |  4
AA001AA  |  12    |    T     |  5
BB001BB  |   8    |    M     |  1
BB001BB  |   8    |    R     |  2

etc.

I've then tried to use the value of ranking to create columns, like so:

Select distinct
i.Postcode
, i.Number
    , case when i.[rank] = 1 then i.Occupant end as [First Tennant]
    , case when i.[rank] = 2 then i.Occupant end as [Second Tennant]
    , case when i.[rank] = 3 then i.Occupant end as [Third Tennant]
    , case when i.[rank] = 4 then i.Occupant end as [Fourth Tennant]
    , case when i.[rank] = 5 then i.Occupant end as [Fifth Tennant]

    from Reporting.dbo.Test u
            inner join RANKING i on i.Postcode = u.Postcode

Now, 2 questions:

1) Is there any way to automate this process so for a rank of x we have x tenant rows

2) The table this outputs is

Postcode | Number | First Tennant | Second Tennant | Third Tennant | Fourth Tennant | Fifth Tennant |
AA001AA  |   12   |        D      |       NULL     |       NULL    |       NULL     |      NULL     |
AA001AA  |   12   |      NULL     |         E      |       NULL    |       NULL     |      NULL     |

etc.

How do I condense this list so each postcode only has one row and all the non-null values appear on that row.

Do you need the actual words "First", "second" etc? Or will it suffice to have the number in there? It doesnt fundamentally change the answer, but if you need those, you'll probably want an additional mapping table to map numbers to their string representations. — Xedni, Sep 12 '17 at 15:29
You can automate this with a dynamic pivot. The number of columns will equal the max needed for a given postcode/number. NULL will be present for all rows that don't contain those tenants. — S3S, Sep 12 '17 at 15:30
Any way of distinguishing it would work, if there was a way of putting, for example, Tennant (Rank value), that would be fine. — MichaelL, Sep 12 '17 at 15:32
You can use a dynamic pivot like this https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query/10404455#10404455 — Sean Lange, Sep 12 '17 at 16:11

score 0 · Answer 1 · answered Sep 12 '17 at 16:34

To answer your second question first (how do you condense the list), the easiest way would just to group by postcode and number. You can just take the max() of each column (i.e if there's a value, it gets chosen; all the nulls fall out).

To answer the first question (can this be automated), you probably want to look into a dynamic pivot. One such article posted in the comments is this: SQL Server dynamic PIVOT query?

The idea is basically to serialize the distinct tennants, and concantenate that into a dynamic SQL string which performs a pivot. There are several ways to skin this cat, but here's how I approached it.

use tempdb
go

if object_id('tempdb.dbo.#data') is not null drop table #data
create table #data
(
    PostCode varchar(10),
    Number int,
    Occupant char(1),
    Rnk int,
    ColName as 'Tennant ' + cast(Rnk as varchar(10))

)

insert into #Data(PostCode, Number, Occupant, Rnk)
select 'AA001AA', 12, 'D',1
union all select 'AA001AA', 12, 'E',2
union all select 'AA001AA', 12, 'K',3
union all select 'AA001AA', 12, 'M',4
union all select 'AA001AA', 12, 'T',5
union all select 'BB001BB',  8, 'M',1
union all select 'BB001BB',  8, 'R',2

declare 
    @PivotColumns nvarchar(max),
    @SelectColumns nvarchar(max),
    @sql nvarchar(max)

select 
    @PivotColumns = stuff((select ',' + quotename(ColName)
                      from #data
                      group by ColName
                      order by ColName
                      for xml path('')), 1, 1, ''),
    @SelectColumns = stuff((select ',' + quotename(ColName) + ' = max(' + quotename(ColName) + ')'
                            from #data
                            group by ColName
                            order by ColName
                            for xml path('')), 1, 1, ''),
    @sql = '
        select 
            PostCode, 
            Number,
            ' + @SelectColumns + '
        from #data d
        pivot (max(Occupant) for ColName in (' + @PivotColumns + ' )) p
        group by PostCode, Number'


print @sql
exec sp_executesql @sql

score 0 · Answer 2 · answered Sep 12 '17 at 16:50

you can use dynamic pivot to get your result. please see below code-

create  table #tab (Postcode varchar(10) , Number int , Occupant char(1) , Rank int)

insert into #tab
select 'AA001AA'  ,  12    ,    'D'     ,  1
union all select 'AA001AA'  ,  12    ,    'E'     ,  2
union all select 'AA001AA'  ,  12    ,    'K'     ,  3
union all select 'AA001AA'  ,  12    ,    'M'     ,  4
union all select 'AA001AA'  ,  12    ,    'T'     ,  5
union all select 'BB001BB'  ,   8    ,    'M'     ,  1
union all select 'BB001BB'  ,   8    ,    'R'     ,  2
union all select 'CC001CC'  ,   8    ,    'N'     ,  1
union all select 'CC001CC'  ,   8    ,    'O'     ,  2
union all select 'CC001CC'  ,   8    ,    'P'     ,  3
union all select 'CC001CC'  ,   8    ,    'Q'     ,  4
union all select 'CC001CC'  ,   8    ,    'R'     ,  5
union all select 'CC001CC'  ,   8    ,    'S'     ,  6
union all select 'CC001CC'  ,   8    ,    'T'     ,  7
union all select 'CC001CC'  ,   8    ,    'U'     ,  8
union all select 'CC001CC'  ,   8    ,    'V'     ,  9
union all select 'CC001CC'  ,   8    ,    'W'     ,  10

declare @mx int , @min int = 1 , @sql nvarchar(max) = '' , @select1 nvarchar(max) = '',@select2 nvarchar(max) = ''

select @mx = MAX(rank) from #tab

while @min<= @mx
begin
set @select1  = @select1 + '[' + cast(@min as varchar(10))+ '] ,' 
set @select2  = @select2 + '[' + cast(@min as varchar(10))+ ']  as '+ '[Tennant_' + cast(@min as varchar(10))+ '] ,' 
set @min = @min + 1
end 

set @select1  = SUBSTRING( @select1 , 1 , LEN(@select1)-1)
set @select2  = SUBSTRING( @select2 , 1 , LEN(@select2)-1)


set @sql = '
 SELECT Postcode    , Number ,'+@select2+'
FROM  #tab
PIVOT
(
    max(Occupant)
    FOR [Rank] IN ('+@select1+')
)AS pvt '

 exec sp_executesql @sql

SQL, using rank to generate columns

2 Answers2