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
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 ...
user16947's user avatar
  • 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 |...
Khairu Aqsara'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
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
  • 62.7k
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
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 ...
tmachielse's user avatar
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 ...
Federico Giust's user avatar
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
Adam's user avatar
  • 151
3 votes
1 answer
500 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 ...
kacalapy's user avatar
  • 2,052
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
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
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 ...
Gunnrryy's user avatar
  • 131
1 vote
1 answer
629 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 ...
user avatar
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, ...
Leo's user avatar
  • 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 -------...
Kaishu's user avatar
  • 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 ...
Rafey's user avatar
  • 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 ...
JoeNahmias's user avatar
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 ...
Bhuban's user avatar
  • 211
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 ...
daniel franklin's user avatar
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 ...
Alex's user avatar
  • 33
2 votes
1 answer
933 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 ...
Craig Efrein's user avatar
  • 9,640
2 votes
2 answers
6k 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 ...
Ted O'Brien's user avatar
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 ...
user2680821's user avatar
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 ...
user85312's user avatar
0 votes
3 answers
310 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 | -...
MJ.'s user avatar
  • 101
-1 votes
1 answer
85 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) ...
Mahi's user avatar
  • 11
11 votes
2 answers
22k 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
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
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 ...
Matthew Sontum's user avatar
10 votes
3 answers
118k 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,360
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
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
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
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
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
2 answers
23k 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
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
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) ...
SqlUser's user avatar
  • 43
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 "...
DForck42's user avatar
  • 3,038
3 votes
1 answer
152 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-----...
user2989038's user avatar
2 votes
1 answer
7k 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 ...
Ersi Nurcellari's user avatar
2 votes
1 answer
54 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 ...
Anony's user avatar
  • 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: +-----------+------------+-------------+-------...
User1974's user avatar
  • 1,457
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 ...
zeroflaw's user avatar
  • 315
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, ...
Franck Dernoncourt's user avatar
1 vote
1 answer
149 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 ...
hbadger19042's user avatar
1 vote
1 answer
180 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) ...
User1974's user avatar
  • 1,457
0 votes
3 answers
144 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 ...
Vaccano's user avatar
  • 2,496
0 votes
1 answer
464 views

How to handle database of multiples languages and countries

Imagine that I have a database of books. I would like to store multiples informations for multiples languages and countries. Imagine that I would like to support 10 countries and 10 languages. I want ...
user3416510's user avatar