A pivot (or crosstab) converts row data into columnar data, and vice versa for an unpivot. Not all databases support explicit PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc), aggregate functions, or an extension.

learn more… | top users | synonyms (2)

3
votes
1answer
8k views

Dynamic Number of Columns in Pivot Table

I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate ...
-1
votes
1answer
712 views

Sum of two columns from pivot

I have the following query: DECLARE @columns NVARCHAR(MAX),@columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX),@columns2 NVARCHAR(MAX),@columns3 NVARCHAR(MAX); SET @columns = '1,2,3,4'; SELECT @columns1 = ...
0
votes
0answers
56 views

Is it possible to get this output in Oracle 8i?

I am using Oracle 8i. I have a table Ord that stores data on such, with two columns that I'm interested in OrdNum and OrdDate. Here is an example SELECT OrdNum, OrdDat FROM Ord; OrdNum OrdDate ...
0
votes
1answer
1k views

Pivot table with MySQL

I'm trying to create a pivot table like output (cross table? Not entirely sure about the difference). The idea is to have rows, columns and summary values (specifically: distinct count). The data ...
4
votes
1answer
146 views

Multiple Table Pivot

I have a few tables that need to be converted into a single new table. I'm positive (un)pivot is the solution to the problem. However, I'm uncertain what the best way is to unpivot data from ...
-1
votes
2answers
112 views

How to merge a column in single row in sql server [duplicate]

I have a following data: Comp_id | Cntm_nb | Seq_nb | Amount | Type 1 | 1 | 1 | 2000 | rent 1 | 1 | 1 | 200 | charge 1 | ...
0
votes
1answer
173 views

Splitting Columns

I want to ask a complicated question and I think its advanced a little. I have a table has this design, its an attendance system. I need to split the time if the attendance count per day was not 2: ...
1
vote
1answer
1k views

Cross tab query with concat field

I want to create a cross tab query for my table. When I use CONCAT I am unable to get output successfully. Can anyone help me with my query? SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT ...
4
votes
1answer
665 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...
0
votes
1answer
590 views

change display of rows into single row in SQL server 2008 using dynamic PIVOTS

I have 2 tables - USE [Test1July] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Hotels]( [SeqID] [int] IDENTITY(1,1) NOT NULL, [HotelName] [nchar](25) NULL, ...
1
vote
1answer
9k views

change display of rows into single row in SQL server 2008

I have created a db that contains the following 2 small tables Here is the db script USE [Test1July] GO /****** Object: Table [dbo].[Hotels] Script Date: 07/01/2014 22:33:33 ******/ SET ...
0
votes
0answers
40 views

How to aggregate similar columns inside a very long table? [duplicate]

I have quite a difficult problem that I don't have any idea how to approach it. I'll be glad to receive some advice. I have a very long table which contains details about a specific house residents. ...
1
vote
1answer
625 views

How can I count different types in a column in sql? [duplicate]

Data: QID Type DOB ------------- 1 1 01/01/1980 1 2 03/01/1981 1 2 01/02/1991 1 1 01/01/1980 1 2 03/01/1981 ...
6
votes
1answer
21k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
0
votes
1answer
5k views

pivot a table with cell name as column name

I have a table structure is : year | code | name| value | wef | 2014 | a001 | abe | 2000 | 2014-04-01 | 2014 | a001 | def | 3000 | 2014-05-01 | 2014 | a002 | abe | 2000 | 2014-06-01 | ...
0
votes
2answers
122 views

Rows into Column

I want to make the above view vConsolPrchOrd like below: Database: PostgreSQL 9.2 I want to create a Crystal Report with this view (I asked about this on Stack Overflow).
2
votes
1answer
484 views

Need help with SQL Server PIVOT

I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations ...
0
votes
1answer
351 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 ...
2
votes
1answer
606 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
609 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
268 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 ...
0
votes
0answers
341 views

Partly rotated table with pivot/dynamic SQL without aggregation

Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have ...
2
votes
1answer
2k 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
126 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
339 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
2k 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 ...
2
votes
1answer
2k 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 ...
5
votes
1answer
411 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
19k 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 ...
5
votes
1answer
3k 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 ...
4
votes
1answer
16k 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
13k 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 ...
2
votes
1answer
8k 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
547 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
829 views

Aggregate query on multiple values of same field [duplicate]

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

Why is dynamic pivoting not supported? [closed]

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 ...
9
votes
2answers
4k 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 ...
3
votes
1answer
4k 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 ...
7
votes
4answers
1k 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 ...
-2
votes
2answers
26k views

Dynamic pivot: sum of sales per month

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to ...
1
vote
1answer
340 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
158 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 ...
13
votes
3answers
33k 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
2k 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
258 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 ...
3
votes
1answer
5k views

Security permission to only read SSAS Cube with Excel

I have an SSAS database set up on a SQL Server 2008 server. I have a Role set up with a group of users that I want to be able to access the Cubes in that database. I set up "Read definition" and ...
6
votes
3answers
374 views

MySQL single table static and dynamic pivot

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
2
votes
1answer
653 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 ...
8
votes
2answers
34k views

Return a column per date in a range

Let's say I have Table A: BookingsPerPerson Person_Id ArrivalDate DepartureDate 123456 2012-01-01 2012-01-04 213415 2012-01-02 2012-01-07 What I need to achieve with a ...
3
votes
2answers
839 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 ...