Need some with an sql statement

[JVZ]

I help some help writing an sql statement. Lets say I have a table like:

Table Structure:

CustomerID
CompanyID
Account
FirstName
LastName

A customer can have up to 4 account codes, so data in the table will looks something like this:

CustomerID CompanyID Account[tab][tab] FirstName[tab] LastName
1[tab][tab][tab][tab] 1[tab][tab][tab][tab] 123[tab][tab][tab] Joe[tab][tab][tab] Smith
1[tab][tab][tab][tab] 1[tab][tab][tab][tab] 345[tab][tab][tab] Joe[tab][tab][tab] Smith
1[tab][tab][tab][tab] 1[tab][tab][tab][tab] 678[tab][tab][tab] Joe[tab][tab][tab] Smith
1[tab][tab][tab][tab] 1[tab][tab][tab][tab] 910[tab][tab][tab] Joe[tab][tab][tab] Smith
2[tab][tab][tab][tab] 2[tab][tab][tab][tab] 777[tab][tab][tab] Alex[tab][tab][tab] Joe
2[tab][tab][tab][tab] 2[tab][tab][tab][tab] 888[tab][tab][tab] Alex[tab][tab][tab] Joe


I need to result set to look like this:

CustomerID CompanyID Account1 Account2 Account3 Account4 FirstName LastName
1[tab][tab][tab][tab] 1[tab][tab][tab][tab] 123[tab][tab] 345[tab][tab] 678[tab][tab] 910[tab][tab] Joe[tab][tab] Smith
2[tab][tab][tab][tab] 2[tab][tab][tab][tab] 777[tab][tab] 888[tab][tab] [tab][tab][tab][tab][tab][tab] Alex [tab][tab]Smith

Any help would be great.

Thanks

 

[gmmastros]

This is best shown by example. In the code I show, you should be able to copy/paste all of it to a query analyzer window and run it. It will provide the results you are looking for. Then, remove the @Table variable stuff and replace you table name instead.

[green]-- Test data[/green]
declare @Table table(CustomerID int, CompanyID int, Account int, FirstName varchar(50), LastName varchar(50))

Insert Into @Table Values(1,1,123,'Joe','Smith')
Insert Into @Table Values(1,1,345,'Joe','Smith')
Insert Into @Table Values(1,1,678,'Joe','Smith')
Insert Into @Table Values(1,1,910,'Joe','Smith')
Insert Into @Table Values(2,2,777,'Alex','Joe')
Insert Into @Table Values(2,2,888,'Alex','Joe')

[green]-- Actual code begins here[/green]

Declare @Temp Table(RowId Integer Identity(1,1), CustomerId integer, CompanyId Integer, Account Integer, FirstName VarChar(50), LastName VarChar(50))

Insert Into @Temp(CustomerId, CompanyId, Account, FirstName, LastName)
Select CustomerId, CompanyId, Account, FirstName, LastName 
from   @Table
Order By CustomerId, Account

Select A.CustomerId,
       Min(A.CompanyId) As CompanyId,
       Min(A.Account) As Account1,
       Min(B.Account) As Account2,
       Min(C.Account) As Account3,
       Min(D.Account) As Account4,
       Min(A.FirstName) As FirstName,
       Min(A.LastName) As LastName
From   @Temp A
       Left Join @Temp B
         On A.CustomerId = B.CustomerId
         And A.RowId = B.RowId - 1
       Left Join @Temp C
         On A.CustomerId = C.CustomerId
         And A.RowId = C.RowId - 2
       Left Join @Temp D
         On A.CustomerId = D.CustomerId
         And A.RowId = D.RowId - 3
Group By A.CustomerId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom