I am using Amazon redshift. How do I combine the result of the columns.  If the original rows are:  *ID   Name  Color ---------------- 1   John   White 1   John   Black 2   Mark   Blue 2   Mark   Red*   the result should be:  *ID   Name  Color ---------------- 1   John   White Black 2   Mark   Blue Red*
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?
106822
Given a file with two columns:  Id  ht 510 69 510 67 510 65 510 62 510 59 601 29 601 26 601 21 601 20   I need a way to coalesce all rows with the same ID into one that has an average height. In this case, (69 + 67 + 65 + 62 + 59) / 5 = 64 and (29 + 26 + 21 + 20) / 4 = 24, so the output should be:  Id  Avg.ht  510 64  601 24   How can I do that using sed/awk/perl?
122132
I have a table that looks like this in Google Big Query:  custID  InteractionDate Purchased?  Sales 1       20150312           F         0 1       20150312           T         200 1       20150314           T         150 2       20150221           T         400 2       20150221           F         0 2       20150221           T         120   ..that I need to transform to the following structure:   custID InteractionDate Success Sales  1     20150312          0.5    200  1     20150314           1     150  2     20150221         0.66    520   ..where in this second table the success column is defined as  count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),   and sales is just the sum.  I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.
79492
I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of first three. so for all the combinations of three columns there is different value for the fourth column. Now I want a design for data like this. Any help is appreciated.
70835
I have some Excel columns that look like this.  1774    David                    Not Upheld 1770    James                    Upheld 1771    David                    Upheld 1772    Sam                      Upheld 1768    Arthur                   Not Upheld 1769    James                    Upheld   How can I write an Excel formula for: count the number of rows with 'James' in column B, and 'Upheld' in column C?  I know how to do each of these conditions individually (using COUNTIF), but I'm not sure of the best way to combine them.  Thanks!
108130
I got the following table on google sheets.   Text version +----+---+---+----+ |    | A | B | C  | +----+---+---+----+ | 1  | 1 | 2 | 1  | +----+---+---+----+ | 2  | 2 | 1 | 1  | +----+---+---+----+ | 3  | 3 | 1 | 4  | +----+---+---+----+ | 4  | 3 | 2 | 7  | +----+---+---+----+ | 5  | 3 | 3 | 8  | +----+---+---+----+ | 6  | 3 | 4 | 1  | +----+---+---+----+ | 7  | 4 | 1 | 3  | +----+---+---+----+ | 8  | 4 | 2 | 4  | +----+---+---+----+ | 9  | 4 | 3 | 13 | +----+---+---+----+ | 10 | 4 | 4 | 1  | +----+---+---+----+ | 11 | 4 | 5 | 3  | +----+---+---+----+ | 12 | 5 | 3 | 5  | +----+---+---+----+ | 13 | 5 | 4 | 14 | +----+---+---+----+ | 14 | 5 | 5 | 5  | +----+---+---+----+ | 15 | 5 | 3 | 8  | +----+---+---+----+ | 16 | 6 | 4 | 27 | +----+---+---+----+ | 17 | 6 | 5 | 16 | +----+---+---+----+ | 18 | 5 | 5 | 1  | +----+---+---+----+ | 19 | 7 | 4 | 4  | +----+---+---+----+ | 20 | 7 | 5 | 1  | +----+---+---+----+ | 21 | 8 | 3 | 2  | +----+---+---+----+ | 22 | 8 | 4 | 72 | +----+---+---+----+ | 23 | 8 | 5 | 55 | +----+---+---+----+   I want the first column to represent the groups in x axis. The 2nd column is supposed to be the a group in a certain x value lets say I have 8 3 22 8 4 55 8 5 72 I want to have at value x = 8 , 3 bars, the first bar has the y value of 22 with the label 3, the 2nd has y value of 22 with label 4, and the 3rd has y value of 72 with label 5. This is the chart I got which is completely not the one I wanted.
86051
This question is similar, yet different, to another DBA SE question I have here:     I have a FINANCE_SOURCE table:  +-----------+------------+-------------+--------+------+ | SOURCE_ID | PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR | +-----------+------------+-------------+--------+------+ |       101 |          1 | A           |  10.00 | 2017 | |       102 |          1 | B           |   5.00 | 2017 | |       103 |          1 | B           |  15.00 | 2017 | |       104 |          1 | B           |  70.00 | 2016 | |       105 |          1 | C           |  30.00 | 2017 | |       106 |          1 | D           |   1.00 | 2016 | |       107 |          1 | D           |  20.00 | 2017 | +-----------+------------+-------------+--------+------+   I want to rearrange the data, so that SOURCE_A, SOURCE_B, and OTHER(anything that's not A or B) are in separate columns.  The data needs to be grouped into separate YEARS:  +------------+---------+---------+-------------+-------+------+ | PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR | +------------+---------+---------+-------------+-------+------+ |          1 |      10 |      20 |          50 |    80 | 2017 | |          1 |         |      70 |           1 |    71 | 2016 | +------------+---------+---------+-------------+-------+------+   How can I do this? Performance is important.
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?
64186
In my company, I need to join table A to table B:   Table A is the sources table  Table B is the empty table   I want to know that how can I do it and the result like table B?  Source table A:  ID    date     money    HOUR ----------------------------- 1     20-1      10       1 1     20-1      20       2 1     20-1      30       3 1     20-1      40       4 1     21-1      50       1 1     21-1      10       2 1     21-1      20       3 1     21-1      30       4 2     20-1      40       1 2     20-1      50       2 2     20-1      10       3 2     20-1      20       4   Empty table B:  ID   date   Hour1   Hour2   Hour3   Hour4    Sum ------------------------------------------------- 1    20-1    10      20      30      40      100 1    21-1    50      10      20      30      110 2    20-1    40      50      10      20      120
96092
I have three columns as shown below. What is the best way to pull all the values from column 3 that are not the same in the other columns? the result should return 2, 7 and 9 as they are only in column 3.   Column 1 |  Column 2  | Column 3 ---------|------------|--------- 1        |    5       |     1 3        |    4       |     2 1        |    5       |     7 4        |    6       |     9   I tried  not in (Column 1, Column 2) method but it checks by row only and I need it to take first value in column 3 and compare against the whole column in 1 and 2....
64714
I have the following table:  NAME Alex Bob  Bob Tim Alex Roger   I need to produce the following:   Name    Count Percentage  Alex    2     33%  Bob     2     33%  Roger   1     16.6%  Tim     1     16.6%   I have no idea where to start.  I can do the initial query for the count which is simple.
120564
I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005.  my input Data    ONAY_ID  sip_RECno    KULLANICI       ONAY    1           1         user1          true    2           1         user2          true    3           1         user3          false    4           1         user4          true   Output would be   sip_RECno  user1     user2    user3     user4           1          true     true    false    true
38248
I have three tables and i need an out put which has data from two tables, i would like to create the same using macro.  Table A  A B C D   Table B  Apple Orange Pears   Table C  Americas Asia Europe   The Output i need is  A Apple Americas A Apple Asia A Apple Europe A Orange Americas A Orange Asia A Orange Europe   So i guess for each letter from Table A, there would be 12 additional rows created. The above is the sample data and I have around 5000 rows in Table A.
31950
I have a Google Sheets with a long list of names and amounts. Many of the names are duplicates. For example:  John  | 10 Bill  | 2 Susan | 3 Frank | 4 Sally | 10 John  | 2 Susan | 2 John  | 3   Is there any automated way to combine all of the values so I get a list without any duplicate names and a single sum of all of each person's entry?  For example:  John  | 15 Bill  | 2 Susan | 5 Sally | 10
20640
I'd like to remove from a given column ($2 in the example) the duplicate fields (comma separated), but only if the ID column ($1 in the example) is the same.  Input:  A    1,2,3,4 A    8,9,10,11 A    2,3,4,11,12 B    4,5,6,7 B    6,8,9,10   Expected output:   A    1,2,3,4 A    8,9,10,11 A    12 B    4,5,6,7 B    8,9,10
95576
Supposing we have a table with four columns (a,b,c,d) of the same data type.  Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?
64809
I'm not sure if I can complete this operation in one step - but someone might be able to help me.  My input file is a csv file with PointID, x, y, z and LineID columns.  I'm looking to combine all rows with the same LineID into Polyline25D using ogr2ogr with a virtual datasource (VRT file).  I can get a MultiPoint output, but can't work out how to combine multiple rows (if it's at all possible).   Alternatively, could someone help me with a python script to combine the x, y, z fields into WKT?  Input Example :  pointid y   x   z   lineid 1   320589.01   1815258.73  423.78  1 2   320573.49   1815256.71  425.04  1 3   320557.42   1815252.40  425.86  1 4   320541.64   1815248.71  426.19  1 5   320529.37   1815247.01  427.18  1 1   320588.78   1815259.35  423.43  2 2   320573.06   1815257.09  424.66  2 3   320557.00   1815253.09  425.06  2 4   320541.79   1815249.54  425.78  2 5   320529.14   1815247.74  426.22  2 6   320516.78   1815245.02  426.84  2 1   320588.62   1815260.10  423.44  3 2   320572.59   1815258.42  424.65  3 3   320556.21   1815255.29  425.13  3 4   320540.73   1815252.19  425.80  3 5   320529.02   1815248.37  426.20  3