The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. Not all databases support the PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc) and aggregate functions.

learn more… | top users | synonyms

0
votes
1answer
73 views

Sort Totals By Months

I need to show the count of all rows grouped by month and then pivot it Appreciate an easy solution for that Or alternatively if someone can help me fix this I've used dynamic pivot query DECLARE ...
1
vote
1answer
37 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
0
votes
1answer
37 views

MySQL Rows to Columns to HBase (using Sqoop)

I have a table that looks like this in MySQL: id | date | num | avg I have found that I can use this for rows to columns and it works so far: SELECT CONCAT(id, '-', date), MAX(IF(`num` = ...
0
votes
0answers
84 views

SQL Server 2008 single-line PIVOT/UNPIVOT combo

I have a request to get information from a table with a stored procedure that shows all the necessary data for in one line. This table includes a hierarchyID, so I am trying to use the HID methods in ...
1
vote
1answer
220 views

Postgres: “Pivot” unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, ...
0
votes
0answers
59 views

Seeding Pivot table with percentage

Given two tables - users and games - and a pivot table - userGames, I'm trying to efficiently seed the userGames table For testing, so that each user has close to a set percentage of games. The query ...
0
votes
1answer
54 views

Help with a PIVOT

I have a view that gives data which looks like this: Rank | Score -------------------------- 1 | 1 2 | 2.5 ...
0
votes
1answer
244 views

Query with PIVOT fails with second LEFT OUTER JOIN to same table

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm ...
0
votes
1answer
82 views

select row data as column list

In my database I have a set of custom field values that can be setup by the customer. They can create labels for these fields and then assign values which are linked back to particular tables in the ...
1
vote
1answer
145 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
3
votes
2answers
3k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
4
votes
1answer
460 views

Transpose two-dimensional array from n rows to 2 columns

Background Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available. Problem Looking to simplify the code that pivots a two-dimensional array. Code A working, overly-verbose example ...
3
votes
1answer
2k views

Display Monthly Attendance Report in MySql

I am doing a School Management System in php using Mysql DB. I am stuck in my Project. Please anybody suggest what I am doing wrong. I have two tables in my database; one is to store Students records ...
2
votes
2answers
2k views

Dynamic Oracle Pivot_In_Clause

I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this: WITH PIVOT_DATA AS ( SELECT * FROM ( SELECT USERNAME, GRANTED_ROLE ...
1
vote
1answer
519 views

How to create a table in MySQL with automatic numeration of columns?

Example. My data is from Table name:BIKE Bike_No Repair_Date Repair_Cost -------- ----------- ------------ ABC1234 2013-01-05 50.00 BMX5678 2013-02-04 75.00 ABC1234 ...
4
votes
1answer
155 views

Oracle pivot on a column with delimited data

My data is like: keycol,col1,col2,col3 1,a;b;c,some data,some other data 2,x,some data,some other data 3,y;z,some data,some other data Where a column is delimited in the source system with ...
0
votes
2answers
76 views

Aggregate query on multiple values of same field

I am using SQL-Server 2012, I have a table of credit card transactions as follows: CardScheme RespCode ========================= Visa Declined MasterCard Approved MasterCard Approved ...
1
vote
0answers
83 views

Why is dynamic pivoting not supported?

TL;DR What is the reason that in 2013 we still can't define a VIEW that displays some pivoted data with a dynamic column set. TL;DR/2 Is pivoting data a denormalization technique? First: Not going ...
7
votes
2answers
644 views

How to flatten results of a table with two related “many” tables?

I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them. I have the following tables (somewhat abbreviated ...
1
vote
1answer
746 views

can I replace nulls in a PIVOT with zeroes?

I am using the PIVOT function in Oracle and am curious if I can replace the null values with zeroes? I know I can wrap the entire query in another SELECT and then use COALESCE on the values, but I am ...
6
votes
4answers
511 views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...
-6
votes
2answers
3k views

Display month in columns and the sum of sales below the columns in sql [closed]

My result is this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 Result will show like ...
1
vote
1answer
133 views

Pivot a table to repeat the first column for each following column

i'm not sure how to explain so I'll try and write out an example. I'm currently using an access database with a table in the following format 1 A B C D 2 A B C D I'm trying to perform a query ...
1
vote
1answer
128 views

Transposing data

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to ...
6
votes
3answers
2k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
0
votes
0answers
670 views

Dynamically Converted rows into columns (Pivoting)

Query SELECT Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total, COUNT(studfeedback.studfeedback_Rating) AS totastudents, ...
0
votes
0answers
234 views

Problem with PIVOT [closed]

I am facing a small issue while using PIVOT table. My problem is as following. I have a single table with the procedures id and their steps and i want all the steps needs to be displayed in 5 column ...
2
votes
3answers
194 views

MySQL data view

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
2
votes
1answer
374 views

PIVOT SQL Server

SQL Server 2008 R2 PIVOT IO Stats I have a script that collects IO stats from sys.dm_io_virtual_file_stats once a day and stocks them in a table. I want to be able to use these stats to generate ...
2
votes
2answers
5k views

SQL query to return a column per date in date range?

Let me explain what I've got and what I'm trying to achieve. Let's say I have Table A: BookingsPerPerson Person_Id ArrivalDate DepartureDate 123456 2012-01-01 2012-01-04 213415 ...
3
votes
2answers
308 views

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

I have something that looks like this (SQL Fiddle here): Date Location Value 1/1/2012 Baltimore 36 1/1/2012 Houston 36 1/1/2012 Chicago 55 2/1/2012 Baltimore 49 ...
0
votes
3answers
107 views

How to convert query results into groups of common results

I have a query that returns results like this: MyValue MyGroup _______________|_______________ 56 | I _______________|_______________ 12 | I ...
3
votes
1answer
326 views

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this ...
2
votes
1answer
283 views

Oracle Discoverer like app native for SQL Server?

Can someone point me at some web based apps that work like Oracle Discoverer but native for SQL Server. We already the SQL connector running to actually use disco, but was wondering what other ...
2
votes
1answer
3k views

Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

I've developed a timesheet recording system, that allows users to record: A Period of time (9:00 to 12:00) What they did during that time (DutyActivity: 1 hour report writing, 2 hours training) ...
1
vote
2answers
865 views

why does my pivot give me an error?

Msg 207, Level 16, State 1, Line 1 Invalid column name 'FullName'. select FullName, ['1-Urgent'], ['2-High'], ['3-Medium (3000)'] from (select FullName,AltBusinessSeverity, total from Mdata) AS ST ...
2
votes
2answers
947 views

SQL statement to combine main table and child table as columns?

Having a main table and a child table in a Microsoft SQL Server database (2005+), I want to show the user a single table in a grid view which includes rows from the main table and rows from the child ...
1
vote
1answer
334 views

How can I pivot this select?

SELECT sc.CID,sc.CodeName as OverviewText,scRAG.CodeName as RAGStatusText FROM StatusCode sc LEFT OUTER JOIN ProjectOverview po ON sc.CID = po.ProjectOverviewCID AND po.ProjectId ...
4
votes
2answers
838 views

SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements. SELECT t.ProjectId, COALESCE(MAX(ExecutiveChampion), 'n/a'), ...