Convert rows into columns

  • 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.

    We are using SQL server 2005 but the datamart is in SQL server 2000, So i cant use 2005 functions like Pivot, Row_Number etc. We cant use dynamic sql at the place where i work. Can you please help me out?

  • ouch...without dynamic sql, and still trying to do it in SQL and not a programming language, I don't know how you'd be able to do it.

    the headache is the max() number of cities beneath a state...your database might currently have 4 cities for Connecticut as an example, but potentially you might need a column for each city.. ~270 cities for Connecticut. add a different state, and you might need more columns . because that value is potentially dynamic based on data input of new cities on a daily basis, you either need to assume a maximum number of columns, or build the view with dynamic sql...which is against your limitations.

    it's easy to create what you need in a programming language after gathering the data from sql server, but without dynamic sql, I'm not sure it's possible. just creating a grid and populating it from a loop.

    are you allowed to use a CLR to call an external program that would create the view dynamically? so the programming moves off of SQL and into CLR?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • No, because this sp will be connected to crystal reports for creating report.

  • Not sure if this would work as I haven't done much cross version stuff yet, but couldn't you select the data you need into a temp table on your SQL 2K5 Server before processing? Ordinarily you'd probably avoid this, but if it saves you doing something crazy with loops or cursors over a SQL 2000 method, it'd probably be worth it... if it worked.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. šŸ˜‰

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • As the datamart is connected to management Studio, when Iā€™m running it does not allow. So Iā€™m unable to copy to temp table also.

  • If you're using Crystal, then the crosstab report may be an option. Formatting in a crosstab report is poor, paper foot print may be an issue but being handicapped by no dynamic sql and no 2005 functions, you're options are limited.

  • First I tried in cross tab itself but I have other fields like sales also. So its not giving the correct records.

  • Here's a link to the OP's other post on this same topic for reference, but let's use this current thread as the only one going forward.

    Other thread:http://www.sqlservercentral.com/Forums/Topic582789-8-1.aspx

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • I don't see a quick elegant solution

    CURSOR will be the quickest

    PIVOT may be harder with UNKNOWN # of Cities/columns per row (may involve Dynamic SQL)

    Can you put the Cities into a CSV in 1 column?

    like

    "Ontario", "Toronto, Mississauga, Montreal, etc..."

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Yes Jerry, we can do in that way.

  • Jerry can you help me out to arrange them in one column?

  • Side note: you do NOT want to be in Montreal when they find out you've moved them to Ontario .....

  • Shree (10/9/2008)


    Jerry can you help me out to arrange them in one column?

    This would get the CSV

    DECLARE @Cities TABLE

    (

    State VARCHAR(20)

    ,City VARCHAR(20)

    )

    INSERT INTO @Cities

    SELECT 'Connecticut','Hartford'

    UNION ALL SELECT 'Connecticut','Stamford'

    UNION ALL SELECT 'Connecticut','New Haven'

    UNION ALL SELECT 'Connecticut','Bridgeport'

    SELECT State

    ,STUFF(

    (

    SELECT ', ' + B.City

    FROM @Cities B

    WHERE A.State = B.State

    FOR XML PATH(''))

    ,

    1, 2, '')

    FROM @Cities A

    GROUP BY State

  • Hi, when I run the code , its giving me Incorrect syntax near XML.

  • Shree (10/13/2008)


    Hi, when I run the code , its giving me Incorrect syntax near XML.

    That's strange. I copied & pasted the code straight from the forum. It's not giving me an error.

    Are you having trouble with this exact code? Or are you having trouble applying it to your scenario?

    If it is the latter, post the exact code you ran.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply