pivot

SQL corresponding column

This is the output I got from my query. You can see Monthly sales for 1997 is followed by monthly sales of 1998. (It might not show in proper format here but they are all in a row) Month Year Sales --------------------------- 1 1997 61258.07045 2 1997 38483.63504 3 1997 38547.21998 4 1997 53032.95254...

Good Pivot Table / Data Table algorithm in Java

I have a database to access via JDBC which returns around 200k of records I have to consolidate data table style for further processing. I could if that leads to good performance send a few SELECT (Count(...) ...) statements upfront. What is a good algorithm in Java to compute such a pivot table? ...

String concat on MS SQL for a group of rows

Lets say I have 2 tables: 1 with users and another one which keeps record of which users used what codes. Users ----- Id, Name 1, 'John' 2, 'Doe' Codes ------ Id, UserId, code 1, 1, 145 2, 1, 187 3, 2, 251 Now I want to pull a query that results he following Name, UsedCodes 'John', '145,187' 'Doe', '251' How can this be done wi...

Pivot Chart in SpreadsheetGear

Hi, I have Spreadsheetgear and I'm wondering if anyone knows if 2009 edition supports PivotCharts, does anyone know how to do this? Cheers, Doga ...

Pivot / Crosstab Query in Oracle 10g (Dynamic column number)

I have this table view UserName Product NumberPurchaces -------- ------- --------------- 'John Doe' 'Chair' 4 'John Doe' 'Table' 1 'Jane Doe' 'Table' 2 'Jane Doe' 'Bed' 1 How can I create a query that will provide this pivot view in Oracle 10g ? UserName Chair Table Bed --------...

pivot/unpivot tables mysql

can you make pivot/unpivot in mysql similar to ms sql server? thanks Here's a link to how SQL Pivot works: link text ...

Free OLAP solution for .NET running on Mono / MySQL

I'm developing my university graduation project and I'd like to include an OLAP-based reporting module. In the past, I've used Mondrian with JPivot as an OLAP solution for Java projects and I'm looking for something similar using .NET. The tricky part is that my project should run on Mono and MySQL (installation environment is a Linux ...

MySql with more then one Column

I have this table ------------------ 1 | 20,00 | A | 2 | 20,00 | A | 3 | 20,00 | A | 4 | 20,00 | A | 1 | 50,00 | B | 2 | 50,00 | B | 3 | 50,00 | B | 4 | 50,00 | B | I wold like to produce this one using group by. id | A | B | ---------------------- 1 | 20,00 | 50,00 | 2 | 20,00 | 50,00 | 3 | 20,00 ...

pivot tables in database with sqlalchemy

I need a database that support pivots to get pivot columns with sqlalchemy, something like that: # find all possible values of the pivot pivot_values = map( operator.itemgetter(0), select([pivot_on], from_obj=[report]).distinct().execute() ) # build the new pivot columns new_columns = [ pivot_func(case([(pivot_on == value, ...

Create a summary result with one query.

I have a table with the following format. mysql> describe unit_characteristics; +----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------+------+-----+---------+----------------+ | id ...

PIVOT problem when converting 4 Rows (1 Column) to 5 Columns (1 Row)

I have a table var with some rows but only one column of type DATETIME, like this: [Day] 2010-08-03 2010-08-04 2010-08-10 2010-08-11 I need to show on some columns but in only one row. My result set will be limited to 5 rows, then I can limit to 5 columns too. Example of what I need: [Day1] [Day2] [Day3] [Day4] [D...

How can I get only one row per record in master table?

Hi guys, it is possible to get only one row per record in a multitable query? I have this three tables: APPLES ID | APPLE ---------- 1 | RED 2 | YELLOW 3 | GREEN FARMS ID | FARM -------------------- B1 | GEORGE'S FARM B2 | JOHN'S FARM FARM_APPLES FARM | APPLE --------------- B1 | 1 B1 | 2 B1 | 3 B2 | 1 B3 |...

Using Pivot in SQL Server to Display dates as days of the week

Hi, I have table that has a column of type DateTime, I would like to pivot on the datetime column so that the dates are displayed as columns (as days of the week Monday, Tuesday etc). So for example I have a table like this (can't remember how SQL Server displays full datetimes but you get the idea): BugNo | DateOpened | TimeSpent 12...

MySQL: query to sort data based on the attribute of another column.

So, coming up with a title for this is difficult SELECT Date(p.born_at), SUM(p.value) as 'total_value_for_day' FROM puppies as p WHERE p.status = 2 GROUP BY DATE(p.born_at); So, this gives a 2 column result. What I want is this: columns: date | status = 1 | status = 2 | status = 3 DATA!!! is there a way to do that? ...

SQL AdventureWorks count employees by gender by city

Hi, I want to count the cities by gender, like this; City GenderFCount GenderMCount Redmond 10 20 Here is my query gets city and gender in AdventureWorks database select Gender,City from HumanResources.Employee as t1 inner join HumanResources.EmployeeAddress as t2 on t1.EmployeeID = t2.EmployeeID inner ...

MySQL: Pivot + Counting

I need help with a SQL that will convert this table: =================== | Id | FK | Status| =================== | 1 | A | 100 | | 2 | A | 101 | | 3 | B | 100 | | 4 | B | 101 | | 5 | C | 100 | | 6 | C | 101 | | 7 | A | 102 | | 8 | A | 102 | | 9 | B | 102 | | 10 | B | 102 | =================== to ...

Crosstab/Pivot query in TSQL on nvarchar columns

I have a Table1: ID Property 1 Name 2 City 3 Designation and Table2: ID RecordID Table1ID Value 1 1 1 David 2 1 2 Tokyo 3 2 1 Scott 4 2 3 Manager The Table1ID of Table2 maps to Table1's ID. Now I wish to show the Table1 Property column values as colum...

Is a dynamic pivot using LINQ possible?

I have a T-SQL 2005 query which returns: pid propertyid displayname value ----------- ----------- --------------- --------------- 14270790 74 Low Price 1.3614 14270790 75 High Price 0 14270791 74 Low Price 1.3525 14270791 75 High Price 0 14270792 74 ...

mysql rows to columns, text type

If i have a table of data like Option_id|Component_id|Option_parent|Option_name|Option_value 1 1 0 id 2 1 1 option1 Some value 3 1 1 option2 Other 4 1 0 id Value 5 1 4 ...

TSQL Table Transformation Fields => Columns

I have the following table layout. Each line value will always be unique. There will never be more than one instance of the same Id, Name, and Line. Id Name Line 1 A Z 2 B Y 3 C X 3 C W 4 D W I would like to query the data so that the Line field becomes a column. If the value exists, a 1 is applied in the field ...