Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [pivot]

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.

Databases that support PIVOT/UNPIVOT syntax:

  • Oracle 11g+
  • SQL Server 2005+

PostgreSQL includes crosstab() in the tablefunc optional module.

While MySQL does not have a PIVOT function, this can be emulated with plain SQL using aggregate functions and CASE statements, which works in all other RDBMS as well.

439 questions
22
votes
3 answers

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: ╔══════════╦══════════╗ ║ PersonID ║ OptionID ║ ╠══════════╬══════════╣ ║ …
NotMe
  • 867
  • 3
  • 10
  • 19
19
votes
4 answers

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values. CREATE TEMP TABLE foo AS SELECT x::text AS name, x::int FROM generate_series(1,10) AS t(x); CREATE TEMP…
NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
18
votes
3 answers

Dynamically Define a Range in a Dimension

I have an issue that I face every time I decide to build a cube, and I haven't found a way to overcome it yet. The issue is how to allow the user to define a range of things automatically without having the need to hardcode them in the dimension. I…
AmmarR
  • 2,756
  • 3
  • 22
  • 32
16
votes
3 answers

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 view is the following: Person_Id ArrivalDate …
Federico Giust
  • 435
  • 1
  • 8
  • 16
16
votes
3 answers

How to get count for different columns on same table

Table #01 Status: StatusID Status ----------------------- 1 Opened 2 Closed 3 ReOpened 4 Pending Table #02 Claims: ClaimID CompanyName StatusID -------------------------------------- 1 …
Kaishu
  • 517
  • 1
  • 10
  • 18
13
votes
2 answers

How to pivot rows into columns MySQL

Been looking to pivot rows (progress_check) into columns check 1, check 2 etc... No sum or totals required, just to display the results hopefully... Can anyone help, Thanks Ad
Adam
  • 141
  • 1
  • 1
  • 4
12
votes
2 answers

Help with PIVOT query

I have a table with below structure : CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION]( [SCHEMA_VER_MAJOR] [int] NOT NULL, [SCHEMA_VER_MINOR] [int] NOT NULL, [SCHEMA_VER_SUB] [int] NOT NULL, [SCHEMA_VER_DATE] [datetime] NOT NULL, …
Kin Shah
  • 60,908
  • 5
  • 111
  • 230
11
votes
2 answers

When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?

I am using this great example https://dba.stackexchange.com/a/25818/113298 from Bluefeet, to create a pivot and transform it to xml data. Declaring the param DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); Next there is a CTE with a lot…
Bunkerbuster
  • 213
  • 2
  • 6
10
votes
2 answers

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 for a clearer example): CREATE TABLE Loans( Id…
Sailing Judo
  • 245
  • 1
  • 2
  • 6
10
votes
2 answers

Is it possible to PIVOT on a LIKE statement

Is it possible to group by elements (as in COLUMN LIKE='Value%') in a PIVOT table? I have a table [DBT].[Status] which contains various statuses (of databases, instances, etc.) and don't want to pivot/query all the PROD and TEST values as single…
John K. N.
  • 14,660
  • 9
  • 40
  • 93
10
votes
5 answers

Alternative to Self Join

I have asked a question here: https://stackoverflow.com/questions/43807566/how-to-divide-two-values-from-the-same-column-but-at-different-rows about dividing values from the same table, at the same column but on different rows. Now I have the…
Randomize
  • 1,093
  • 15
  • 27
10
votes
4 answers

Which is faster, SUM(CASE) or CTE PIVOT?

There are two types of ways to perform a PIVOT. Before SQL Server 2005, when PIVOT was introduced, most people did this: SELECT RateID SUM(CASE WHEN RateItemTypeID = 1 THEN UnitPrice ELSE 0 END), SUM(CASE WHEN…
Matthew Sontum
  • 495
  • 4
  • 13
10
votes
1 answer

How to pivot without fixed columns in TSQL?

I'm struggling with pivoting a quite simple table. All examples and tutorials on the web, are not what I'm looking for, so maybe you guys can help me out here (I must say that my T-SQL knowledge isn't so good...) Let me explain the situation: I have…
Mr. T.
  • 103
  • 1
  • 1
  • 6
9
votes
1 answer

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 another one is to store their attendance day…
Narendar_CH
  • 365
  • 4
  • 6
  • 13
9
votes
3 answers

How to pivot on multiple columns in SQL Server?

What is the best way to 'flatten' tables into a single row? For example, with the following table: +-----+-------+-------------+------------------+ | Id | hProp | iDayOfMonth | dblTargetPercent | +-----+-------+-------------+------------------+ |…
Zach Smith
  • 2,192
  • 10
  • 29
  • 48
1
2 3
…
29 30