Select multiple times

[clegg]

Hi all,

I have a table that is basically ID, ItemID, Alias

There can be multiple rows for the same ItemID but the Alias will be different

EG,

ID ItemID Alias
1 1 Moose1
2 1 Moose2
3 1 Moose3
4 1 Moose4
5 2 Moose5
6 2 Moose6

What i would like as an output would be:

ItemID, All aliases
1,Moose1,Moose2,Moose3,Moose4
2,Moose5,Moose6,NULL,NULL

Anyone got any ideas without having to resort to an ugly cursor?

Cheers
Clegg

 

[gmmastros]

Take a look at this thread: thread183-1159740

If you have any followup questions, feel free to post them here.

-George

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

 

[clegg]

I probably didn't make my self clear......I want a recordset containing multiple columns, not one column containing a comma seperated list.

 

[gmmastros]

There may be several ways to do this. I usually use a table variable while manufacturing a 'rank' per group of ItemId's. Once you have this, it's simple to self join several times to get the data in the format you ultimately want.

Here is an example. I create a table variable to hold your initial data. This exists for testing purposes only. Ultimately, you will want to remove the table variable (@Temp) and modify the code to pull from your table.

[green] -- Creating a test table[/green]
Declare @Temp Table(Id int, ItemId Int, Alias VarChar(200))

Insert Into @Temp Values(1,      1,          'Moose1')
Insert Into @Temp Values(2,      1,          'Moose2')
Insert Into @Temp Values(3,      1,          'Moose3')
Insert Into @Temp Values(4,      1,          'Moose4')
Insert Into @Temp Values(5,      2,          'Moose5')
Insert Into @Temp Values(6,      2,          'Moose6')

[green]-- Query begins here[/green]
Declare @Out
Table   (RowId Int Identity(1,1),
        KeyId int,
        ItemId Int,
        Alias VarChar(200)
        )

Insert Into @Out(ItemId, Alias)
Select ItemId, Alias 
From   @Temp
Order By ItemId, Id

Update Out
Set    KeyId = RowId - MinRowId + 1
From   @Out As Out
       Inner Join (
         Select ItemId, 
                Min(RowId) As MinRowId 
         From   @Out
         Group By ItemId
         ) As A On Out.ItemId = A.ItemId

Select A.ItemId,
       A.Alias As Alias1,
       B.Alias As Alias2,
       C.Alias As Alias3,
       D.Alias As Alias4
From   @Out A
       Left Join @Out B
         On A.ItemId = B.ItemId
         And B.KeyId = 2
       Left Join @Out C
         On A.ItemId = C.ItemId
         And C.KeyId = 3
       Left Join @Out D
         On A.ItemId = D.ItemId
         And D.KeyId = 4
Where  A.KeyId = 1

You can copy/paste this code to query analyzer so you get a better understanding of how this works. I hope it helps.

-George

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