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

7
votes
2answers
2k 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 ...
6
votes
3answers
10k 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: ╔══════════╦══════════╗ ║ ...
6
votes
4answers
931 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 ...
5
votes
2answers
13k 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 ...
4
votes
2answers
971 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'), ...
4
votes
1answer
205 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 You can refer to this question to see how the query was produced. In the fiddle ...
4
votes
1answer
1k 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
369 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 ...
3
votes
1answer
6k 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 ...
3
votes
2answers
490 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 ...
3
votes
1answer
142 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
3
votes
2answers
10k 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 ...
3
votes
1answer
431 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
3answers
215 views

MySQL data view

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
2
votes
2answers
5k 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
2k 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 ...
2
votes
1answer
53 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 ...
2
votes
2answers
1k 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 ...
2
votes
1answer
495 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
1answer
336 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
0answers
145 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 ...
2
votes
1answer
6k 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
1answer
147 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 ...
1
vote
2answers
134 views

Separate data from 1 column into multiple columns

Below is sample data and expected results: Table1: salesprice ItemNo Variant SalesCode UnitPrice A 1 SRP 100 A 2 SRP 200 ...
1
vote
1answer
1k 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 ...
1
vote
1answer
232 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 ...
1
vote
1answer
24 views

Pivoting with NULL Dates

I'm attempting the pivot below: SELECT EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] ...
1
vote
2answers
1k 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 ...
1
vote
1answer
353 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 ...
1
vote
1answer
386 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
vote
1answer
565 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, ...
1
vote
1answer
169 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
250 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 ...
1
vote
1answer
2k 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 ...
1
vote
1answer
340 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, ...
1
vote
1answer
2k 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 ...
1
vote
1answer
23 views

MYSQL Database PIVOT and wants in specific format

I have below table and its data in mysql CREATE TABLE `pointhistory` ( `id` bigint(20) NOT NULL auto_increment, `channelid` varchar(64) NOT NULL, `deviceid` varchar(64) NOT NULL, `pointid` ...
1
vote
1answer
64 views

how to included concatenated values in Dynamic Pivot Column Value

I am extending a dynamic pivot query. The original query works fine. In the second query Instead of display the date as a value for the pivot column I would like to display a concatenated column ...
1
vote
1answer
74 views

Attempting to Pivot a column of grades and coming up with NULL values

I have Data in a table that i'm attempting to pivot from rows to columns, and the script im using is working, but the pivot is coming up with NULL for everything. Here's the source data: ...
1
vote
1answer
271 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, ...
0
votes
2answers
280 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 ...
0
votes
1answer
96 views

T-SQL How to Pivot on the Values of a Table?

How do you pivot a table on the value in the field? I have to admit that while I can write some pretty nifty queries, have my CTEs down, and enjoy a wide knowledge of T-SQL programming, Pivots get ...
0
votes
1answer
139 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
756 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
275 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 ...
0
votes
1answer
301 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, ...
0
votes
1answer
190 views

How can I count different types in a column in sql?

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 ...
0
votes
1answer
413 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
3answers
118 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 ...
0
votes
1answer
35 views

Dynamic Pivot with overlapping years

I have looked and cannot find how to show the last # months in a pivot table. Everything I seem to run across codes months statically. I'm seeking a solution that shows something similar to the ...