Is there a way to transpose row contents into seperate columns 1

[eo]

I will use a simple dataset. This dataset shows mortgages applications, and the applicants. For applicant 100 and 200 there are single applicants per applicantion, but for application 300 there are 3 applicants.

Application Applicant
100         Smith Andrew
200         Jones Daniel
300         Collins David
300         Bush Garry
300         Busby Joe

A piece of BI needs to show the applicants up to a max of 3 each in a seperate column, so I am trying top divise some T-sql to help with this. Therefore a single record per applicantion with 3 Applicant Columns (that can be null). Such as

Application Applicant1    Applicant2   Applicant3   
100         Smith Andrew  NULL         NULL
200         Jones Daniel  NULL         NULL
300         Collins David Bush Garry   Busby Joe

Any advise?

EO
Hertfordshire, England

 

[gmmastros]

What version of SQL Server are you currently using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eo]

SQL Sever 2008

EO
Hertfordshire, England

 

[gmmastros]

Take a look at this sample code. It uses a table variable to dummy up some data. Copy/paste this to a query window and run it. It shouldn't be too difficult to modify this for your purposes.

Declare @Temp Table(Application Int, Applicant VarChar(100))

insert Into @Temp Values(100,'Smith Andrew')
insert Into @Temp Values(200,'Jones Daniel')
insert Into @Temp Values(300,'Collins David')
insert Into @Temp Values(300,'Bush Garry')
insert Into @Temp Values(300,'Busby Joe')

; With cte As(
Select	*, Row_Number() Over(Partition By Application order by Applicant) As RowNumber
From	@Temp)
Select  Application, 
        Min(Case When RowNumber = 1 Then Applicant End) As Applicant1,
        Min(Case When RowNumber = 2 Then Applicant End) As Applicant2,
        Min(Case When RowNumber = 3 Then Applicant End) As Applicant3
From    cte
Group By Application

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should mention that the output is not EXACTLY what you want. The names are sorted within the row.

You wanted:
[tt]
Application Applicant1 Applicant2 Applicant3
100 Smith Andrew NULL NULL
200 Jones Daniel NULL NULL
300 [!]Collins David[/!] Bush Garry Busby Joe
[/tt]

My code shows:

[tt]
Application Applicant1 Applicant2 Applicant3
100 Smith Andrew NULL NULL
200 Jones Daniel NULL NULL
300 Busby Joe Bush Garry [!]Collins David[/!]
[/tt]

Notice that the Applicant 1 in your desired output is not the same as applicant 1 in my query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eo]

Perfect thanks

EO
Hertfordshire, England

 

[markros]

In SQL Server 2008 use PIVOT for this. Similar to what George showed, but simpler syntax:

select * from (select *, 'Applicant' + 
cast(row_number() over (partition by ApplicationID order by Applicant) as varchar(10)) as Row from myTable) src
PIVOT (max(Applicant) for Row IN ([Applicant1],[Applicant2], [Applicant3])) pvt

PluralSight Learning Library