October 7, 2008 at 2:43 pm
Hi,
I have two Columns State and City. When I select them from table, it displays like
STATE CITY
Connecticut Hartford
Connecticut Stamford
Connecticut New Haven
Connecticut Bridgeport
But I need them as
STATE CITY1 CITY2 CITY3 CITY4
Connecticut Hartford Stamford New Haven Bridgeport
So that I can use them in my SP. There are many cities for some states.
Can you please help me out?
October 7, 2008 at 3:38 pm
I think that this should do it nicely:
/*
Simple Dynamic Pivot Example:
*/
--====== Create really cool test data
Select distinct Table_name as [State], Column_name as [City]
Into #Cities
From INFORMATION_SCHEMA.COLUMNS
--====== Determine the maximum number of columns needed
Declare @MaxCol int
Select @MaxCol = Max(CityCount)
From (Select Count(City) as [CityCount] From #Cities
Group by [State]) tab
--====== Build the command header
Declare @sql varchar(max)
Set @sql = 'WITH cteCityNums as (Select State
, City
, Row_Number() Over (Partition by [State] Order by City) as CityNum
From #Cities)
Select [State]'
--====== Add in a cross-tab clause for every column needed
;WITH cteTally as (
Select Row_Number() Over(Order By [object_id]) as [Number]
From master.sys.system_columns)
, Max(Case When CityNum='
+Cast(Number as varchar(3))
+' Then City Else NULL End) as [City'
+Cast(Number as varchar(3))
+']'
From cteTally
Where Number <= @MaxCol
--====== Finish the dynamic SQL statement
From cteCityNums
Group By [State]'
--====== Execute it
Print @sql
EXEC (@sql)
Drop table #Cities
Pretty fast too, 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 7, 2008 at 3:51 pm
Hi RbarryYoung,
Thanks for your help but i have a problem where i work. I can't use dynamic SQL in the place i work. It will be of great help if you can modify it.
October 7, 2008 at 4:04 pm
Shree (10/7/2008)
Hi RbarryYoung,Thanks for your help but i have a problem where i work. I can't use dynamic SQL in the place i work. It will be of great help if you can modify it.
That's a shame because Dynamic SQL is the only way that I know of entirely within SQL to do it. Dynamic SQL is perfectly safe if used correctly which my routine does.
So, why aren't you allowed to use Dynamic SQL? Maybe you should apply for an exception.
Otherwise, I think that you will need to write a client program or something similar. Or if you can tell me the maximum column number, maybe I could write a static version for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 8, 2008 at 8:13 am
The maximum will be 50 I guess. I need this to use in my report. Anyway thanks so much for what you are doing.
October 8, 2008 at 8:25 am
Shree (10/8/2008)
The maximum will be 50 I guess. I need this to use in my report. Anyway thanks so much for what you are doing.
*sigh* Hookay. 50 it is ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 8, 2008 at 8:30 am
Okay, here is a static example for 50 columns:
/*
Simple Static Pivot/Cross-Tab Example:
*/
--====== Create really cool test data
Select distinct Table_name as [State], Column_name as [City]
Into #Cities
From INFORMATION_SCHEMA.COLUMNS
--====== Static Cross-Tab:
;WITH cteCityNums as (Select State
, City
, Row_Number() Over (Partition by [State] Order by City) as CityNum
From #Cities)
Select [State]
, Max(Case When CityNum=1 Then City Else NULL End) as [City1]
, Max(Case When CityNum=2 Then City Else NULL End) as [City2]
, Max(Case When CityNum=3 Then City Else NULL End) as [City3]
, Max(Case When CityNum=4 Then City Else NULL End) as [City4]
, Max(Case When CityNum=5 Then City Else NULL End) as [City5]
, Max(Case When CityNum=6 Then City Else NULL End) as [City6]
, Max(Case When CityNum=7 Then City Else NULL End) as [City7]
, Max(Case When CityNum=8 Then City Else NULL End) as [City8]
, Max(Case When CityNum=9 Then City Else NULL End) as [City9]
, Max(Case When CityNum=10 Then City Else NULL End) as [City10]
, Max(Case When CityNum=11 Then City Else NULL End) as [City11]
, Max(Case When CityNum=12 Then City Else NULL End) as [City12]
, Max(Case When CityNum=13 Then City Else NULL End) as [City13]
, Max(Case When CityNum=14 Then City Else NULL End) as [City14]
, Max(Case When CityNum=15 Then City Else NULL End) as [City15]
, Max(Case When CityNum=16 Then City Else NULL End) as [City16]
, Max(Case When CityNum=17 Then City Else NULL End) as [City17]
, Max(Case When CityNum=18 Then City Else NULL End) as [City18]
, Max(Case When CityNum=19 Then City Else NULL End) as [City19]
, Max(Case When CityNum=20 Then City Else NULL End) as [City20]
, Max(Case When CityNum=21 Then City Else NULL End) as [City21]
, Max(Case When CityNum=22 Then City Else NULL End) as [City22]
, Max(Case When CityNum=23 Then City Else NULL End) as [City23]
, Max(Case When CityNum=24 Then City Else NULL End) as [City24]
, Max(Case When CityNum=25 Then City Else NULL End) as [City25]
, Max(Case When CityNum=26 Then City Else NULL End) as [City26]
, Max(Case When CityNum=27 Then City Else NULL End) as [City27]
, Max(Case When CityNum=28 Then City Else NULL End) as [City28]
, Max(Case When CityNum=29 Then City Else NULL End) as [City29]
, Max(Case When CityNum=30 Then City Else NULL End) as [City30]
, Max(Case When CityNum=31 Then City Else NULL End) as [City31]
, Max(Case When CityNum=32 Then City Else NULL End) as [City32]
, Max(Case When CityNum=33 Then City Else NULL End) as [City33]
, Max(Case When CityNum=34 Then City Else NULL End) as [City34]
, Max(Case When CityNum=35 Then City Else NULL End) as [City35]
, Max(Case When CityNum=36 Then City Else NULL End) as [City36]
, Max(Case When CityNum=37 Then City Else NULL End) as [City37]
, Max(Case When CityNum=38 Then City Else NULL End) as [City38]
, Max(Case When CityNum=39 Then City Else NULL End) as [City39]
, Max(Case When CityNum=40 Then City Else NULL End) as [City40]
, Max(Case When CityNum=41 Then City Else NULL End) as [City41]
, Max(Case When CityNum=42 Then City Else NULL End) as [City42]
, Max(Case When CityNum=43 Then City Else NULL End) as [City43]
, Max(Case When CityNum=44 Then City Else NULL End) as [City44]
, Max(Case When CityNum=45 Then City Else NULL End) as [City45]
, Max(Case When CityNum=46 Then City Else NULL End) as [City46]
, Max(Case When CityNum=47 Then City Else NULL End) as [City47]
, Max(Case When CityNum=48 Then City Else NULL End) as [City48]
, Max(Case When CityNum=49 Then City Else NULL End) as [City49]
, Max(Case When CityNum=50 Then City Else NULL End) as [City50]
From cteCityNums
Group By [State]
--====== Clean-up
Drop table #Cities
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 8, 2008 at 8:56 am
Its giving me Row_number() is not a recognized function name.
October 8, 2008 at 9:03 am
Ah. This is a SQL Server 2005 forum. Are you on SQL Server 2005?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 8, 2008 at 9:07 am
yes i'm using sql server 2005
October 8, 2008 at 9:56 am
I figured out why, We are using the SQL server 2005 but the datamart is in SQL server 2000.
So that’s why its giving me error when im running.
October 8, 2008 at 11:20 am
Shree (10/8/2008)
I figured out why, We are using the SQL server 2005 but the datamart is in SQL server 2000.So that’s why its giving me error when im running.
Well, it works fine on SQL 2005, so I would suggest that you copy the data over there and then run it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 8, 2008 at 11:24 am
Actually, we can’t do it as the whole datamart is in 2000. So its not possible to do it. For doing that we must have approval from Business Management.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply