Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.
I have a table in the form:
year | season | hits
2004 | Summer | 42
2004 | Autumn | 43
2005 | Spring | 51
2005 | Summer | 52
2005 | Autumn | 53
2006 | Spring | 61
I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form
year | Spring | Summer | Autumn
2004 | ------ | ----42 | ----43
2005 | ----51 | ----52 | ----53
2006 | ----61 | ------ | ------
Could anyone suggest a mysql query to generate the required output?
View 2 Replies (Posted: April 08, 2006 05:14AM)
Sponsored Links:
Related Forum Messages for MySQL:
Rails Database Neutral Pivot Table Or Crosstab?
Does anyone know of a way to build a pivot table using activerecord which would be remotely DB neutral? I've tried to avoid using find_by_sql and DB specific queries but for a pivot table or crosstab query I have no idea how to do it in a way which is not specific to say MySQL. IE my mySQL find_by_sql breaks on a postgresql DB. I found [URL] this obscure crosstab gem which might work, but I'm wondering if anyone else has a better solution. Example something rediculous like this which basically just flips the axis on a table: SELECT availables.name, rooms.id, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 0, availables.price, '')) AS day1, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 1, availables.price, '')) AS day2, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 2, availables.price, '')) AS day3, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 3, availables.price, '')) AS day4, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 4, availables.price, '')) AS day5, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 5, availables.price, '')) AS day6, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 6, availables.price, '')) AS day7, AVG(availables.price),SUM(availables.price) FROM `availables` INNER JOIN rooms ON availables.room_id=rooms.id WHERE availables.room_id = '18382' GROUP BY availables.name
Posted: Nov 23 09 at 10:31
View 1 Replies!
View Related
Matrix / Crosstab / Pivot Query - SUM Of All ItemAmount Grouped By BillNo
BillNo - 1, ServiceCode -1, ServiceName - 'Lab test' , ItemAmount -30 BillNo -1, ServiceCode -2, ServiceName -'Consultation', ItemAmount -70 I need output like this Bill No -1, Total Bill Amount-100 , Lab Test-30, Consultation-70, Drugs-0, Misc-0 So basically i need the Bill Amount which is the SUM of all ItemAmount Grouped by BillNo. Then in the same row i need to distribute this Bill Amount to the 4 ServiceNames. So 100 gets distributed as 30 for Lab test and 70 for Consultation. This gave me what i want but it is not optimal. SELECT BD.BILLNO AS BN, SUM(BD.ITEMAMOUNT) AS "Bill Amount", (SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD WHERE SERVICENAME LIKE '%Lab%' AND BD.BILLNO = BN ) AS "Lab", (SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD................
Posted: Jul 23 10 at 3:43
View 2 Replies!
View Related
Ruby On Rails - Pivot To A Postgres Pivot Table?
I was using mysql just fine until I recently switched one of my rails apps to heroku and had to change over. Almost everything works as expected except I have one query which does something totally funky. This is the postgres, but under mysql it is mostly identical except for the EXTRACT DOW and some group by additions, but that isn't the problem, the problem is it used to SUM the days of the week listed, now it sums the entire table... and also the AVG is off since it also gets the table avg and not the days listed. Is there a way to get a sum of the listed days without having to do another select, something i'm missing?... I would like to avoid doing SELECT ( SELECT ... ) as SUBQUERY just to get a sum of the columns. SELECT rooms.name, rooms.id, MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -3 THEN (availables.price*1) ELSE 0 END) AS day1, MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -2 THEN (availables.price*1) ELSE 0 END) AS day2, MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -1 THEN (availables.price*1) ELSE 0 END) AS day3, (AVG(availables.price)*1) AS avg, (SUM(availables.price)*1) * 2 AS sum, MAX((SIGN(spots)-1) + 2) AS beds FROM availables INNER JOIN rooms ON availables.room_id=rooms.id WHERE availables.room_id = '1818' AND availables.price > 0 GROUP BY rooms.id, rooms.name
Posted: Jan 11 10 at 19:38
View 3 Replies!
View Related
Crosstab Sum
I'm basically trying to create a crosstab-esq query which works fine. I'm totalling the various groups of results as 'CAT1', 'CAT2' etc (this works fine). Is there a way that I can total the totals? The syntax I've atmysqlted doesn't work. Basically I need to collect the total of CAT1 to CAT6 then order the results by that total. SELECT `Advisor ID`, sum(if(`Price Without IVA` BETWEEN 0 AND 50000, 5, 0)) AS CAT1, sum(if(`Price Without IVA` BETWEEN 50001 AND 100000, 10, 0)) AS CAT2, sum(if(`Price Without IVA` BETWEEN 100001 AND 250000, 15, 0)) AS CAT3, sum(if(`Price Without IVA` BETWEEN 250001 AND 500000, 20, 0)) AS CAT4, sum(if(`Price Without IVA` BETWEEN 500001 AND 1000000, 25, 0)) AS CAT5, sum(if(`Price Without IVA` > 1000001, 35, 0)) AS CAT6, sum(CAT1+CAT2+CAT3+CAT4+CAT5+CAT6) AS TOTAL FROM `sales` GROUP BY `Advisor ID` ORDER BY TOTAL DESC;
Posted: February 12, 2009 10:09AM
View 1 Replies!
View Related
Ordering Columns In A Crosstab?
I am trying to combine 3 crosstab queries for output on a website. Is it possible to order the results?I am pulling data from 2 tables.Tables are set up something like this: 1st table: (Codes) Code | Name -------------------- 123 | Yellow 124 | Green 125 | Blue 126 | Red [code]....
Posted: July 16, 2010 08:25AM
View 4 Replies!
View Related
Get Crosstab Column Names?
I am looking for a way to do a crosstab. I've read plenty of articles on mySQL "crosstabs", but I still haven't found one that suits my needs.Here is my table: Ticker - Date - Field1 MSFT - 20110325 - 1 MSFT - 20110322 - 0 CSCO - 20110325 - 1 CSCO - 20110322 - 1 [code]....
Posted: March 27, 2011, 12:20:07 PM
View 3 Replies!
View Related
Select Statement In Crosstab Style?
we have the following select statement and we need to have the description appear as a column header, there can be multiple descriptions so this needs to loop. So in essence display like this WO number| Batch No | comcod | stage | desc1 | desc2 .... and so on| resflg the tstrdgs would appear under the desc columns for the appropriate columns. the current query i have is Code: SELECT DISTINCT `readings`.`Wonumber` , `readings`.`BatchNo` , `readings`.`ComCod` , `readings`.`Stage` , `testcodes`.`description` , `readings`.`tstrdg` , `readings`.`Resflg` FROM `Eastland_Compounding`.`readings` LEFT JOIN `Eastland_Compounding`.`prdtest` ON (`readings`.`TstNum` = `prdtest`.`Tstnum`) INNER JOIN `Eastland_Compounding`.`testcodes` ON (`prdtest`.`Tstcode` = `testcodes`.`id`) WHERE (`readings`.`Wonumber` = 99999) and the results look as follows WonumberBatchNoComCodStagedescriptiontstrdgResflg 99999615211862Specific Gravity1.488PASS 99999542211861Mooney 1+451.46PASS 99999615211862Mooney 1+448.39PASS 99999615211862Mooney T10 Scorch11.6PASS 99999615211862Hardness IRHD78PASS 99999616211862Hardness IRHD78PASS although there are only a few descriptions in this resultset there can be multiple ones for different wonumbers or comcod's
Posted: August 12th, 2010, 05:23 AM
View 5 Replies!
View Related
Hibernate - Dynamic Database Query/view For Crosstab?
I currently have a hard coded view with the following sql: select username ,(case user_role.role_id when 1 then true else false end) as ROLE_SUPER ,(case user_role.role_id when 2 then true else false end) as ROLE_ADMIN ,(case user_role.role_id when 3 then true else false end) as ROLE_VIEW ,(case user_role.role_id when 4 then true else false end) as ROLE_USER ,(case user_role.role_id when 5 then true else false end) as ROLE_EMAIL from user left outer join user_role on user.id=user_role.user_id left outer join role on user_role.role_id = role.id; my question is whether or not it is possible to dynamically generate role columns from the records in the role table.
Posted: Jun 26 10 at 2:08
View 1 Replies!
View Related
Extract, Transform
We have an old legacy database at my company called CIMPRO, running on a SCO UNIX box. We're trying to find a way to connect to the CIMPRO database, grab the data, and send it to a Mysql database. I've been able to successfully connect to the database using PHP and ODBC to view the data in the tables. i'd like to find a way to run a Linux tool or script to connect to the database, do an initial copy of the table structure from the legacy database to Mysql (on the linux box) and then copy selected data. If that is doable I'd also like to be able to refresh the table data periodically (e.g., cron job) either by deleting and reimporting or by checking to see what has changed.
Posted: September 11th, 2006 06:45 PM
View 1 Replies!
View Related
Transform Rows To Columns
I have a dataset that looks like this | profileID | groupID | Value | | 3 | 2 | 26 | | 3 | 3 | 85 | | 3 | 4 | 40 | | 3 | 5 | 35 | | 4 | 2 | 27 | | 4 | 4 | 81 | | 4 | 5 | 76 | | 5 | 2 | 48 | | 5 | 3 | 50 | | 5 | 4 | 59 | etc i want to transform it into columns (denormalize) for a specific business purpose, so that it looks like this; profileID Group2 Group3 Group4 Group5 3 26 85 40 25 4 27 NULL 81 76 5 48 50 59 NULL etc
Posted: November 11, 2008 04:43PM
View 1 Replies!
View Related
Transform Columns Into Rows?
I have searched the solution in the forums but the things I saw didn`t work fine. I have this table: ID | Total_time | Partial_time ----------------------------------------- 1 60 30 and I want to transform if in this one: ID | Parameter | Value ---------------------------------------- 1 Total_time 60 1 Partial_time 30
Posted: April 29, 2010 05:07PM
View 4 Replies!
View Related
Transform This SELECT Into A DELETE?
I've got a database table which by necessity accumulates a lot of duplicates. The reason it does this is that the data it contains is entered over a series of operations, and then only marked as final once the last operation has completed, meaning that duplicate rows from previous results should be retained until the new results have all arrived.Anyway, I've constructed a SELECT statement which neatly finds all duplicate rows which can be safely deleted, here is the statement: SELECT * FROM table1 WHERE final = 1 AND category = 5 GROUP BY category, var1, var2 HAVING (COUNT(*) > 1) AND time < '2009-08-22 05:01:36' [code]...
Posted: August 23rd, 2009, 06:40 AM
View 1 Replies!
View Related
Transform All The Entries To 14 Digits?
I have a table with a column 'A'. Some rows have 14 digits for the column 'A' and some have only 12. I need to transform all the entries to 14 digits. The datatype is varchar. I would like to update all the rows at once (one query), adding zeros before the first digit, so an entry like 012345678910 would become 00012345678910. Is it possible to do it in one single query?
Posted: Mar 17 10 at 23:14
View 3 Replies!
View Related
Transform '07,01,06' Into A Date Format With Just A Query
I've been handed just about the worst HTML/PHP/MySQL site ever scrapped together. While we're in the process of rebuilding the site from scratch for our client, they desperately need some data pulled from their DB. Originally the data was dumped to files but due to attempted injection attacks, the table has grown so large it times out. So I'm trying to pull the data directly but I have a huge problem. I need to pull all data with a date >= July 1, 2006. The problem is the dipsh*t who built this has the date field with a datatype of varchar and the date in the format of གྷ,01,06'. Obviously I can't do a date comparision check with that so how can I transform this data into a correct date format using only a query? I do NOT want to go in and start coding in this site. It's a live site and I'm afaid anything I do will break it. It's MySQL 3.23.57 and all I have access to is PHPMyAdmin. PLEASE help me!!! Someone work a little query magic for me.
Posted: Aug 29, 2007, 11:42
View 4 Replies!
View Related
Transform Date To Year-month-day?
I won't post questions and search the internet for them. But I'm really stuck what to do now. I have a database with a column name dob, unfortunately the formats of those dobs are all different, for example: September 4, 1832 28 June 1490Is there some way to recognize that months are written full out and day are less than 3 characters and year is 4 characters? So that you can change it to year-month-day (all written in numbers)?I really don't know what to do.
Posted: April 23, 2011 03:22AM
View 4 Replies!
View Related
Transform Vertical Into Horizontal Data?
I have a table "Item" with a number of related items, like so: ID Rel_ID Name RelRank --- ------ ---- ------- 1 1 foo 1 2 1 bar 2 3 1 zam 3 4 2 foo2 1 I'm trying to get a query so items with the same Rel_ID would appear in the same row, like so: Rel_ID Name1 Name2 Name3 ------ ----- ----- ----- 1 foo bar zam 2 foo2 I've tried selecting the table multiple times: SELECT k.Rel_ID, k.name 'Name1', k2.name 'Name2' FROM item k, item k2 WHERE k.Rel_ID = k2.Rel_ID But this fails. Surely there's a transformation or query that could drastically simplify the process, and I'm just missing it because I haven't used SQL in this way before. What am I missing?
Posted: Nov 1 10 at 18:19
View 2 Replies!
View Related
Transform Date To Mysql Timestamp Via CSV Import
I have a CSV file (from excel) with contains personal data, like names, adress, and birth date. The birth date is in this format yyyy-mm-dd. I would like to import this CSV file into my MYsql database with the "import CSV file" function that is build in PHPmyadmin. The problem is that I need UNIX timestamps in my database, and not the date in yyy-mm-dd format. Code:
Posted: November 08, 2006 02:39PM
View 2 Replies!
View Related
Generic Db Statement For Wide To Tall-skinny Transform?
I would like to transform a wide table into a tall-skinny table. Is there an SQL statement to perform something like that? for example: var, sub1, sub2, sub3, sub4, ... v1 , a1 , b1 , c1 , d1, ... v2 , a2 , b2 , c2 , d2, ... ... into var, subject, data v1 , sub1 , a1 v1 , sub2 , b1 v1 , sub3 , c1 v1 , sub4 , d1 v2 , sub1 , a2 v2 , sub2 , b2 v2 , sub3 , c2 v2 , sub4 , d2 ... That's how far I can go: SELECT w1.v , cols.sub FROM wide as w1 , (SELECT column_name as sub FROM information_schema.columns WHERE (table_name='wide') and (ordinal_position > 1) ) as cols ; This gives: var, subject v1 , sub1 v2 , sub1 v1 , sub2 v2 , sub2 v1 , sub3 v2 , sub3 v1 , sub4 v2 , sub4 ...
Posted: January 07, 2010 04:49PM
View 3 Replies!
View Related
|
TRACKER
