distinct columns

Please - please -please someone help me with this one:

current table looks like this:

Act Num City

1 New York

1 Tampa

2 Denver

3 LA

3 Tampa

3 New York

Im trying to (have to ) create a table that looks like this:

Act Num City 1 City 2 City 3 --City 10

1 New York Tampa Null Null

2 Denver null null null

3 LA Tampa New York null

I new to this and am at wits end, can some one help?If you are using SQL 2005, refer to Books Online, Topic: Rotating Columns|||

Here you go..

Code Snippet

Create Table #cities (

[Act Num] Int,

[City] Varchar(100)

);

Insert Into #cities Values('1','New_York');

Insert Into #cities Values('1','Tampa');

Insert Into #cities Values('2','Denver');

Insert Into #cities Values('3','LA');

Insert Into #cities Values('3','Tampa');

Insert Into #cities Values('3','New_York');

Select

*

,Identity(int,1,1) as RowId

INTO

#Data

From

#cities

Select

[Act Num]

,Max(Case RowId When 1 Then City End) [City1]

,Max(Case RowId When 2 Then City End) [City2]

,Max(Case RowId When 3 Then City End) [City3]

,Max(Case RowId When 4 Then City End) [City4]

,Max(Case RowId When 5 Then City End) [City5]

,Max(Case RowId When 6 Then City End) [City6]

,Max(Case RowId When 7 Then City End) [City7]

,Max(Case RowId When 8 Then City End) [City8]

,Max(Case RowId When 9 Then City End) [City9]

,Max(Case RowId When 10 Then City End) [City10]

From

(

select

[Act Num]

,[City]

,RowId - (Select Min(RowId)-1 From #Data Sub Where Sub.[Act Num]=Main.[Act Num]) as RowId

From

#Data Main

) as data

Group By [Act Num]

|||That did it - Thank You so much!|||

Hi Manivannan,

I liked your script. I would like to add just one point in order to use the new ROW_NUMBER() function in SQL2005

So instead of

Code Snippet

select

[Act Num]

,[City]

,RowId - (Select Min(RowId)-1 From #Data Sub Where Sub.[Act Num]=Main.[Act Num]) as RowId

From #Data Main

The below script can also be used

Code Snippet

select

[Act Num]

,[City]

,ROW_NUMBER() OVER (PARTITION BY [Act Num] ORDER BY City) AS RowId

From #Data Main

Eralper

http://www.kodyaz.com