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.
53
questions
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
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
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
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
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
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
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
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
3
votes
1
answer
499
views
Implementing a PIVOT query
How can I pivot the following SELECT statement:
SELECT sc.CID,
sc.CodeName as OverviewText,
scRAG.CodeName as RAGStatusText
FROM StatusCode ...
- 2,032
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
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
3
votes
1
answer
8k
views
Mysql Pivot rows into dynamic columns with unknown number of columns
I have an output of a query as follows:
bid | code | amount |
-----------------------------
2915 | BF | -10700.00 |
2915 | YQ | -300.00 |
2915 | YR | 0.00 |
2915 | YM ...
- 131
1
vote
1
answer
620
views
SQL pivot/transpose/row-column non-proprietary
Maybe this question has been asked already (it should
be quite simple). I've Googled loads of phrases
(PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is
TSQL this, TSQL that and or Oracle ...
user100406
0
votes
1
answer
3k
views
Access: Making a Pivot Table with Multiple, Same Values as Columns
First post ever, thanks for having me!
Currently we have an Access database connected to two access control devices tracking hours for different statuses: Check In, Check Out, Break Out, Break In, ...
- 3
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
4
votes
2
answers
25k
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 ...
- 65
3
votes
1
answer
6k
views
How to perform a pivot on multiple columns
I have a list of data that includes values and the time those values were recorded. I would like to pivot this data so that i have as columns the individual types of observations and the date/time ...
- 444
3
votes
1
answer
6k
views
Add column with a sum total to crosstab() query in PostgreSQL 9.0
Following on from my previous question:
Creating crosstab() pivot table in PostgreSQL 9.0
I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create ...
- 145
3
votes
1
answer
19k
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 ...
- 211
2
votes
1
answer
13k
views
One to many relationship query result in a single row and dynamic multiple columns
In a one-to-many relationship query, I would have a single row and dynamic multiple columns.
In this example the expected result is to have for each owner all the associated phone numbers in a single ...
- 33
2
votes
2
answers
5k
views
How to Pivot Multiple Columns in SQL Server
I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.
I have this data:
That I'm trying to get into a format similar to this:
For ...
- 33
2
votes
1
answer
930
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 ...
- 9,630
1
vote
2
answers
9k
views
Select Multiple Values from Same Column; one sql statement and split into separate columns
This post derives from following -
Select Multiple Values from Same Column; one sql statment
Based on this query -
SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id)
FROM t
WHERE meta_key ...
- 11
1
vote
2
answers
5k
views
Transforming multiple rows into a single row
In my company, I need to join table A to table B:
Table A is the sources table
Table B is the empty table
I want to know that how can I do it and the result like table B?
Source table A:
ID ...
- 21
0
votes
3
answers
303
views
Using start and end event logs to create a table/view containing spans between the times of each log
To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following:
-------------------------------------
| user | event | team | timestamp |
-...
- 101
-1
votes
1
answer
81
views
SQL Server Rows to Columns Dynamically
Below are my table and query details.
Table definition
create table #ReviewData
(
ProjectId int,
ReviewId int,
ReviewDataPointKey varchar(max),
ReviewDataPointValue varchar(max)
...
- 11
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
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
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
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
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
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
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
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
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
4
votes
1
answer
8k
views
Security permission to only read SSAS Cube with Excel
I have an SSAS database set up on a SQL Server 2008 server. I have a Role set up with a group of users that I want to be able to access the Cubes in that database. I set up "Read definition" and "...
- 3,028
4
votes
2
answers
8k
views
Dynamic dates Pivot
Sample Data from StartKPA table:
I wrote the following query:
SELECT *
FROM
(
SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN '>80' WHEN (EndKPA*100) ...
- 43
3
votes
1
answer
151
views
Name for table with metric_name and metric_value columns
A typical database table might look something like this:
date-------geography---clicks---cost---conversions
___________________________________________________
1/1/2010---Kansas------56-------12-----...
- 31
2
votes
1
answer
53
views
Help with a pivot query
I've got data that goes sort of like this:
Time Name country
12 steve brazil
12 mary brazil
12 kate usa
12 john usa
13 paul canada
13 tim canada
...
- 23
2
votes
3
answers
3k
views
Pivot, categorize and group by
This question is similar, yet different, to another DBA SE question I have here: Group by NAME and YEAR, categorize NAME
I have a FINANCE_SOURCE table:
+-----------+------------+-------------+-------...
- 1,447
2
votes
1
answer
6k
views
Separate values into multiple columns
ID
Contact No
Ext
Type
0001
75865558
123
work
0001
207586558
NULL
home
0001
207586559
NULL
cell
0001
746655558
321
work
0002
946655558
323
work
0002
2356841
NULL
home
0003
6655558
NULL
cell
I ...
2
votes
1
answer
6k
views
How to merge multiple rows into one row with SQL? [duplicate]
Originally, I have one table, storing UserID and the corresponding value based on Year, Month, as shown below:
Now, I want to 'merge' the rows according to the Year and Month value and to store them ...
- 315
1
vote
1
answer
148
views
MySQL DB design for very long growing row size
My data has the property to grow in the row direction and the column direction at the same time. The size will grow in both directions without limit while I do more research.
The number of columns ...
- 113
1
vote
2
answers
1k
views
Select column to be inserted based on input column value
Assume the following two tables (and ignore the quality of the database design):
Table 1 has columns id, sequence_number, value: it contains many rows.
Table 2 has columns id, seq1, seq2, seq3, seq4, ...
- 2,033
1
vote
1
answer
179
views
Rotate table for year-over-year comparison
I have a road_insp table:
create table road_insp
(
insp_id int,
road_id int,
insp_year int,
condition number(10,2)
);
insert into road_insp (insp_id, road_id, insp_year, condition) ...
- 1,447
0
votes
1
answer
822
views
Dynamic Pivot with overlapping years [duplicate]
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 ...
0
votes
3
answers
143
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
...
- 2,484