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.

475 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 ║ ╠══════════╬══════════╣ ║ …
ChrisLively
  • 887
  • 3
  • 10
  • 19
20
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…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
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,826
  • 3
  • 28
  • 33
17
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
  • 443
  • 2
  • 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
  • 2
  • 12
  • 19
14
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
  • 151
  • 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
  • 62,545
  • 6
  • 124
  • 245
11
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
  • 537
  • 1
  • 5
  • 13
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
11
votes
2 answers

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 10:27:15 | info 2016-04-05 11:27:15 | warn With…
user91740
  • 111
  • 1
  • 1
  • 3
11
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.
  • 113
  • 1
  • 1
  • 6
10
votes
4 answers

Dynamically convert hstore keys into columns for an unknown set of keys

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns. Users can add new custom fields and so I can't rely on…
coagmano
  • 273
  • 3
  • 10
10
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
  • 385
  • 4
  • 6
  • 14
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.
  • 18,854
  • 14
  • 56
  • 117
1
2 3
…
31 32