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
You gotta change the name of columns for next Pivot Statement. Like SELECT * FROM ( SELECT Branch, Category, Category+’1′ As Category1, Category+’2′ As Category2, Sales, Stock, Target FROM TblPivot ) AS P — For Sales PIVOT ( SUM(Sales) FOR Category IN ([Panel], [AC], [Ref]) ) AS pv1 — For Stock PIVOT ( SUM(Stock) FOR … Read more
Here’s a non-UDF way involving a single pivot (hence, just a single column scan to identify all the unique dates). dff = mydf.groupBy(‘id’).pivot(‘day’).agg(F.first(‘price’).alias(‘price’),F.first(‘units’).alias(‘unit’)) Here’s the result (apologies for the non-matching ordering and naming): +—+——-+——+——-+——+——-+——+——-+——+ | id|1_price|1_unit|2_price|2_unit|3_price|3_unit|4_price|4_unit| +—+——-+——+——-+——+——-+——+——-+——+ |100| 23| 10| 45| 11| 67| 12| 78| 13| |101| 23| 10| 45| 13| 67| 14| 78| 15| … Read more
Consider: TRANSFORM First(Data.Cat) AS FirstOfCat SELECT Data.ID, Data.Name FROM Data GROUP BY Data.ID, Data.Name PIVOT “Cat” & DCount(“*”,”Data”,”ID=” & [ID] & ” AND Cat<‘” & [Cat] & “‘”)+1; Or if there is a unique record identifier field – autonumber should serve: TRANSFORM First(Data.Cat) AS FirstOfCat SELECT Data.ID, Data.Name FROM Data GROUP BY Data.ID, Data.Name PIVOT … Read more
If you are using SQL Server 2005 (or above), here is the code: DECLARE @cols VARCHAR(1000) DECLARE @sqlquery VARCHAR(2000) SELECT @cols = STUFF(( SELECT distinct ‘,’ + QuoteName([Month]) FROM YourTable FOR XML PATH(”) ), 1, 1, ”) SET @sqlquery = ‘SELECT * FROM (SELECT Person, Month, Paid FROM YourTable ) base PIVOT (Sum(Paid) FOR [Person] … Read more