The currently accepted answer by bhamby is certainly correct, but it’s worth checking if using several correlated subqueries is much slower than a single group by (hint: it most likely is): SELECT A.ItemID, MAX(CASE WHEN A.Item = ‘Meeting’ THEN Value END) AS Meeting, MAX(CASE WHEN A.Item = ‘Advise’ THEN Value END) AS Advise, MAX(CASE WHEN … Read more
You can use : tidyr::pivot_longer(dat, cols = -id, names_to = c(‘.value’, ‘brand’), names_sep = “_”) # id brand Q1r1 Q1r2 # <chr> <chr> <dbl> <dbl> #1 A pepsi 1 1 #2 A cola 0 0 #3 B pepsi 0 1 #4 B cola 0 1 #5 C pepsi 1 1 #6 C cola 1 1
What you are trying to do is called a PIVOT. There are two ways to do this, either with a Static Pivot or a Dynamic Pivot. Static Pivot – is where you will hard-code the values of the rows to transform to columns (See SQL Fiddle with Demo): select ws_id, start_date, end_date, IsNull([100.00], 0) [100.00], … Read more
If you are using Oracle 10g, you can use the DECODE function to pivot the rows into columns: CREATE TABLE doc_tab ( loan_number VARCHAR2(20), document_type VARCHAR2(20), document_id VARCHAR2(20) ); INSERT INTO doc_tab VALUES(‘992452533663’, ‘Voters ID’, ‘XPD0355636’); INSERT INTO doc_tab VALUES(‘992452533663’, ‘Pan card’, ‘CHXPS5522D’); INSERT INTO doc_tab VALUES(‘992452533663’, ‘Drivers licence’, ‘DL-0420110141769’); COMMIT; SELECT loan_number, MAX(DECODE(document_type, ‘Voters … Read more
Use: SELECT r.user_id, MAX(CASE WHEN r.question_id = 1 THEN r.body ELSE NULL END) AS “Do you like apples?”, MAX(CASE WHEN r.question_id = 2 THEN r.body ELSE NULL END) AS “Do you like oranges?”, MAX(CASE WHEN r.question_id = 3 THEN r.body ELSE NULL END) AS “Do you like carrots?” FROM RESPONSES r JOIN QUESTIONS q ON … Read more
These are a few ways of many to go about it: 1) With library dplyr, you can simply group things and count into the format needed: library(dplyr) other %>% group_by(name) %>% summarise(N = sum(result == ‘N’), Y = sum(result == ‘Y’)) Source: local data frame [4 x 3] name N Y <fctr> <int> <int> 1 … Read more
Using dynamic sql for a result where the columns are unknown at the time of executing is a bit of a hassle in Oracle compared to certain other RDMBS. Because the record type for the output is yet unknown, it can’t be defined beforehand. In Oracle 11g, one way is to use a nameless procedure … Read more
Actually, you’d be better off doing this in the client. Suppose you’re using Reporting Services, get the data as per your first result set and display it using a Matrix, with author_id and review_id in the Row Group, question_id in the Column Group, and MAX(answer_id) in the middle. A query is doable, but you’d need … Read more
You can use the PIVOT function to convert your rows of data into columns. Your original query can be used to retrieve all the data, the only change I would make to it would be to exclude the column b.field_id because this will alter the final display of the result. If you have a known … Read more
You cannot do it with SQL (except with dynamic queries), unless you know the number of columns (i. e. questions) in design time. You should pull the data you want in tabular format and then process it on client side: SELECT * FROM Question LEFT OUTER JOIN Response ON Response.QuestionId = Question.QuestionID or, probably, this … Read more