How I can query the xml in order to obtain the following result? I am completely new on using XQuery. XML sample: And the result: idAsig B1_1 B1_2 B1_3 B1_4 B1_5 1 NULL NULL NULL NULL NULL 2 1 0 N 8 4848 2 52 0 N 8 625 3 1 0 N 8 589 3 39 0 N 8
How can I write a SQL query to calculate the quantity of components sold with their parent assemblies? (Postgres 11/recursive CTE?)
My goal To calculate the sum of components sold as part of their parent assemblies. I’m sure this must be a common use case, but I haven’t yet found documentation that leads to the result I’m looking for. Background I’m running Postgres 11 on CentOS 7. I have some tables like as follow…
Oracle hierarchical queries data
The link gives a good example for overview on how to use Oracle hierarchical queries. I was trying to generate the below combination of data with the example table tab1 given in the link but struck …
Sequelize – Include based on specific attribute
I have a model defined as follows: I’m trying to use Sequelize to load all game object and include the User with the id equal to the playerId field. The problem is I have two attributes (leaderId, playerId) which reference the User model so using include as follows does not work: Is there a way to speci…
Convert utc string to date SQL
I’ve a column sample_date in form of string as 200912301111230000000000 (UTC Time).How can I convert it from string to datetime in form of yyyymmdd using SQL select statement? Answer As you only want yyyymmdd, which is the first 8 chacters of your string, it is enough to simply use LEFT I added the ST_T…
count(distinct request_ip) GROUP by date
I have the following SQL, which I am using against AWS ELB logs stored in S3 with AWS Athena. SELECT count(distinct request_ip) AS count, request_ip, DATE(from_iso8601_timestamp(timestamp)) AS date …
Dynamic TSQL Pivot without aggregate function
I have a table like this (‘ExternalPersonRelationTable’) PersonId SubjectCode 4187 3 4187 278 4429 3 4429 4 4463 99 4464 174 4464 175 I want to rotate the data so that every person in the table gets a column and a TRUE/FALSE value for each subject code, i.e. a table like this: Code 4187 4429 4463 …
Spark SQL : filtering a table on records which appear in another table (two columns)?
I have several tables, and I would like to filter the rows in one of them, based on whether two columns are present in another table. The data on each table is as follows Table1 : one hash can be associated to several articles ; one article can be associated to several hashes User Hash Article Name Hash1 Arti…
Page numbers & total result count in a cursor based pagination
Though it may sound like a stupid question, sometimes it is necessary to show page numbers (and also the last page). What is the best way to calculate total row counts and also calculate page numbers (by providing a page query to return specific page) in a cursor based pagination? Also are all these possible …
SQL union grouped by rows
Assume I have a table like this: col1 col2 col3 col4 commonrow one two null commonrow null null three How to produce a result to look like this: col1 col2 col3 col4 commonrow one two three Thanks Answer like this, you can group by col1 and get the maximum in each group: