Skip to main content

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
4 votes
1 answer
2k views

Tips on reshaping data, pivot-like?

I have a table of levels and years that looks roughly like this: INSERT INTO demo(first_name, rank, year) AS VALUES ('Peter',2,2017), ('Robert',3,2016), ('Evelyn',2,2017), ('Rand',3,2017), ('...
Amanda's user avatar
  • 305
4 votes
1 answer
39k views

MySQL - How to group data by Day and Hour

I have some problem in getting the query for this sample format. My idea is select hours on that day and group it by day or select all the 7 days and group it by hours? here is the format:
user77142's user avatar
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,048
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
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
1k views

Oracle pivot on a column with delimited data

My data is like: keycol,col1,col2,col3 1,a;b;c,some data,some other data 2,x,some data,some other data 3,y;z,some data,some other data Where a column is delimited in the source system with ...
Cade Roux's user avatar
  • 6,602
4 votes
1 answer
4k views

While using Cross Apply, can I force "empty" records to appear?

I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS ...
Jeff.Clark's user avatar
4 votes
1 answer
3k views

How to match column names and values from joining and related tables?

I have the following DB structure: cars: id make features: id name cars_feature: id car_id feature_id value I want to be able to select all cars with all features and return results with ...
MarkL's user avatar
  • 165
4 votes
2 answers
71 views

Add column for subsequent values for a record

I have a table structured as follows: CREATE TABLE t ( HHID varchar2(1), INDIV_ID int, PRIM varchar2(1) ); insert into t values ('A', 10, 'Y'); insert into t values ('A',...
berlin's user avatar
  • 53
3 votes
2 answers
3k views

Merge multiple rows into fewest number of rows of distinct values

In SQL Server does anyone know of a nice way to merge/flatten multiple rows of data into the fewest possible rows containing only the distinct non null values. I.e. A dataset like this: As this: ...
JamesP's user avatar
  • 127
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
3 votes
2 answers
4k views

Pivot with 2+ columns (using CROSSTAB?)

I have a table deflator that is defined as: Table "deflator" Column | Type | Modifiers -------------+-------------------+----------- country_code | smallint ...
dw8547's user avatar
  • 947
3 votes
2 answers
2k views

How can I compute the TOTAL of multiple COLUMNS from a PIVOT?

I have the following ORACLE SQL table/data: A DATE_FIELD C D 1 01-JAN-13 hello 0 2 01-FEB-13 wonderful 10 3 20-MAR-13 world 20 4 21-APR-13 tree 10 5 21-JUN-13 tree 30 6 01-JUL-13 world 10 7 30-...
xentwo's user avatar
  • 33
3 votes
1 answer
3k views

Pivot with rollup in sql server

Is it possible to use a cursor to create a dynamic table and then use those columns to aggregate data in SQL server 2008? For example take the following table. CREATE TABLE Billing ( BillingId ...
Mark D's user avatar
  • 1,110
3 votes
2 answers
4k views

Rotating a table with multiple value columns

I'm not sure if this can be done with crosstab(). I've been using this questions/answer as a reference, but am having difficulty putting together the query. My table looks like this, just that I have ...
AGS's user avatar
  • 145
3 votes
1 answer
6k views

Indexed VIEW in SQL Server with PIVOT

A simplified version of my problem is as follows: I have three tables Records, Inputs, and Outputs. Records Id DTS   1  2015-01-01 00:00 ... Inputs Id RecordId InputId ...
Bryan's user avatar
  • 53
3 votes
2 answers
930 views

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

I have something that looks like this (SQL Fiddle here): Date Location Value 1/1/2012 Baltimore 36 1/1/2012 Houston 36 1/1/2012 Chicago 55 2/1/2012 Baltimore 49 2/1/...
quietmint's user avatar
  • 143
3 votes
3 answers
11k views

Pivot time series into date period columns with array aggregate cells

Given a time series of events where each event has a successful or unsuccessful outcome, how do I pivot the ratio of events by entity and time period columns with an aggregate array cell value? I ...
Petrus Theron's user avatar
3 votes
1 answer
723 views

How to Pivot Multiple Columns

I have the following data, Year DRC DISP Id 0 140.21 0.00 5808 1 112.37 1.00 5808 0 140.21 0.00 ...
peter's user avatar
  • 2,187
3 votes
2 answers
25k views

Dynamic Oracle Pivot_In_Clause

I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this: WITH PIVOT_DATA AS ( SELECT * FROM ( SELECT USERNAME, GRANTED_ROLE ...
Chris.V's user avatar
  • 231
3 votes
1 answer
343 views

Properly pivot an XML column with XQuery

I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format. To get started, this is what the XML column looks like in SQL: <Model ...
JF Landry's user avatar
3 votes
2 answers
9k views

Subquery for column names of tablefunc crosstab queries

Is it possible to get the values for the names of the columns for the crosstab query from a subquery? CREATE TABLE mytable(employee_name text, month date, allocation double precision); INSERT INTO ...
sav's user avatar
  • 51
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
3 votes
1 answer
3k views

PIVOT function Says Invalid Column

I'm trying to teach myself to use the SQL Server 2012 PIVOT function, using the old example pubs database from Microsoft, I have written this code.. SELECT [au_lname], [CA], [OR], [IN] FROM (...
StevieJD's user avatar
3 votes
1 answer
2k views

Find time difference in different rows with a condition [closed]

I have a table showing statuses (working, not working) for several devices (ordered by device desc, datetime desc): rownum device datetime status ---------------------------------...
Gyuzal's user avatar
  • 235
3 votes
2 answers
6k views

Dynamic Parameters In Pivot

I know the premise of a PIVOT() this syntax produces it perfectly for me: Select * FROM ( Select field1 ,annual ,dv As Amt FROM Testing ) x pivot ( SUM(Amt) ,for annual IN ([2014],[...
user2676140's user avatar
3 votes
1 answer
4k views

The multi-part identifier "d.dept_name" could not be bound

I want to create a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count of emp.ID is coming from emp ...
Priyanka's user avatar
3 votes
1 answer
3k views

Postgres: "Pivot" unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, timestamp,...
Steve Pike's user avatar
3 votes
1 answer
12k views

How to create a table in MySQL with automatic numeration of columns?

Example. My data is from Table name:BIKE Bike_No Repair_Date Repair_Cost -------- ----------- ------------ ABC1234 2013-01-05 50.00 BMX5678 2013-02-04 75.00 ABC1234 2013-01-...
user avatar
3 votes
2 answers
1k views

Showing data in a table horizontally

I have a table that stores sensor data (in SQLite) and looks like this; TABLE Timestream: idTimestream PK autoincrementing integer, time int not null, value float not null, idSensor integer not ...
James's user avatar
  • 531
3 votes
1 answer
2k views

pivot on date and count values by hour

I hope this makes sense. I have a database which logs the day and time a row was inserted. I have a query which will select and count the number of insertions per hour. I would like to pivot this so ...
M-Corp's user avatar
  • 61
3 votes
1 answer
150 views

Crosstab Pivot or not?

I am have difficulty wrapping my head around the following issue. I currently have a set of data in one table as shown below: As you can see, there is an invoice ref number along with a date, and ...
Dav.id's user avatar
  • 133
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
504 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
3 votes
1 answer
2k views

How to combine a parent with all children in the same row

SQL Statement Question: I have two tables PARENT and STUDENTS. PARENT Table ParentID | FName | LName | StudentID ---------+-------+-------+---------- 1 | John | Doe | 11 1 | ...
Rols's user avatar
  • 31
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
3 votes
2 answers
13k views

How to flatten and pivot a normalised hierarchy table

I have an OrgChart table that looks something like this: PositionCode PositionName ParentCode Level AA0001 CEO 0 AA0002 CFO AA0001 1 AA0003 ...
James Gale's user avatar
3 votes
2 answers
5k views

Re-order columns values based on values in a table

I would like know, how can we re-order the columns in ascending or descending order, while retrieving the data. Suppose the table contains the data as below with no primary key/unique key constraints:...
user3688422's user avatar
3 votes
1 answer
6k views

MySQL pivot query trouble sorting column by total

I am trying to return results for a matrix that represents the net weight that a country imports from another country. I have managed to write a query that produces a pivot type table in mysql all ...
Matt Fricker's user avatar
3 votes
1 answer
2k views

Display multiple IP address values for one system in a single row

I cannot for the life of me get the systems with multiple IPs to display under one row. Below is the current SQL statement I have. systems with more than one IP show up as duplicated on the report. ...
Eliza's user avatar
  • 33
3 votes
2 answers
3k views

Select into specific array positions with array_agg()?

Is there a way to set values in specific positions inside an array, based on information from other columns? (Postgres 9.3 or later.) For example, I would like to select an item and its stock ...
plaes's user avatar
  • 175
3 votes
2 answers
7k views

Use values from one column as column names and sum data from another table

I'm going to do my best to describe this. So I have 3 tables as follows: project id title department_id 1001 Project One 50 1002 Project two 50 metric id name 101 Number of exhibits 102 Number ...
Mondi Koci's user avatar
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
3k views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
user48768's user avatar
3 votes
1 answer
31k views

Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

I've developed a timesheet recording system, that allows users to record: A Period of time (9:00 to 12:00) What they did during that time (DutyActivity: 1 hour report writing, 2 hours training) When ...
Peter Bridger's user avatar
3 votes
0 answers
90 views

How to create a view that returns all child records in a single row without aggregation?

I am an electrical engineer developing a parts library database and explorer tool for internal use in my organization. If anyone is familiar with OrCAD schematic capture tools, I am essentially ...
jde0503's user avatar
  • 31
3 votes
0 answers
63 views

Dynamically swap multiple rows with database columns

First let me start by saying that I've done intensive research before asking here. I have a table datatable that looks like this: +----+------+-----+-----+-----+-----+-----+-----+-----+-----+-----+----...
JohnTheWalker's user avatar
3 votes
2 answers
3k views

Merging multiple rows into one in MySQL

I have the following table : Column1 Column2 Column3 Column4 Account One Two Three Data1 Acc1 One Two Three Data2 Acc2 One Two Three Data3 Acc3 Five Six Seven Data3 Acc4 Five Six Seven Data1 ...
dbase964's user avatar
3 votes
0 answers
1k views

Query to split data in a column into multiple columns using id in another column (pivot table; crosstab)

Database engine: Server version: 10.2.16-MariaDB-10.2.16+maria~xenial mariadb.org binary distribution I have a database table defined as follows: CREATE TABLE `disktemp` ( `sample_time` datetime ...
Mausy5043's user avatar
3 votes
0 answers
4k views

how to get a sum total of each column and row - postgresql [Crosstab or Case]

I have to count the records according to the criterion, they are much more filters but I have summarized. I could use either of two ways. 1# SELECT * FROM crosstab('SELECT dim_espacio.desc_estab, ...
raul's user avatar
  • 31

1
2
3 4 5
10