w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Display multiple rows and column values into a single row, multiple column values
Since you are using SQL Server there are several ways that you can transpose the rows of data into columns. Aggregate Function / CASE: You can use an aggregate function with a CASE expression along with row_number(). This version would require that you have a known number of values to become columns: select id, name, max(case when rn = 1 then employer end) employer1, max(case when rn = 1 then IncomeType end) IncomeType1, max(case when rn = 1 then Amount end) Amount1, max(case when rn = 2 then employer end) employer2, max(case when rn = 2 then IncomeType end) IncomeType2, max(case when rn = 2 then Amount end) Amount2, max(case when rn = 3 then employer end) employer3, max(case when rn = 3 then IncomeType end) IncomeType3, max(case when rn = 3 then Amount end) Amount3

Categories : SQL

Selecting multiple values of other column basing on distinct values of column on same table
You can use the LISTAGG function as of Oracle 11g R2. e.g. SELECT deptno , LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;

Categories : SQL

count occurrence of repeated values in a column with multiple column in mysql
Are you trying to do a simple aggregation? select col1, col2, count(*) from t group by col1, col2; Or are you trying to append this value to each row: select t.*, tsum.cnt from t join (select col1, col2, count(*) as cnt from t group by col1, col2 ) tsum on tsum.col1 = t.col1 and tsum.col2 = t.col2;

Categories : Java

Query To Return Multiple Values From A Single Column Based On Value Of Another Column
You can join to the same table twice. select startDt.EntryDate as StartDate, endDt.EntryDate as EndDate from table startDt inner join table endDt on startDt.id = endDt.id where startDt.ChecklistDay = 1 and endDt.CheckListDay = 10

Categories : SQL

Trying to find multiple values from one column and group by another column - can't seem to figure it out
I believe it's because you can't group by partial matches? You are not “grouping” by the value column, but by company_id instead, so this should not matter. having count(distinct value) = 2 I think the DISTINCT here is your problem – here partial matches could be problematic. Try having count(company_id) = 2 instead (or maybe even >= 2 if there might be more results and you still want to catch those company ids) – every row in your search table that contains a match will be selected, so counting how often each company id occurs should do the trick. Edit: OK, so the problem is if a company has two hits for mall, f.e. mall and shopping mall, but none for grocery, you don’t want to find it, but only if mall and grocery are found at least once. Well, then let’

Categories : Mysql

Displaying multiple values from same column in one row in SQL
Your code should look like: DECLARE @myVariable varhcar(200); SELECT @myVariable = COALESCE(@myVariable + ',', '') + stringvalue from TABLE WHERE COLUMN1 = SOMEVARIABLE and ISSUE = COLUMN2; SELECT @Headtext = 'name' + @myVariable; Another way to concatenate the variables is: SELECT @myVariable = stuff((select ',' + stringvalue from TABLE WHERE COLUMN1 = SOMEVARIABLE and ISSUE = COLUMN2 for xml path ('') ), 1, 1, ''); SELECT @Headtext = 'name' + @myVariable;

Categories : SQL

Return multiple values in one column
Considering RDBMS as SQL SERVER 2008 select E.Ename,E.Eage,E.Eadd,E.Ephone,D.dname into Table1 from Employee E left join Deparment D on E.Empid=D.fkEmpid select t1.[Ename], t1.[Eage], t1.[Eadd], t1.[Ephone], STUFF(( SELECT ', ' + t2.dname FROM Table1 t2 WHERE t2.Ename = t1.Ename AND t2.Eage=t1.Eage AND t2.Eadd=t1.Eadd AND t2.Ephone=t1.Ephone FOR XML PATH ('')) ,1,2,'') AS Names FROM Table1 t1 GROUP BY t1.Ename,t1.[Eage], t1.[Eadd], t1.[Ephone]; SQL FIDDLE

Categories : SQL

multiple row column values into single row
It is a pain to eliminate the [col-B] on the second line. The following formats the datetime's the way you seem to want them: select [col-B], (convert(varchar(19), [col-C] + [col-d], 121) + ' - ' + right(convert(varchar(19), [col-E], 121), 8) ) col from t

Categories : SQL

Conversion of multiple values within one column
Here is procedure: DECLARE CURSOR c (p_id NUMBER) IS SELECT NAME||' - '||SITE FROM TABLE2 WHERE ID = p_id; l_tsv VARCHAR2(1000) := '23:45:56'; l_item NUMBER; lc_t VARCHAR(200); lc_result VARCHAR2(4000); BEGIN FOR i IN 1 .. LENGTH(l_tsv) - LENGTH(REPLACE(l_tsv, ':', '')) + 1 LOOP l_item := REGEXP_SUBSTR(l_tsv, '[^:]+', 1, i); OPEN c (l_item); FETCH c INTO lc_t; CLOSE c; lc_result := lc_result ||', '||lc_t; END LOOP; lc_result := SUBSTR(lc_result,3); dbms_output.put_line(lc_result); END; UPDATE Function: CREATE OR REPLACE FUNCTION some_name(l_tsv VARCHAR2) RETURN VARCHAR2 IS CURSOR c (p_id NUMBER) IS SELECT NAME||' - '||SITE FROM TABLE2 WHERE ID = p_id; l_item NUMBER; lc_t VARCHAR(200); lc

Categories : SQL

match one column value with multiple values in mysql
use a n:n Table: Products Table products id | name city table cities id | name Mapping (unique constraint over BOTH columns, not single constraints): shipping_information product_id | city_id Then you can easily select all available cities for a product, or select all products, which are shipped to a certain city. All available cities for product 4: SELECT * FROM cities, shipping_info WHERE cities.id = shipping_info.city_id AND shipping_info.product_id = 4 All available products for city 3: SELECT * FROM products,shipping_info WHERE shipping_info.id = 3 AND shipping_info.product_id = products.id

Categories : PHP

Join table on XML column with multiple values
select fgz_leveranciers.*, xmldata.i from fgz_leveranciers left join ( Select x.t.value('.','int') i from fgz_merken cross apply lst_leveranciers.nodes('lst_leveranciers/lst_leveranciers/value') as x(t) ) xmldata on fgz_leveranciers.i = xmldata.i

Categories : SQL

Combining values from multiple columns into one column
Your existing query is returning all levels in the join, because the only level selection is occurring in the where clause - if any level matches the criteria, then all levels are selected. Instead, try: select LevelTypeId, LevelId from ( select a.ID as [1], b.ID as [2], c.ID as [3], d.ID as [4] from A a left join B b on a.ID = b.A_ID and @levelTypeId >= 2 left join C c on b.ID = c.B_ID and @levelTypeId >= 3 left join D d on c.ID = d.C_ID and @levelTypeId >= 4 where (@levelTypeId = 4 and D.ID = @levelId) or (@levelTypeId = 3 and C.ID = @levelId) or (@levelTypeId = 2 and B.ID = @levelId) or (@levelTyp

Categories : SQL

Multiple joins to get the same lookup column for different values
correlated subqueries might be the way to go: SELECT r.event_id , r.datetime_id , (select lookup1.lookup_desc from lookup_table lookup1 where lookup1.lookup_id = r.lookup_1) as desc_1 , (select lookup2.lookup_desc from lookup_table lookup2 where lookup2.lookup_id = r.lookup_2) as desc_2 , (select lookup3.lookup_desc from lookup_table lookup3 where lookup3.lookup_id = r.lookup_3) as desc_3 , (select lookup4.lookup_desc from lookup_table lookup4 where lookup4.lookup_id = r.lookup_4) as desc_4 FROM Raw_Event_data r WHERE r.event_id = 1 ;

Categories : SQL

find where column could have multiple values, rails
What you are trying to do is only work with IN You can do as follow; <%= Post.where("category LIKE ? OR commentary LIKE? OR commentary LIKE?", 20, "%mom%", "%mother%"]).count %> This will generate the following sql query; SELECT Count(*) FROM `posts` WHERE (category like "20" OR commentary like "%mom%" OR commentary like "%mother%" )

Categories : Ruby On Rails

replace multiple values in a column for a single one
This would maintain your data structure (a factor like you guessed): x <- levels(a$T) levels(a$T) <- ifelse(x %in% c("E","S","T"), "AB", x) or levels(a$T)[levels(a$T) %in% c("E","S","T")] <- "AB" Edit: if you have many such replacements, it is a little more complicated but not impossible: from <- list(c("E","S","T"), c("J", "K", "L")) to <- c("AB", "YZ") find.in.list <- function(x, y) match(TRUE, sapply(y, `%in%`, x = x)) idx.in.list <- sapply(levels(a$T), find.in.list, from) levels(a$T) <- ifelse(is.na(idx.in.list), levels(a$T), to[idx.in.list]) a$T # [1] AB F G H I YZ YZ YZ M N O P Q R AB AB # Levels: AB F G H I YZ M N O P Q R

Categories : R

Getting Different column values from Multiple tables in SQL Server
use union all: select id, sal as [sal/Wages] from table1 union all select id, wages as [sal/Wages] from table2 order by 1 Note that I've used union all and not union, because union removes duplicates from resulting set. Sometimes it might be useful, but not in your case, I think.

Categories : SQL

retrieving rows with multiple column values sqlite
Did you try something like this? mCursor = db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_DESC, KEY_EVENT, KEY_DATE}, KEY_DESC + " like '%" + inputText + "%' or "+KEY_DATE+"="+WHAT_EVER, null, null, null, null, null);

Categories : Android

Append a column values from multiple rows into a string
To achieve your goal you need to use STUFF() and GROUP BY SELECT q.Question + ';' + STUFF((SELECT ';' + Answer FROM Answers WHERE Question_Id = q.Question_Id ORDER BY 1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'') result FROM Questions q LEFT JOIN Answers a ON q.Question_Id = a.Question_Id GROUP BY q.Question_Id, q.Question Sample output: | RESULT | ----------------------------------------------- | Can You Repeat The Question;Maybe;No;Yes | | Can You Really Repeat The Question;Sure;Yes | Here is SQLFiddle demo If there is a possibility of having questions without answers being assigned yet and you want to properly show them you may use I

Categories : SQL

How to add a column to a dataframe with values of another based on multiple conditions
DF1$Z <- sapply(1:nrow(DF1), function(i) DF2$Z[DF2$X==DF1$X[i] & DF2$Y==DF1$Y[i]]) seems to be taking roughly a quarter of the time of your for loop. I created DF1 with 300 each reps, and my function took ~2secs to run; your loop with subset took ~8secs to run, and repackaging your loop into an sapply it took ~5secs to run.

Categories : R

Iterating through column values for multiple rows mysqli
Lose the foreach and use $row, not $result while ( $row = $result->fetch_assoc() ){ $tablebuild .= "<tr><td>"; $tablebuild .= $row['product_id']; $tablebuild .= "</td><td>"; $tablebuild .= $row['cost']; $tablebuild .= "</td><td>"; $tablebuild .= $row['image']; $tablebuild .= "</td></tr>"; }

Categories : PHP

SQL query normalize multiple row values into one row one column field
Why not just join to a table that codes the benefits to an integer (Life -> 10000, Medical -> 1000, ..., education -> 1; and then Sum the benefit code integer; convert the sum to a string; prepend the string '0000' and take the right-most 5 characters. Update: select EmployeeID, right('0000' + convert(varchar(5),sum(map.value)),5) from ( select value=10000, benefit = 'Lif' union all select value= 1000, benefit = 'Med' union all select value= 100, benefit = 'Uni' union all select value= 10, benefit = 'Opt' union all select value= 1, benefit = 'Edu' ) map join blah blah group by EmployeeID

Categories : SQL

Database design query regarding column that can have multiple values
Yes, that would be the correct way to implement this. If the solution_ans are going to be a finite list of solutions then that is all that you would need in the Solution_table. If the answers are going to be dynamic, I would think about having a foreign key in the solution_table that links to the stu_id. This way when you are looking at the data you can easily connect each answer to the forum post/question I would look into normalization of databases to understand why this is a good solution for this particular problem.

Categories : Database

Is it possible to have a MySQL column containing multiple values as foreign keys?
If you do not wish to make up a "middle man" table for linking the two tables you can have a comma separated value in the field, you would just need to use the find_in_set mysql function when doing queries USING find_in_set SELECT log.user_id, log.activity_id, log.tags, GROUP_CONCAT(tags.name) as taggedNames //This assumes there is a field called `name` in tags table FROM log LEFT JOIN tags ON FIND_IN_SET(tags.tag_id,log.tags) GROUP BY log.activity_id GROUP_CONCAT will group together a field and separate them by a deliminator, default is ,

Categories : Mysql

Calculating totals with multiple occurrences of the same ID and values in same column
You can start with SUM and GROUP BY select userid, sum(hrsworked) hrsworked from TableName group by userid

Categories : Sql Server

How to get the latest records of the multiple values of same column in PostgreSQL?
You can use a window function to get the latest row for each URL: select * from ( select url, update_time, dns_time, row_number() over (partition by url order by update_time desc) as rnk from dns_lookup_table ) as t where rnk = 1 SQLFiddle example: http://sqlfiddle.com/#!12/fbd38/1 Edit You can also use something like this: select * from dns_lookup_table lt join ( select url, max(update_time) as latest_time from dns_lookup_table group by url ) as mt on mt.latest_time = lt.update_time and mt.url = lt.url;

Categories : SQL

Is there a more optimal way to retrieve multiple values from an XML Column in SQL Server
Try to use XQuery statement FLWOR. It should reduce parsing time. See http://msdn.microsoft.com/en-us/library/ms190945.aspx

Categories : Sql Server

TSQL: Join columns, but rows in one column have multiple values
try select tab2.* -- whatever from t1 tab1 inner join t2 tab2 on ( ';'||tab2.col2||';' like '%;'||tab1.col1||';%' ) ; the extra affixed ; characters serve to avoid disjunctions in the join condition.

Categories : Sql Server

multiple where clause failed - assigning same string for different column values
Try the below code: SQLiteDatabase db=openOrCreateDatabase("MYDB", MODE_PRIVATE, null); String table="myTbl"; String whereClause = "Number = ? AND Name = ?"; String[] whereArgs = new String[] { strNum }; whereArgs[1] = new String[] { strNname }; db.delete(table, whereClause, whereArgs); db.close(); In the above code, i'm deleting entries with multiple where clause.

Categories : Android

Custom format mysql query and multiple values in one column
What you are looking for is group_concat(). I think it would be something like this: Select zp.products_id as id, zpd.products_name as name, group_concat(zcd.categories_name separator '@') as categories, products_price_w as cost, products_price as price, zp.products_date_added as date_created, zp.products_image as thumbnail FROM `zen_products` as zp INNER JOIN `zen_products_description` as zpd ON zp.products_id = zpd.products_id INNER JOIN `zen_products_to_categories`as zptc ON zp.products_id = zptc.products_id INNER JOIN `zen_categories_description` as zcd on zptc.categories_id = zcd.categories_id WHERE zp.products_status = 1 group by zp.products_id ORDER BY zp.products_id ASC;

Categories : Mysql

Define column range as multiple values for INDEX function
You only have to show the "lower bound" for each range and then use MATCH with 1 as 3rd argument, e.g. with age ranges in B1:E1 as 1,6,11,16 then salary in A2:A5 as 0, 10000, 20000, 30000 then use this formula =INDEX(B$2:E$5,MATCH(G2,A$2:A$5,1),MATCH(H2,B$1:E$1,1)) where G2 and H2 contain the specific salary and age to lookup respectively. e.g. looking up 13 and 23000 will match 13 with 11 in D1 and 23000 with 20000 in A4 and you get the value from D4 ....yes, I know a 13 year old is unlikely to have a salary of 23000......!

Categories : Excel

MySQL query to get first unique values of one column on multiple tables
This is a fix I managed to do SELECT tickets.threadId, ticketThreads.threadSubject, tickets.ticketCreatedDate, ticketThreads.threadCreatedDate, threadStatus.threadStatus, users.name FROM tickets INNER JOIN ticketThreads ON tickets.threadId = ticketThreads.threadId INNER JOIN threadStatus ON ticketThreads.threadStatus = threadStatus.id INNER JOIN user

Categories : Mysql

SQL Server : selecting row with column that contains multiple comma delimited values
Storing lists as a comma delimited data structure is a sign of bad design, particularly when storing ids, which are presumably an integer in their native format. Sometimes, this is necessary. Here is a method: select * from a join b on ','+b.column_1+',' like '%,'+cast(a.column_1 as varchar(255))+',%' This will not perform particularly well, because the query will not take advantage of any indexes. The idea is to put the delimiter (,) at the beginning and end of b.column_1. Every value in the column then has a comma before and after. Then, you can search for the match in a.column_1 with commas appended. The commas ensure that 10 does not match 100. If possible, you should consider an alternative way to represent the data. If you know there are at most two values, you mi

Categories : SQL

SQL Merging Tables Based On Multiple Matching Column Values
My way would be to 1st: create a view for replacing rows and another with the correct result set that should be appended like --view for update create view ChangeTable1 as select table2.map, table2.authid,table2.name, table2.date, table1.map as t1map, table1.authid as t1authid...from table1 to inner join table2 on table1.map=table2.map and table1.athid=table2.autid where table1.time>table2.time -- view for appending create view Add2Table1 select table2.map, table2.authid,table2.name, table2.date... from table2 where concat(table2.map, table2.authid) not in (select concat(table1.map, table1.authid) from table1) -- update statement based on first view ChangeTable1 update ChangeTable1 set t1date=date, t1somevalue=somevalue...... -- insert Statement based an second view Add2Table1 i

Categories : Mysql

adding multiple ranges of values from a column in the ifelse statement in R
Assuming you have data frame like that: d <- data.frame(position=round(runif(100, 1, 70000))) You can combine logical operators: d$status <- ifelse(( d$position >= 1000 & d$position <= 10200) | (d$position >= 17800 & d$position <= 21000) | (d$position >= 43000 & d$position <= 54000), 'INTRON', 'EXON') or you can use nested ifelse: d$status <- ifelse(d$position >= 1000 & d$position <= 10200, 'INTRON', felse(d$position >= 17800 & d$position <= 21000, 'INTRON', ifelse(d$position >= 43000 & d$position <= 54000, 'INTRON', 'EXON')))

Categories : R

How to select rows with multiple specific column values in single query?
Try SELECT * FROM A WHERE (itemNo = '1' AND colorNo = '4') OR (itemNo = '2' AND colorNo = '70') OR (itemNo = '3' AND colorNo = '9') or you can also do this SELECT * FROM A WHERE (itemNo, colorNo) IN ((1, 4),(2, 70),(3, 9)) Output: | ITEMNO | COLORNO | -------------------- | 1 | 4 | | 2 | 70 | | 3 | 9 | Here is SQLFiddle demo

Categories : Mysql

Count the number of duplicate values on one column for specific values of another column in MySQL?
Here is one way to do it, by joining to the "111" customer values and then aggregating: select t.customer, count(t111.variable) as "count" from t left outer join (select t.* from t where customer = 111 ) t111 on t.variable = t111.variable group by t.customer; I think the above is clear on what it is doing. However, you can eliminate the subquery (which is nice in MySQL): select t.customer, count(t111.variable) as "count" from t left outer join t t111 on t.variable = t111.variable and t111.customer = 111 group by t.customer;

Categories : Mysql

How to find out erroneous values in one column based on values in another column in R?
the %in% function may come in handy. It will throw an FALSE for those cases that are in the first but not the second set E.g. DF$master_id %in% DF$id id is the subset of master_id, so master_id values without a counterpart will get a FALSE or, to see how it works run (from R help file) 1:10 %in% c(1,3,5,9)

Categories : R

R matrix: unique by one column, add the values on other column and concatenate the values on other
If you have some columns that you want to treat as numeric (adding the values together) and some that you want to treat as character (pasting them) then a matrix isn't the right structure for your data. You should be using a data frame. Using plyr: library(plyr) ddply(data.frame(a), .(pattern), summarise, id=paste(id, collapse="-"), size=sum(as.numeric(size)))

Categories : R

Sum values in one mysql column and group by values of other column (using php foreach)
MySQL query $query_select_all =" SELECT TransactionPartnerName, SUM(Amount) FROM 2_1_journal GROUP BY TransactionPartnerName"; Then $sql = $db->prepare($query_select_all); $sql->execute(); $sql = $sql->fetchAll(); With print_r($sql); get Array ( [0] => Array ( [TransactionPartnerName] => name one [0] => name one [SUM(Amount)] => 154.00 [1] => 154.00 ) [1] => Array ( [TransactionPartnerName] => name three [0] => name three [SUM(Amount)] => 10.00 [1] => 10.00 ) [2] => Array ( [TransactionPartnerName] => name two [0] => name two [SUM(Amount)] => 9.00 [1] => 9.00 ) ) So far seems ok. Then foreach <?php foreach ($sql as $i1 => $row1) { ?> <tr> <td width="90px"><div style="width:90px;"> <?php echo ht

Categories : PHP

How to increment the values in one column depending upon the dynaimic values of other column
Put this in cell B2: =IF(A2="na","",MAX($B$1:B1)+1) Or you could put this in B1, should give the same results: =IF(A2="na","",COUNTIF($A$2:A2,"<>na"))

Categories : Excel



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.