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
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
For anyone who is still interested in the difference between pivot and pivot_table, there are mainly two differences: pivot_table is a generalization of pivot that can handle duplicate values for one pivoted index/column pair. Specifically, you can give pivot_table a list of aggregation functions using keyword argument aggfunc. The default aggfunc of pivot_table is numpy.mean. … Read more
A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type SYS_REFCURSOR : CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS v_recordset SYS_REFCURSOR; v_sql VARCHAR2(32767); v_cols_1 VARCHAR2(32767); v_cols_2 VARCHAR2(32767); BEGIN SELECT LISTAGG( ””||”level”||”’ AS “‘||”level”||'”‘ , ‘,’ ) WITHIN … Read more
SELECT a.ID, a.user_ID, a.job_id, MAX(CASE WHEN c.question = ‘Is it this?’ THEN b.answer END) ‘Is it this?’, MAX(CASE WHEN c.question = ‘Or this?’ THEN b.answer END) ‘Or this?’, MAX(CASE WHEN c.question = ‘Or that? ‘ THEN b.answer END) ‘Or that? ‘ FROM Results a INNER JOIN Answers b ON a.id = b.fk_result_id INNER JOIN Question … Read more
Since you are using SQL Server 2005, here is the code: DECLARE @cols VARCHAR(1000) DECLARE @sqlquery VARCHAR(2000) SELECT @cols = STUFF(( SELECT distinct ‘,’ + QuoteName([Name1]) FROM myTable FOR XML PATH(”) ), 1, 1, ”) SET @sqlquery = ‘SELECT * FROM (SELECT Name2, Name1, Value FROM myTable ) base PIVOT (Sum(Value) FOR [Name1] IN (‘ … Read more
this should give you the results you want. CREATE TABLE #temp ( id int, data varchar(50), section varchar(50) ) insert into #temp values(1, ‘1AAA’, ‘AAA’) insert into #temp values(1, ‘1BBB’, ‘BBB’) insert into #temp values(1, ‘1CCC’, ‘CCC’) insert into #temp values(2, ‘2AAA’, ‘AAA’) insert into #temp values(2, ‘2BBB’, ‘BBB’) insert into #temp values(2, ‘2CCC’, ‘CCC’) … Read more
If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages. ;with cte as ( select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM from A ), cte2 as ( SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_ FROM cte PIVOT(MAX(activity) FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT … Read more