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
