The PIVOT function requires an aggregation to get it to work. It appears that your VAL column is a varchar so you will have to use either the MAX or MIN aggregate functions. If the number of tests is limited, then you can hard-code the values: select sbno, Test1, Test2, Test3 from ( select test_name, … Read more
This should work: WITH Sales AS ( SELECT S.SaleID, S.SoldBy, S.SalePrice, S.Margin, S.Date, I.SalePrice, I.Category FROM dbo.Sale S INNER JOIN dbo.SaleItem I ON S.SaleID = I.SaleID ) SELECT * FROM Sales PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P ; Or alternately: SELECT S.SaleID, S.SoldBy, S.SalePrice, S.Margin, S.Date, I.Books, I.Printing, I.DVD FROM dbo.Sale S … Read more
You can use GROUP BY and MAX to simulate pivot. MySQL also supports IF statement. SELECT ID, MAX(IF(`order` = 1, data, NULL)) data1, MAX(IF(`order` = 2, data, NULL)) data2 FROM TableA GROUP BY ID SQLFiddle Demo If you have multiple values of order, dynamic SQL may be more appropriate so that you will not have … Read more
You could use a query like this: SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ‘,’, n.digit+1), ‘,’, -1) color FROM colors INNER JOIN (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n ON LENGTH(REPLACE(colors, ‘,’ , ”)) <= LENGTH(colors)-n.digit ORDER BY id, n.digit Please see fiddle here. Please notice that this query … 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
The only way in MySQL to do this dynamically is with Prepared statements. Here is a good article about them: Dynamic pivot tables (transform rows to columns) Your code would look like this: SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( ‘MAX(IF(pa.fieldname=””‘, fieldname, ”’, pa.fieldvalue, NULL)) AS ‘, fieldname ) ) INTO @sql FROM product_additional; SET … Read more
Since you want to pivot multiple columns of data, I would first suggest unpivoting the result, score and grade columns so you don’t have multiple columns but you will have multiple rows. Depending on your version of SQL Server you can use the UNPIVOT function or CROSS APPLY. The syntax to unpivot the data will … Read more
Spark >= 3.4 You can use built-in melt method. With Python: df.melt( ids=[“A”], values=[“col_1”, “col_2″], variableColumnName=”key”, valueColumnName=”val” ) with Scala df.melt(Array($”A”), Array($”col_1″, $”col_2″), “key”, “val”) Spark < 3.4 It is relatively simple to do with basic Spark SQL functions. Python from pyspark.sql.functions import array, col, explode, struct, lit df = sc.parallelize([(1, 0.0, 0.6), (1, 0.6, … Read more
I would use this query: SELECT user_id FROM wp_usermeta WHERE (meta_key = ‘first_name’ AND meta_value=”$us_name”) OR (meta_key = ‘yearofpassing’ AND meta_value=”$us_yearselect”) OR (meta_key = ‘u_city’ AND meta_value=”$us_reg”) OR (meta_key = ‘us_course’ AND meta_value=”$us_course”) GROUP BY user_id HAVING COUNT(DISTINCT meta_key)=4 this will select all user_id that meets all four conditions.
df.groupby(‘Category’).agg({‘Item’:’size’,’shop1′:[‘sum’,’mean’,’std’],’shop2′:[‘sum’,’mean’,’std’],’shop3′:[‘sum’,’mean’,’std’]}) Or if you want it across all shops then: df1 = df.set_index([‘Item’,’Category’]).stack().reset_index().rename(columns={‘level_2′:’Shops’,0:’costs’}) df1.groupby(‘Category’).agg({‘Item’:’size’,’costs’:[‘sum’,’mean’,’std’]})