google sheets – Merge rows into a single line while keeping data

I have a Google sheet that logs attendance using a simple x to tell when someone has come in. Unfortunately there is a lot of misspelling of peoples names and so there’s a bunch of duplicate data in a sheet that has hundreds of names and dates. How can I merge these duplicate rows into one while preserving the data? Here’s example of what I want to do.

Name 1/1 1/2 1/3 1/4 1/5
Anthony, Mark X X X
Anthony, Marc X X x

to:

Name 1/1 1/2 1/3 1/4 1/5
Anthony, Mark X X X X

The built-in merge only saves the person’s name. Sometimes the x is uppercase, sometimes lower.

mysql – Transform a varchar field with #-delimiters into multiple rows

I am working with a database where some data (integer values representing selected options of a dropdown menue) is saved as a string (varchar) inside only one table column. In products_table (let’s say), there is a product_id column mapped to a selected-options column. In the latter, data is saved as #3#9#15# where 3, 9 and 15 are IDs of dropdown menue options that have been selected by a user. There is another table (let’s say option_table) where each of these options with IDs 1 to 15 are given more attributes (like a label and a weight).

For statistical reasons, I want to create a MySQL view with the columns selected option and number of products (the number of products where this option has been selected). To get there, I need the single values from #3#9#15#, that is, explode the string somewhere on the way.

Now I read that this form of saving data is not encouraged, which is why there is no built-in explode/split function for strings in MySQL. But I cannot change the data format, which has been determined inside a huge software. I didn’t make this design choice, but I have to somehow work with it.

Working with the built-in function substring_index() is no choice because the number of selected options saved in this field varies, from 0 to 15. Also, the maximum number is not fix, as new options may be created from time to time.

I tried writing an own procedure that will take one number from #3#9#15# (for example, by repeatedly trimming the # delimiters) and create a new row with it. The procedure should access the value with a SELECT statement, create a new view with CREATE OR REPLACE VIEW, and then insert a new row with only the first option (3) option with INSERT INTO `viewname`(`product_id`,`option`) VALUES(@productID, SUBSTR(@options,2,1)); where @options == #3#9#15# for example. I tried writing the procedure for only the first option at the beginning, but it already failed at this stage.

Other ideas I had included trying to convert the string @options into a SET datatype, so I can use find_in_set() function. I also read this question and this article but found them very hard to understand.

Having only basic knowledge of MySQL, is there some way for me to create this view? I would be very thankful for any hint in which direction I should go on researching.

What’s the formula to select non matching rows by comparing rows in google sheets query?

You can get those results with an array formula that massages the data based on whether a row is Debit or Credit, and aggregate the massaged data with a query() using the group by clause. To weed out rows with a zero result, wrap the query() in another query(), like this:

=arrayformula( 
  query( 
    query( 
      { K1:L, if(L1:L = "Credit", -M1:M, M1:M) }, 
      "select Col1, sum(Col3) 
       where Col1 is not null 
       group by Col1", 
      1 
    ), 
    "where Col2 <> 0", 
    1 
  ) 
)

Note that this formula uses open-ended references like K1:L that will evaluate rows all the way to the bottom of the sheet. To limit it to some fixed number of rows, use K1:L9 and so on.

google sheets – How to apply formula to new rows that are generated by script

google sheets – How to apply formula to new rows that are generated by script – Web Applications Stack Exchange

query – Finding child rows that match multiple conditions

I’m sure this has been answered a hundred times, but I’ve no idea what to search for so hopefully somebody can point me in the right direction.

Given this simple query, I can find all projects that have either a red task or a blue one:

SELECT * FROM Projects P
JOIN Tasks T ON T.taskId = P.taskId
WHERE T.color = 'blue' OR P.color = 'red'

But I’m trying to find all tasks that have both a red task and a blue one. How can I do this? Ideally the query should be scalable so I can match lots of combinations succinctly.

database design – Insert JSONL with two columns of comma separated fields into separate rows in mysql

I have json datas in the format as shown below.

**the number of objects under ‘images'(VIEWS, ROOMS, etc.) varied for each JSON object. Not all objects have VIEWS…POOL, it may only have ROOMS.

    "propertyId":{"ea":"12345678","h":"","vo":""},
    "thumbnail":{"width":1000,"height":500,"link":"xxx.jpg"},
    "hero":{"title":"Featured Image","width":1000,"height":669,"link":"yyy.jpg"},
    "images":{
       "VIEWS":(
        {"title":"View from Property","width":1000,"height":666,"link":"a1.jpg"},
        {"title":"View from Property","width":1000,"height":666,"link":"a2.jpg"}
       ),
        "ROOMS":(
            {"title":"Room","width":1000,"height":669,"link":"b1.jpg"},
            {"title":"Room","width":1000,"height":662,"link":"b2.jpg"}
       ),
        "EXTERIOR":(
            {"title":"Terrace","width":1000,"height":666,"link":"c1.jpg"},
            {"title":"Terrace","width":1000,"height":666,"link":"c2.jpg"}
       ),
        "AMENITIES":(
             {"title":"Property Amenity","width":1000,"height":666,"link":"d1.jpg"}
       ),
        "POOL":(
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e1"},
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e2.jpg"}
    )}
    }

I wanted to insert the following data into the table with the following format in mysql database 8.0.
enter image description here

After searching through the old topics, I was able to use the select statement below to display the format above. However, I’m confused as to it how works when using INSERT.

  SELECT t.HotelID, t.hero_url, t.hero_title,SUBSTRING_INDEX(SUBSTRING_INDEX(t.media_title, ',', n.n), ',', -1) media_title, SUBSTRING_INDEX(SUBSTRING_INDEX(t.images, ',', n.n), ',', -1) images
  FROM hotel_sample.property_photos t CROSS JOIN 
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n 
   ) n
 WHERE n.n <= 1 + (LENGTH(t.media_title) - LENGTH(REPLACE(t.media_title, ',', '')))
 ORDER BY HotelID

Would appreciate if anyone could show me how I could insert the above JSON data into the table. (MYSQL 8.0)

CREATE TABLE `property_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `HotelID` varchar(10) DEFAULT NULL,
  `hero_url` varchar(255) DEFAULT NULL,
  `hero_title` text,
  `category` varchar(30) DEFAULT NULL,
  `media_url` varchar(255) DEFAULT NULL,
  `media_title` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY (`id`)

sql server – Eliminate extra rows that intersect

I have a table of geometry values where some rows intersect other rows.

I need a list of rows where the geometry overlaps other rows, but I’d like the list to be as concise as possible.

Here’s the setup:

USE tempdb;
DROP TABLE IF EXISTS dbo.t;

CREATE TABLE dbo.t
(
    n varchar(100) NOT NULL
    , i geometry NOT NULL
);

INSERT INTO dbo.t (n, i)
VALUES ('poly1', geometry::STGeomFromText('POLYGON ((1 2, 1 4, 1 5, 4 6, 1 2))', 4326))
     , ('poly2', geometry::STGeomFromText('POLYGON ((1 2, 1 3, 2 5, 4 6, 1 2))', 4326))
     , ('poly3', geometry::STGeomFromText('POLYGON ((7 9, 8 7, 9 6, 7 9))', 4326))

SELECT t1.n
    , t2.n
FROM dbo.t t1
    INNER JOIN dbo.t t2 ON t1.i.STIntersects(t2.i) = 1
WHERE 
    t1.n <> t2.n;

The output looks like:

n n
poly2 poly1
poly1 poly2

However, I’d like only a single row for brevity. i.e. because poly1 overlaps poly2 and poly2 overlaps poly1 I’m getting two rows returned where I’d like only one, as in:

google sheets – Help Please? Pivot Table doesn’t seem to be correct way to fix? Add rows based on data type and return value?

I’ve added a new sheet (“Erik Help”) with two options for you. However, here I will only post the option that matches your exact request. In B1:

=ArrayFormula({"ID","Type","Value";QUERY(SPLIT(FLATTEN(FILTER(Sheet1!A2:A,Sheet1!A2:A<>"")&"|"&Sheet1!B1:E1&"|"&FILTER(Sheet1!B2:E,Sheet1!A2:A<>"")),"|"),"Select * Where Col3 Is Not Null")})

This formula creates the three headers (which you can change as you like), and then does the following:

1.) concatenate all non-blank entries in A2:A, a pipe symbol, each header in B1:E1, another pipe symbol and each element of the B2:E grid where A2:A is not blank. This will form a grid of results the same size as B2:E where A2:A is not blank, each in the format X|Y|Z.

2.) this grid of results is FLATTENed into one column.

3.) that column of results is split into three columns at the pipe symbols.

4.) QUERY weeds out any entries where the value is null.

8 – Can’t get distinct rows from taxonomy view

Drupal 8 has a problem with creating distinct views, and the distinct views module hasn’t been ported yet. I’ve been researching this today, and haven’t found a good solution. I wrote a custom hook in a module that I ‘d hoped would fix it, but it hasn’t. I have s pretty simple content type that has a taxonomy reference, and this is an attachment that gathers the unique terms for an index.

The SQL generated for my view looks like this:

SELECT DISTINCT taxonomy_term_field_data_node__field_faq_category.name AS taxonomy_term_field_data_node__field_faq_category_name, node_field_data.nid AS nid, taxonomy_term_field_data_node__field_faq_category.tid AS taxonomy_term_field_data_node__field_faq_category_tid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_faq_category} node__field_faq_category ON node_field_data.nid = node__field_faq_category.entity_id AND node__field_faq_category.deleted = '0'
LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_faq_category ON node__field_faq_category.field_faq_category_target_id = taxonomy_term_field_data_node__field_faq_category.tid
WHERE (node_field_data.type IN ('pfaq')) AND (node_field_data.status = '1')
ORDER BY taxonomy_term_field_data_node__field_faq_category_name ASC

I get a result of all rows. So I made this handler. It creates a copy of $view->result with only unique terms, then copies it back.

function wdocs_site_views_post_execute(ViewExecutable $view) {
  if (($view->id() =='faq') && ($view->current_display == 'attachment_1')) {
    $row_ids = ();
    $distinct_results = ();
    foreach ($view->result as $row) {
      $cat = $row->_entity->get('field_faq_category')->getValue();
      $tid = $cat(0)('target_id');
      if (!in_array($tid, $row_ids)) {
        $row_ids() = $row->taxonomy_term_field_data_node__field_faq_category_tid;
        $distinct_results() = $row;
      }
    }
    $view->result = $distinct_results;
  }
}

Seems like it should do the trick, but now I’m only seeing the first two rows. A row is generated in the HTML for each value, but after the first two rows, the span.field-content is null. Looking at the returned values in my handler, they all look like they should render. As a test, I tried adding this right before the $distinct_results array is assigned to $view->result:

$distinct_results(1) = $distinct_results(2);

Still nothing displayed. However, if I assign the value of the first row to the second row, the first two rows are displayed identically:

$distinct_results(1) = $distinct_results(0);

Seems switching the data for the rows in the result should work, this has me baffled. Any suggestions on how to deal with distinct entries would be appreciated.

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive Proxies-free.com New Proxy Lists Every Day Proxies123