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.
474
questions
22
votes
3
answers
82k
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:
╔══════════╦══════════╗
║ ...
- 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
...
- 62.2k
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 ...
- 2,776
17
votes
3
answers
78k
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 ...
- 443
16
votes
3
answers
93k
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
-------...
- 517
14
votes
2
answers
61k
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
- 151
12
votes
2
answers
769
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,
...
- 62.1k
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 ...
- 213
11
votes
1
answer
21k
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 ...
- 113
11
votes
2
answers
21k
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:...
- 111
10
votes
4
answers
12k
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 ...
- 515
10
votes
3
answers
115k
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 |
+-...
- 2,310
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 ...
- 385
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/...
- 17.4k
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 ...
- 1,163
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 ...
- 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 ...
- 245
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,
...
- 91
8
votes
3
answers
10k
views
MySQL single table static and dynamic pivot
I have a table that looks like this:
+----------------------------------------+
|Name | kode | jum |
+----------------------------------------+
| aman |kode1 |...
- 213
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 ...
- 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 ...
- 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....
- 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 ...
- 6,612
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 ...
- 2,844
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 ...
- 71
7
votes
1
answer
583
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 ...
- 177
6
votes
4
answers
3k
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 ...
- 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
...
- 163
6
votes
1
answer
56k
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 ...
- 133
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-...
- 165
6
votes
3
answers
8k
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 ...
- 225
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 ...
- 833
6
votes
1
answer
966
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 ...
- 61
6
votes
2
answers
145
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 ...
- 113
5
votes
2
answers
74k
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 ...
- 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
...
- 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),...
- 2,032
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, ...
5
votes
1
answer
6k
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 ...
- 329
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 ...
- 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, '...
- 15.8k
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 | ...
- 181
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 ...
- 145
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 ...
- 195
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 ...
- 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 ...
- 115
5
votes
2
answers
22k
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 ...
5
votes
1
answer
549
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....
- 6,612
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 ...
- 151
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 ...
- 43