SQL help transposing data

Deebaser

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

Ganluan

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.

schuss

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.

Deebaser

It's totally ad hoc. The one requirement is that the states for the restuarant appear on the same row in the same cell.

Deebaser

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)

syndalis

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?

Deebaser

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.

syndalis

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?

Deebaser

zactly

Deebaser

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"

schuss

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.

syndalis

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.

Deebaser

-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

cmsamo

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

syndalis

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.

L Ron Howard

In Excel, you could make a Pivot table...

Creamstout

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.

Pailryder

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.

Deebaser

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

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

JdNoa

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