I have a scenario where I'd like to get columns with information stored in a very specific way.  I'd like to select n number of columns from a table A, concatinate their values, and then add m number of more values which should be grabbed from other tables by joining one of their columns to the current statement's column.    Cars - (car_id, model_id, plate_id, brand_name, year_of_release, custom_column) Model - (model_id, model_name, description) Plates -  (plate_id, car_owner_name, registration_state)   Let's say we have these tables and what we want to do is place the following information in one column:  'car_id, brand_name, year_of_release, plate_id, model_name, car_owner_name, registration_state'   for each row of the Cars table as a 'custom_column' value  I'd like it to be a comma-separated string. Is there a way to do this with MySQL?
12844
Using MySQL, I can do something like:  SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;   My Output:  shopping fishing coding   but instead I just want 1 row, 1 col:  Expected Output:  shopping, fishing, coding   The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.  I've looked for a function on  and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets.  So does anyone here know how to do this?
48546
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this  trick but I can't recall it and must not have saved it. I can't seem to get it to work in this case either, only seems to work on a single row.  The data looks like this:  id  row_num  customer_code comments ----------------------------------- 1   1        Dilbert        Hard 1   2        Dilbert        Worker 2   1        Wally          Lazy   My results need to look like this:  id  customer_code comments ------------------------------ 1   Dilbert        Hard Worker 2   Wally          Lazy   So for each row_num there's really only one row of results; the comments should be combined in the order of row_num. The above linked SELECT trick works to get all the values for a specific query as one row, but I can't figure out how to make it work as part of a SELECT statement that spits all these rows out.   My query has to go through the whole table on its own and output these rows. I'm not combining them into multiple columns, one for each row, so PIVOT doesn't seem applicable.
37749
I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .  For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.  My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.   However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see  for a clearer description).  But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see ).  AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?  EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.  Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.
21450
Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases  (PIVOT, ROWS to COLUMNS, TRANSPOSE &amp;c.) and all I get is  TSQL this, TSQL that and or Oracle this, Oracle that, proprietary function this, proprietary function that well, you get the picture.  I have a simple table structure - if it was any simpler, it'd be a list :-) - i.e. it's two columns.  stock_date       sku ----------       --- 2016-06-01         0 2016-06-02       123 2016-06-03       123 2016-06-04       123 2016-06-06       123 2016-06-07       123 2016-06-03       245 &lt;... &amp;c...&gt;   There may be many thousands of sku's but the date range will be at most a month or so, but can vary, so the code needs to cater for that.   So, I want to make the stock_date field a column.   sku     1   2   3   4   5   6   7 ---     -   -   -   -   -   -   -  123     0   Y   Y   Y   0   Y   Y 245     0   0   Y.... &amp;c.   Ideally, the code would be dynamic and would work with any server (even MySQL).   But if it has to use common table expressions and/or window functions, that's  OK but not proprietary crosstab or pivot functions.
77059
I want to concatenate/combine columns A, B and C into column D as displayed.  The number of filled entries in A, B and C will vary over time, so absolute cell addresses cannot be used.  What formula could I put into D1 to fill the column as illustrated?
51345
The MySQL manual at  covers this.  Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; .  This needs to work with , which stores things very differently than .
114133
I'm running a project on a Postgres database and need to retrieve the comments on columns within the DB to be used as table headings and such. I have seen that there are a couple of built in functions ( and ) but i haven't been able to find examples on how to use them and playing around with them has proved pretty futile.  So I was wondering if any has been able to do this before and if so, how?
42072
I have a table called MyTable with varchar columns named SourceID and ReferenceID, where SourceID contains a table name and ReferenceID contains a key value for the source table. Is it possible in a query to refer to the source table's row assuming I know the name of the key column that matches the value in RefrenceID? Sample: SELECT     * FROM     MyTable as MT INNER JOIN MT.SourceID as Src ON MT.ReferenceID = Src.InstanceID  This doesn't work, obviously.  But is there a way to do this as opposed to dynamically building the SQL statement using the values of SourceID and ReferenceID and then executing the query?  More specifically, is there a way to make MyTable.SourceID in the example above be usable within the JOIN? Further considerations I found , and it has a trick like follows: select   E.EmployeeName, coalesce(s.store,o.office) as Location from   Employees E left outer join    Stores S on ... left outer join   Offices O on ... where   O.Office is not null OR S.Store is not null  The WHERE clause simply helps the outer joins behave like INNER JOINs.  This would work well enough if there were a limited number of table options and they all had UNIQUE constraints.  This gets me part of the way there but but I really want is to have the table names in the JOINs to be based on a column in Employees. I haven't given up yet. Any and all help is greatly appreciated.
118306
I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values.  This is identical to , but for Oracle rather than SQL Server or MySQL.
3484
I have data that looks like this:  Id | Loc1 | Loc2 | Loc3 | Loc4 ---+------+------+------+----- 1  | NY   | CA   | TX   | IL 2  | WA   | OR   | NH   | RI   And I want to convert it to this:  Id | LocNum | Loc ---+--------+---- 1  |   1    | NY 1  |   2    | CA 1  |   3    | TX 1  |   4    | IL 2  |   1    | WA 2  |   2    | OR 2  |   3    | NH 2  |   4    | RI   What's the easiest way to do this in Excel 2007?
49890
I'm currently trying to create a View from a Table that does the following:  Table A ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║    FOO,BAR    ║ ║  PN0004-01  ║  BAR,FOO,FOB  ║ ╚═════════════╩═══════════════╝   To create  View B ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║      FOO      ║ ║  PN0003-01  ║      BAR      ║ ║  PN0004-01  ║      BAR      ║ ║  PN0004-01  ║      FOO      ║ ║  PN0004-01  ║      FOB      ║ ╚═════════════╩═══════════════╝   So, essentially, I need to take records from Table A and insert them into View B, where if the record from A has a LIKE '[,]' property, then that field is split into multiple records in B, depending on the number of times the ',' is present, with the text between each ',' being given its own record in View B.
15767
I have a set of 10 tables and each has an extra column that I don't want. Specifically, these tables are descriptions of database tables exported by phpMyAdmin, and they all have a "MIME" column that I would like to remove. Is there a way to do this other than using string manipulation / find and replace?   I have tried setting the column to p{0in}, but the column title still remains.
46559
One of my columns is called from. I can't change the name because I didn't make it. Am I allowed to do something like SELECT from FROM TableName or is there a special syntax to avoid the SQL Server being confused?
12291
I have a stored procedure that returns 80 columns, and 300 rows.  I want to write a select that gets 2 of those columns.  Something like   SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'   When I used the above syntax I get the error:      "Invalid Column Name".   I know the easiest solution would be to change the stored procedure, but I didn't write it, and I can't change it.    Is there any way to do what I want?   I could make a temp table to put the results in, but because there are 80 columns so I would need to make an 80 column temp table just to get 2 columns.  I wanted to avoid tracking down all the columns that are returned. I tried using WITH SprocResults AS .... as suggested by Mark, but I got 2 errors      Incorrect syntax near the keyword 'EXEC'.Incorrect syntax near ')'.  I tried declaring a table variable and I got the following error      Insert Error: Column name or number of supplied values does not match table definition  If I try  SELECT * FROM EXEC MyStoredProc 'param1', 'param2' I get the error :     Incorrect syntax near the keyword 'exec'.
75446
How can I add a column with a default value to an existing table in  / ?
100007
currently I have a column looks like this:    A B ... 1 A 2 3 4 B 5  6  7 C 8  ...   And I want to make it looks like this    A B ... 1 A 2 A 3 A 4 B 5 B 6 B 7 C 8 C ...   Is there a way to finish the job quickly? Thanks a lot!
75128
Is there an elegant way to do this:  SELECT Cols from MyTable WHERE  zip = 90210 OR zip = 23310 OR zip = 74245 OR zip = 77427 OR zip = 18817 OR zip = 94566 OR zip = 34533 OR zip = 96322 OR zip = 34566 OR zip = 52214 OR zip = 73455 OR zip = 52675 OR zip = 54724 OR zip = 98566 OR zip = 92344 OR zip = 90432 OR zip = 91532 OR ...   (zip codes in this post are ficticious and bear no resemblance to actual zip codes living or dead)