An easier way to unpivot the data would be to use a CROSS APPLY to unpivot the columns in pairs: select vendorid, orders, orders1 from pvt1 cross apply ( select emp1, sa union all select emp2, sa1 ) c (orders, orders1); See SQL Fiddle with Demo. Or you can use CROSS APPLY with the VALUES … Read more
A single SELECT with a LATERAL join to a VALUES expression does the job of “un-pivoting” columns to separate rows: SELECT p.id, v.* FROM price_list p CROSS JOIN LATERAL ( VALUES (‘type_a’, p.price_type_a) , (‘type_b’, p.price_type_b) , (‘type_c’, p.price_type_c) ) v (price_type, price); Related: Convert one row into multiple rows with fewer columns SELECT DISTINCT … Read more
Whenever I want to pivot a table in Hive, I collect key:value pairs to a map and then reference each key in the next level, creating new columns. This is the opposite of that. Query: select a.userid, y.new_id from ( select new_id, fruit_name, fruit_code from ( select new_id, map(“apple_id”, apple_id , “mango_id”, mango_id , “grape_id”, … Read more
Oh, well, it’s a little complicated. One of the problems is, the wizard-callup shortcuts don’t work in non-english versions of excels (damn, at home I would have the English version, but here at work…) Here’s a good video: https://www.youtube.com/watch?v=pUXJLzqlEPk But youtube videos can be deleted, so to make it a solid SO answer: First, you … Read more
You can reference the column name from the val for col in part of the unpivot. Col gets the column name Example Fiddle — Build list of cols we want to unpivot (skip PID & UID) declare @cols nvarchar(max) select @cols = coalesce(@cols+N’,’, N”) + quotename(c.name) from syscolumns c inner join sysobjects o on c.id … Read more
What you need to do is first, unpivot the data and then pivot it. But unfortunately MySQL does not have these functions so you will need to replicate them using a UNION ALL query for the unpivot and an aggregate function with a CASE for the pivot. The unpivot or UNION ALL piece takes the … Read more
I would do this slightly different by applying both the UNPIVOT and the PIVOT functions to get the final result. The unpivot takes the values from both the totalcount and totalamount columns and places them into one column with multiple rows. You can then pivot on those results.: select chardate, Australia_totalcount as [Australia # of … Read more
This query … SELECT ID, A, B, C, [Key 1] AS key_field FROM tblUnpivotSource UNION ALL SELECT ID, A, B, C, [Key 2] AS key_field FROM tblUnpivotSource UNION ALL SELECT ID, A, B, C, [Key 3] AS key_field FROM tblUnpivotSource; … returns this recordset (using your sample table values as tblUnpivotSource) … ID A B … Read more
MySQL does not have an UNPIVOT function, but you can convert your columns into rows using a UNION ALL. The basic syntax is: select id, word, qty from ( select id, ‘abc’ word, abc qty from yt where abc > 0 union all select id, ‘brt’, brt from yt where brt > 0 ) d … Read more