Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

Filter by
Sorted by
Tagged with
22 votes
3 answers
83k 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: ╔══════════╦══════════╗ ║ ...
NotMe's user avatar
  • 887
20 votes
4 answers
10k views

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 ...
Evan Carroll's user avatar
  • 63.5k
18 votes
3 answers
11k views

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 ...
AmmarR's user avatar
  • 2,806
17 votes
3 answers
79k 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 view ...
Federico Giust's user avatar
16 votes
3 answers
94k views

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 -------...
Kaishu's user avatar
  • 517
14 votes
2 answers
62k views

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's user avatar
  • 151
12 votes
2 answers
770 views

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, ...
Kin Shah's user avatar
  • 62.3k
11 votes
4 answers
13k views

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 ...
Matthew Sontum's user avatar
11 votes
2 answers
3k views

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 ...
Bunkerbuster's user avatar
11 votes
1 answer
22k views

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 ...
Mr. T.'s user avatar
  • 113
11 votes
2 answers
23k views

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:...
user91740's user avatar
  • 111
10 votes
3 answers
121k views

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's user avatar
  • 2,370
10 votes
1 answer
47k 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 ...
Narendar_CH's user avatar
10 votes
2 answers
2k views

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/...
John K. N.'s user avatar
  • 17.9k
10 votes
5 answers
3k views

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 ...
Randomize's user avatar
  • 1,183
10 votes
4 answers
7k views

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 ...
coagmano's user avatar
  • 273
10 votes
2 answers
12k 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 ...
Sailing Judo's user avatar
9 votes
1 answer
23k views

Consolidate multiple rows into single row

In PostgreSQL 9.5, I have a table named reports: CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, ...
Comic's user avatar
  • 91
8 votes
3 answers
11k views

MySQL single table static and dynamic pivot

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 |...
Khairu Aqsara's user avatar
8 votes
2 answers
1k views

How to create recurrent weekdays as columns in a pivot?

I am newcomer to programming and databases and I would be grateful for some help on the following scenario. I use PHP with SQL Server. I am building an employee attendance system and I would like to ...
Mike T's user avatar
  • 95
8 votes
2 answers
29k views

How to get multiple row data into a row with multiple columns

I have a MySQL table like this: User_Id course_name course_location course_id 1 course name 1 location 1 1 1 course name 2 location 2 2 1 course name 3 ...
Lanw's user avatar
  • 81
8 votes
1 answer
4k views

MySQL query to create a Matrix view using pivot

I have a table with the following fields: p1 p2 s1 s2 where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The number of players can vary from 3 - 10. The table have the results between them....
dimoss's user avatar
  • 181
7 votes
4 answers
2k 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 ...
Cade Roux's user avatar
  • 6,602
7 votes
1 answer
14k 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 ...
JHFB's user avatar
  • 2,854
7 votes
3 answers
9k views

How to pivot data in SQLite

I've a table which contains text data containing a bunch of translations for different language. Each translation is for a specific label. I need to generate a pivot table so to get quickly what's ...
Andrea Moro's user avatar
7 votes
3 answers
17k views

How to Pivot in PostgreSQL

Having the following data in a table: ID Category Value 1234 Cat01 V001 1234 Cat02 V002 1234 Cat03 V003 1234 Cat03 V004 1234 Cat03 V005 I want to have the ...
Aleps's user avatar
  • 71
7 votes
1 answer
658 views

What am I missing in my crosstab query here?

I am having difficulty understanding how to correctly transpose this table from a database I am using. This table was created by someone who designed the database years ago, but I managed to retrieve ...
arilwan's user avatar
  • 177
6 votes
4 answers
4k views

Convert row into concatenated string with column names included

I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in ...
rsjaffe's user avatar
  • 383
6 votes
1 answer
557 views

How to Pivot in T-SQL

I have a question I can't solve. My query would be: SELECT f.Title, p.StartOfShowing FROM PlayDates p INNER JOIN PlayDateSections ps ON p.PlayDateId = ps.PlayDateId INNER JOIN ...
Tsuna Sawada's user avatar
6 votes
1 answer
57k 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 ...
tmachielse's user avatar
6 votes
1 answer
3k views

Insert data from multiple rows into multiple columns

I cannot find, after much searching, how this would be done. My web searches get Pivots and Concats and Cases and Subqueries etc, none of it is quite solving the issue for me. The multiple-rows-to-...
C0MPU7ER's user avatar
  • 165
6 votes
1 answer
7k 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 ...
Dave Jarvis's user avatar
6 votes
1 answer
976 views

SUM CASE WHEN CLAUSE

I have the below Query and I want the Ageing profile to show as a new column my ageing profile is 0-30, 31-60, 61-90, 91-120, 121-180, 181-365, 365+ I basically want the results to appear lie this ...
Chris's user avatar
  • 61
6 votes
2 answers
147 views

Combining data from multiple rows

I am relatively new when working with queries so let me apologize in advance. This is SQL Server 2012 that I will be working with. This data has been brought into a staging table through a nightly ...
Sandy's user avatar
  • 113
5 votes
2 answers
75k 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 show ...
user16947's user avatar
  • 127
5 votes
3 answers
25k views

Two rows into two columns

I have this table: Name Value Sequence ------------------------------- test A1 1 test A2 3 test2 A20 5 ...
MikeF's user avatar
  • 88
5 votes
2 answers
2k 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'), COALESCE(MAX(BusinessOwner),...
kacalapy's user avatar
  • 2,052
5 votes
1 answer
3k views

group a multiple union statement

I have looked all over the site but can't find the solution to my problem. I tried to abstract it as far as possible. The query I have is like this: select sup, a, b, c, d from ( select sup, a, ...
Ruben van Straten's user avatar
5 votes
1 answer
7k views

RDS crosstab: ERROR: permission denied for language c

We're currently using PostgreSQL 9.2 in our production database, we want to migrate to a new RDS instance, using PostgreSQL 9.4. However, we need to have permission to create a function, but only ...
Valter Silva's user avatar
5 votes
1 answer
2k 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 ...
alexsmn's user avatar
  • 353
5 votes
2 answers
1k views

How can I save the results of DBCC SHRINKFILE into a table?

I have the following script: -- available space in each of the database files PRINT @@SERVERNAME --SQLDWDEV01 USE [BodenStage] GO SELECT name ,(CAST(ROUND((size/128.0 - CAST(FILEPROPERTY(name, '...
Marcello Miorelli's user avatar
5 votes
1 answer
8k views

Is it possible to create a dynamic join?

I'm using PostgreSQL if it matters. Assuming these two tables: id | run ----+------- 1 | run_1 2 | run_2 3 | run_3 and sample | elapsed | run -------+---------+------- samp1 | 1:09 | ...
Yves Dorfsman's user avatar
5 votes
1 answer
4k views

Creating crosstab() pivot table in PostgreSQL 9.0

I have a rather complicated issue for myself which I hope someone can help me with. I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table. To keep it simple I will ...
daniel franklin's user avatar
5 votes
1 answer
2k 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 ...
codepringle's user avatar
5 votes
1 answer
2k 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 ...
jamauss's user avatar
  • 305
5 votes
2 answers
103 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is the ...
Pete's user avatar
  • 115
5 votes
2 answers
25k views

How to transpose values from one column into columns with a value from a different column?

I have a database with the following structure: Date role type duration 2022-04-16 Nurse Food preparation 45 2022-04-17 Nurse Cleaning 30 2022-04-17 Volunteer Cleaning 20 2022-04-17 Nurse Food ...
Brylie Christopher Oxley's user avatar
5 votes
1 answer
556 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, oks_l....
Cade Roux's user avatar
  • 6,602
5 votes
1 answer
1k 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 ...
guildsbounty's user avatar
4 votes
2 answers
4k views

Query monthly having group_concat and group by need help

I’m stuck on a join tables query presenting data monthly involving GROUP BY and GROUP_CONCAT. Here’s a simple client table (DDL and DML at the bottom of this post): id | Name 1 | Sony 2 | Toshiba 3 ...
Appschema's user avatar

1
2 3 4 5
10