As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

SQL help transposing data

DeebaserDeebaser on my way to work in a suit and a tieAhhhh...come on fucking guyRegistered User regular
edited July 2012 in Help / Advice Forum
I'm trying to figure out a method for updating a column with data which would typically generate multiple rows onto a single row seperateed by a comma.

Example:
I have a table that shows the states a restuarant operates in. The data is stored thusly

McDonalds NY
McDonalds TX
McDonalds AK
KFC KY
KFC FL


I want to pull it in as "McDonalds" "NY, TX, AK"
"KFC","KY,FL"


Any thoughts? I'm stumped

Deebaser on

Posts

  • GanluanGanluan Registered User regular
    Is this a one time thing, or something that needs to be built for speed?

    An easy way would just be select all the data you want to begin with into a temp table, then loop through the temp table (grouped by restaurant) and populate a new table with the data you need.

    That's not going to be the most efficient though, so if you need something that will be used regularly I'd have to think about it more. I'm a .NET guy not a DBA so someone else may have a better idea.

  • schussschuss Registered User regular
    Ech, that's a bit nasty, as you're basically grouping by a substring of the column. It really needs to be split into temp tables or you need more control over the field length. You could theoretically join it to itself and sum the ends, but I'm not sure if that's fully supported in your system.
    Something like
    Select Left(a.name, 3) (for KFC), Sum(Right(b.name, 2)) From table1 a, table1 b where Left(a.name, 3) = Left(b.name, 3) Group by left(a.name, 3)

    In reality, you should just dump it into excel (if 1 time) or build more columns (if regular basis)/views which string these things out for you.

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    It's totally ad hoc. The one requirement is that the states for the restuarant appear on the same row in the same cell.


  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    Well, here's the thing. Im dealing with 10,000 restaurants* and they can operate in up to 10 states*.

    I'm not even sure how I can transpose that shit in excel.




    (im not really dealing with restaurants or states)

  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    Deebaser wrote: »
    It's totally ad hoc. The one requirement is that the states for the restuarant appear on the same row in the same cell.


    Are there going to be multiple "Mcdonalds" in "TX" and if so, will there be a need for the redundant entries, or do you only want unique values?

    SW-4158-3990-6116
    Let's play Mario Kart or something...
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited July 2012
    There will be multiple restaurants in each state, but I only give a shit about the McDonalds entity as a whole not the individual stores.

    Deebaser on
  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    Deebaser wrote: »
    There will be multiple restaurants in each state, but I only give a shit about the McDonalds entity as a whole not the individual stores.

    So, essentially... you want to turn THIS:

    McDonalds NY
    McDonalds TX
    McDonalds AK
    McDonalds TX
    McDonalds TX
    McDonalds AK

    Into this:

    "McDonalds" "NY, TX, AK"

    Right?

    SW-4158-3990-6116
    Let's play Mario Kart or something...
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    zactly

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited July 2012
    well, there really won't be any duplicate rows. There can only be One McDonald's in each state because....you know what... fuck the restaurant analogy. I'm really talking about "Asset Locations"


    Deebaser on
  • schussschuss Registered User regular
    In excel, you can filter and split the cells or use the "=Right(A1, 2)" to get the state. Then run a RTrim(A1, 3) to cut off the state part, then pivot.

  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    edited July 2012
    Deebaser wrote: »
    zactly

    Final question - does this process have to be completely done in SQL as a stored procedure that will get referenced all the time or something along those lines, or can we break out some VB or another language to make this easier?

    A quick and dirty app that can access the table will do this worlds better for you.

    Or excel if it is a one time thing.

    syndalis on
    SW-4158-3990-6116
    Let's play Mario Kart or something...
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    -It doesn't need to be done in SQL
    -I am absolute garbage at VB
    -The fields I need to transpose and concatenate are not char(2) They're strings of variable length

  • cmsamocmsamo Registered User regular
    edited July 2012
    I just did something very similar by loading excel spreadsheet of some 10000 assets into access. I then wrote a bunch of 'insert into' queries (1 query per column) to transpose the data, and insert into my target table. I wrote a vb wrapper to wipe my destination table, and run all queries sequentially. It didn't take long and runs quite fast

    cmsamo on
    steam_sig.png
  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    edited July 2012
    I mean, how I would do this is

    1) write a SQL query to only return unique values of that field (If there are 3 "McDonalds TX" entries, it will only return one. Return it sorted alphabetically.

    2) create a new array (array1) where you left/right the two values into... well... two values per row.

    3) create a new array (array2) from that first array that has only the unique names of the restaurants.

    4) Create a for each loop in which for each row in array1 in which column 1 matches on both array 1 and array 2, place the value of the second column into the second column of array2, along with a comma divider.

    sString = sString & array1(2) & ", ";

    at the end of the loop, run another quick for each loop to slice off the last two characters from each value in array 2, column 2. This will kill the stray comma and space.

    Export array 2 into a table of your choosing. you are done.

    syndalis on
    SW-4158-3990-6116
    Let's play Mario Kart or something...
  • L Ron HowardL Ron Howard The duck MinnesotaRegistered User regular
    In Excel, you could make a Pivot table...

  • CreamstoutCreamstout What you think I program for, to push a fuckin' quad-core? Registered User regular
    edited July 2012
    One way this could be done in SQL is to create a temp table that breaks up the values into two separate fields. Then create a new table making your "Restaurant" field a primary key. Insert the fields from the temp table, and on duplicate keys you update the state field (concatenate the string values in whatever format you want). If you wanted to make sure you don't enter the same unique combination twice...state and restaurant, you could set your primary key (unique key) in the temp table to a combination of the two fields.

    Creamstout on
  • PailryderPailryder Registered User regular
    This really depends on how complex your data is. In excel i'd put all the data in and remove duplicates unless you need them for some reason (in 2007/10 go to the data tab, highlight all your data and hit remove duplicates. Then we need to break out the asset name and the location. Hopefully the location is always after the last space, if it is you can use these two expressions (without the brackets)
    [=LEFT(A1,(FIND("@,SUBSTITUTE(A1, ","@,LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))))]
    [=MID(A1,FIND("@,SUBSTITUTE(A1, ","@,LEN(A1)-LEN(SUBSTITUTE(A1, ","")))),LEN(A1))]
    Now you have your asset name and location broken out and no duplicates it should be easier to group by asset name and append the location.

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited July 2012
    Talking to syndalis, I explained this poorly
    Restaurant and State are separate columns.

    I basically have this data

    A B
    1 McDonalds Texas
    2 McDonalds New York
    3 McDonalds Kentucky

    I'm trying to create this output

    A B
    1 McDonalds Texas, New York, Kentucky

    Sorry for the shit explanation

    Deebaser on
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    What I'm planning on doing right now is creating a temp table with the columns
    Restaurant
    ,State1
    ,State2
    ,State3
    ,State4

    UPDATE #output
    SET State1 = state
    FROM inputtable i
    , #output o
    WHERE
    i.restaurant = o.restaurant

    UPDATE #output
    SET State2 = state
    FROM inputtable i
    , #output o
    WHERE
    i.restaurant = o.restaurant
    AND o.State1 <> i.state

    UPDATE #output
    SET State3 = state
    FROM inputtable i
    , #output o
    WHERE
    i.restaurant = o.restaurant
    AND o.State1 <> i.state
    AND o.State2 <> i.state

    etc...

    Then in my print SELECT

    Restaurant
    ,State1 + ", " + State2 + ", " State3


    I am babby when it comes to SQL. There has to be a way to code this more awesomely




  • JdNoaJdNoa Registered User regular
    edited July 2012
    What database system are you using? In MySQL you could do it like this:

    select restaurant, group_concat(distinct state separator ',') from mytable group by restaurant

    But that's a MySQL-specific extension.

    Edit: I looked at your post history, and you're probably using Sybase...? Stack Overflow tells me Sybase has no equivalent to group_concat (and recommends writing a stored procedure). So, sorry, guess that wasn't very helpful.

    Poking around online, here's an approach that looks promising:

    http://www.sybaseteam.com/sql-simple-way-build-string-column-t-92.html

    JdNoa on
Sign In or Register to comment.