I have two tables  land =========== lid name meter address  owner ========== ownerid  name family mobile lid   Every land can have maximum 12 owners.  The result that I need:  lid name meter address owner_name_1 owner_family_1 owner_mobile_1 owner_name_2 owner_family_2 owner_mobile_2 .... owner_name_12 owner_family_12 owner_mobile_12   I tried pivot but I can not write it correctly.
108733
I have a table that looks like this:  RECIPE     VERSION_ID     INGREDIENT    PERCENTAGE 4000       100            Ing_1          23,0 4000       100            Ing_100         0,1 4000       200            Ing_1          20,0 4000       200            Ing_100         0,7 4000       300            Ing_1          22,3 4000       300            Ing_100         0,9 4001       900            Ing_1           8,3 4001       900            Ing_100        72,4 4001       901            Ing_1           9,3 4001       901            Ing_100        70,5 5012       871            Ing_1          45,1 5012       871            Ing_100         0,9 5012       877            Ing_1          47,2 5012       877            Ing_100         0,8 5012       879            Ing_1          46,6 5012       879            Ing_100         0,9 5012       880            Ing_1          43,6 5012       880            Ing_100         1,2   There are 100 ingredients per recipe/version. I'd like to display the data from this table like this:  RECIPE     INGREDIENT_Vxxx     PERCENTAGE_Vxxx     INGREDIENT_Vyyy     INGREDIENT_Vyyy (ETC) 4000       Ing_1               23,0                Ing_1               20,0 4000       Ing_100             0,1                 Ing_100              0,7   Because in different versions of recipes, ingredients could be removed or added, I'd like to display both ingredient and percentage per version per recipe. There's also the difficulty that different recipes have different number of versions.  I'm not even sure if this is possible at all or where to begin. Maybe with the PIVOT function?  Could anyone please point me in the right direction?
4375
Having the following data in a table:  ID     Category   Value 1234   Cat01      V001 1234   Cat02      V002 1234   Cat03      V003 1234   Cat03      V004 1234   Cat03      V005   I want to have the following output:  ID     Cat01  Cat02   Cat03 1234   V001   V002    V003 1234   V001   V002    V004 1234   V001   V002    V005   The output I want to achieve is a kind of pivot table where I have all the values vertically in a table and I want to have those values, horizontally, having the category as a column. But there are some categories that have multiples values, in that case, I need to repeat the values of all other categories and create a row per each repeated value  How can it be done in PostgreSQL?
80579
I have a table, call it TBL. It has two columns,call them A and B. Now in the query I require one column as A and other column should be a comma seprated list of all B's which are against A in TBL.  e.g. TBL is like this  1   Alpha  2   Beta  1   Gamma  1   Delta  Result of query should be   1   Alpha,Gamma,Delta  2   Beta  This type of thing is very easy to do with cursors in stored procedure. But I am not able to do it through MS Access, because apparently it does not support stored procedures.  Is there a way to run stored procedure in MS access? or is there a way through SQL to run this type of query
121058
I want to find out unmatched records of two tables in MySQL.
68032
I have a table item having just one column name:-  name ----- toys shirt mobile Shirt speaker Toys ....  ....   I am trying to delete duplicate rows except one duplicated record with the help of below query  DELETE FROM ( SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME,  @num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM  item ORDER BY `name`) AS result1 ) AS result  WHERE rownumber &gt;1   The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.  I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.
62521
To be specific, I have an event table that logs when a user joins or leaves a team.  It looks something like the following:  ------------------------------------- | user | event  | team | timestamp | ------------------------------------- | A    | joined | 1    | 2016-1-1  | | B    | joined | 1    | 2016-1-1  | | C    | left   | 1    | 2016-1-1  | | C    | joined | 2    | 2016-1-1  | | A    | left   | 1    | 2016-1-2  | | A    | joined | 2    | 2016-1-2  | | B    | left   | 1    | 2016-1-3  | | A    | left   | 2    | 2016-1-3  | -------------------------------------   I need to restructure this in a view to look something like the following  -------------------------------------- | user | team | joined    | left     | -------------------------------------- | A    | 1    | 2016-1-1  | 2016-1-2 | | A    | 2    | 2016-1-2  | 2016-1-3 | | B    | 1    | 2016-1-1  | 2016-1-3 | | C    | 1    | null      | 2016-1-1 | | C    | 2    | 2016-1-1  | null     | --------------------------------------   How can I achieve this?  For more detail, I'm attempting to do this in Amazon Redshift (PostgreSQL)
99280
I put together a sample scenario of my issue and I hope its enough for someone to point me in the right direction.  I have two tables  Products    Product Meta    I need a result set of the following
61628
I would like to convert several range of columns into rows, and keeping the association to the original col/row source  Sample source sheet  --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc...365 days | --------------------------------------------------- | name 1  |    25    |   30     |    5     | etc...|  --------------------------------------------------- | name 2  |    2     |          |    4     | etc...| --------------------------------------------------- | etc ... 100 rows   Expected Results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- | name 1  |  1/2/2018   |   30     |    --------------------------------------------------- | name 1  |  1/3/2018   |   5      |    --------------------------------------------------- | name 1  |  etc.. 365 days --------------------------------------------------- | name 2  |  1/1/2018   |   2      |    --------------------------------------------------- | name 2  |  1/2/2018   |          |    --------------------------------------------------- | name 2  |  1/3/2018   |   4      |    --------------------------------------------------- | name 2  |  etc.. 365 days --------------------------------------------------- | etc ... for each change of name pull all the col dates/values   Current results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- |         |  1/2/2018   |   30     |    --------------------------------------------------- |         |  1/3/2018   |   5      |    --------------------------------------------------- |         |  etc.. 365 days                      I have to copy down to bring in "name 1" for each date  --------------------------------------------------- | name 2  |  1/1/2018   |   2      |             now update the formula to bring in the second item for the sequence of dates --------------------------------------------------- |         |  1/2/2018   |          |    --------------------------------------------------- |         |  1/3/2018   |   4      |    --------------------------------------------------- |         |  etc.. 365 days --------------------------------------------------- | etc ...     =Sheet1!A:A I can bring in the name from the source list, but I have to copy it down for the number of dates.  How can I have it match the number of dates?  and be dynamic to update as dates/values are added Edit: trying to use split and rept to count the number of times to repeat, then transpose.. =transpose(rept(split(arrayformula(concatenate(Sheet1!A:A&amp;"1")),"1",True, True),COUNTA(Sheet1!I1:ZZ1)))  =TRANSPOSE(Sheet1!$C$1:$Z$1) I can use transpose to get the dates/values, but it's fixed range.  How can I make it dynamic to import dates as they are added?
120659
Say I have the following query:  SELECT *  FROM AppDetails, AppTags  WHERE AppDetails.AppID = '1'    AND AppDetails.AppID = AppTags.AppID   Which gives the following results:  AppID    AppName        AppType    Tag 1        Application1   Utility    Test1 1        Application1   Utility    Test2 1        Application1   Utility    Test3   How can I modify the query to return something like this:  AppID    AppName        AppType    Tags 1        Application1   Utility    Test1,Test2,Test3
21671
I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations and I have the following:  TABLE office_settings column office_id (bigint not null) column setting_value (NVARCHAR(500) NOT NULL)  office_id    setting_value -------------------------------------------- 4             0.2 4             0.2 4             2 4             3 5             0.2 5             0.2 5             2 5             3   I'd like to convert this using PIVOT (assuming that's the right tool for the job) to this, basically "grouping by" the office_id column, so:  office_id    value_1    value_2    value_3   value_4 ------------------------------------------------------     4         0.2         0.2         2         3     5         0.2         0.2         2         3   I should also mention that I need to CAST the setting_value column values to DECIMAL(4,2) in the process of this - in case that has any bearing on the T-SQL required to accomplish it.  Any ideas?
31050
I need to run a query like:  SELECT p.id, p.name,         (SELECT name            FROM sites s           WHERE s.id = p.site_id) AS site_list   FROM publications p   But I'd like the sub-select to return a comma separated list, instead of a column of data.  Is this even possible, and if so, how?
50351
I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.  INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "London Office"); INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office");   Can I insert all 4 rows in a single SQL statement?
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.
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?
11546
Been looking to pivot rows (progress_check) into columns check 1, check 2 etc... No sum or totals required, just to display the results hopefully... Can anyone help, Thanks Ad
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.
89827
In my system, I want each user to have an own id sequence for his articles.   So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the outline of such table:       +------+----------+---------+     | id   | article  | user_id |     +------+----------+---------+     | 1    |some notes| 1       |     +------+----------+---------+     | 2    |some notes| 1       |     +------+----------+---------+     | 3    |some notes| 2       |     +------+----------+---------+      | 4    |some notes| 2       |     +------+----------+---------+     | 5    |some notes| 3       |     +------+----------+---------+   But I want to generate the ids in this table in the following way:       +------+----------+---------+     | id   | article  | user_id |     +------+----------+---------+     | 1    |some notes| 1       |     +------+----------+---------+     | 2    |some notes| 1       |     +------+----------+---------+     | 1    |some notes| 2       |      +------+----------+---------+     | 2    |some notes| 2       |     +------+----------+---------+     | 3    |some notes| 2       |     +------+----------+---------+     | 1    |some notes| 3       |     +------+----------+---------+   So, when a new user is being created in the associated user table, an id sequence for his articles will start from 1.   Is there any generally accepted approach to solve this problem?