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
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
0
votes
1
answer
3k
views
Query Pivot Date column into Rows [closed]
My problem is pivoting one single Date column into my row headings while all my current Column headings become rows.
My Column headings are [Date], which contains 50 years of days (from an awesome ...
- 11
-1
votes
1
answer
62
views
Generating Pivot Data in SQL Server
I have a SQL Server query that generates the following result:
| Department | Round Type | Total | Passed |
|:-----------|------------:|:------|--------|
| MD | Spring | 10 | 8 ...
0
votes
0
answers
38
views
How to convert native date value column in a pivot
OPEN SYMMETRIC KEY TblUserDefinedFieldKey DECRYPTION BY CERTIFICATE mDNACert
SELECT * FROM
(
SELECT u.public_id, u.instance_id, u.state, u.tag, udf.name AS [user_defined_key],
CONVERT(NVARCHAR(...
-1
votes
1
answer
32
views
Pivot and Insert
I have two tables. One is like this
The other is like this
How can I use one MySQL query to insert all users subject, total scores of those terms to table two. I have manage to insert only first ...
1
vote
1
answer
9k
views
Combining multiple rows into a single row with multiple columns
I am trying to do a manual query for WordPress postmeta table which would combine two types of postmeta into one with multiple columns by post_id.
This is the structure of table:
-------------------...
0
votes
1
answer
132
views
Change Order in Pivot Table
I write some pivot query in oracle but I have some problem with order data :
Select * FROM
(
SELECT
ve_nazwisko PH,
odbiorca,
CONCAT(CONCAT(nazwa1,' '),nazwa2) FIRMA,
segment2017,
...
- 1
1
vote
2
answers
2k
views
SQLServer - Multiple PIVOT on same column
I have a table data like the below with Type Regular/Irregular from January to December.
I need a query to do this data like the below table: January with below divided into two-jRegular & ...
- 11
2
votes
2
answers
806
views
Selecting the same column from Different rows Based on Different Criteria
My table creates a row for each customer name for the unique customer number.
CREATE TABLE #src(Number int, name varchar(32), seq bit);
INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(...
0
votes
2
answers
63
views
How to join these queries?
So I currently have this query:
SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)
FROM reserveringen r, voorstelling v
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r....
0
votes
1
answer
789
views
MySQL - Split results of count function into columns derived from a separate column
The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just ...
- 1
0
votes
0
answers
119
views
Query to get the "Item -- (1,n) --> Attributes" relation table?
given is the following database scheme and I am wondering how to build the appropriate query on a Postgres system.
It is defined that Items can have multiple (1 -> n) Attributes which relation is ...
- 1
2
votes
3
answers
72
views
How to group many information une one select
There are my database
articles
id | art_code | art_name
1 | 5678 | computer
2 | 0987 | display
3 | 8547 | keyboard
action
id | code | action | qte
1 | 5678 | IN ...
- 33
2
votes
2
answers
242
views
How to pivot a table for a set number of combinations? [duplicate]
Ok so I have a table of components and a colleague needs some data from this table in a different format, now I could do it with some python or java code but wondered if there is a simpler way in MS-...
0
votes
1
answer
62
views
Can I compress two rows with a difference in two columns into a single row with both columns?
I have a query that returns customer product information that shows which region a product is sourced from.
Query:
SELECT
prod.prod_id,
prod.is_source,
link.prod_link_disabled,
...
- 101
1
vote
0
answers
44
views
Repivoting rows of pivoted data with an dynamic amount of rows
I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here ...
- 11
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
0
votes
1
answer
26
views
SELECT , GROUP AND DISPLAY IN TABULAR FORM AND COUNTING INSTANCES
Answer to this query was received as -
SELECT location,
AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable
...
0
votes
1
answer
242
views
Pivot data type error
I have a table which needs to do pivot as column to row, but getting data type error,
I have below table data
Need to pivot and output should be like
I tried with query:
select prod As product_id ,...
- 1
1
vote
2
answers
2k
views
How can I count distinct columns inside a group?
I have the following query:
SELECT DISTINCT name, surname, id_number, gender FROM table1;
How do I filter by gender to get the gender breakdown of the results of the above query? Do I use a subquery?...
1
vote
1
answer
68
views
Pivot information - sql table
I was wondering if anyone could help me with pivoting data in sql? I am a newbies and need some help
I want to pivot data based on "LastLogOn" column. Is it possible to do in SQL?
From:
To:
- 11
2
votes
1
answer
66
views
How do I pivot to summarize my entries by a specific area?
I have a map where every row represents a bird and a row with the attributes shown in the excerpt of the attribute table:
round | area | date | species | behaviour
--------------------...
- 21
0
votes
1
answer
922
views
Transposing repeated rows of datetime into columns
I am working with a dataset of inpatient data in SQL Server 2017. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, ...
- 1
-2
votes
1
answer
341
views
How to get one column and select other column as horizontally
Given data of the form...
+-------------+--------------+
| Employee_ID | Candidate_ID |
+-------------+--------------+
| 1000 | 1 |
| 1000 | 2 |
| 1000 | 5 ...
- 1
-3
votes
1
answer
461
views
How to display multiple columns as rows + columns [closed]
I have this table and data:
CREATE TABLE #t1
(
[Year] int,
Student int,
Test char(2),
Score int,
Achievement varchar(32)
);
INSERT #t1 VALUES
(2014,1,'MA',697, '...
- 9
1
vote
0
answers
140
views
Dynamic rows and columns with Pivot and calculated value
I'm trying to build a pivot request but i don't undestand how it's applicable for my database. The value I need has to be calculate (A percentage) for each column and row.
I'm using SQL Server 2012.
...
- 11
0
votes
1
answer
52
views
How to pivot tables view/results from row to columns
I have e-mail aliases tables that looks like this:
+---------------------------------+--------------------------+-------------------------+-------------+---------------------+---------------------+---...
- 1
0
votes
1
answer
498
views
Sort pivot result
I just want to ask how to sort the result of a PIVOT in SQL Server.
When I use ORDER BY there is an error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, ...
- 3
0
votes
2
answers
904
views
Divide data in single column to multiple columns
I have source data like
Name Is_Trget
a Yes
s No
d Yes
f Yes
a Yes
S No
from this source data expected output is
Name Is_Yes Is_No
a 2 0
s 0 2
d 1 ...
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
2
votes
2
answers
378
views
Create 2D truth table from "cross product" of two tables via PostgreSQL view or function
I have a working Excel-based method for creating a truth table from two vectors exported from a PostgreSQL database. The process takes about 4 hours to complete due to a large number of VLOOKUP and ...
- 439
0
votes
1
answer
2k
views
Oracle convert multiple rows to column using pivot
I have a table that contains the values
+-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+
|...
- 129
0
votes
1
answer
772
views
SQL query to convert Column to Row using Pivot/Unpivot [duplicate]
My Select Table. I tried Pivot query to get my expected result but I was struct to generate the expected result.
WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
SELECT 'A',...
- 103
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 | ...
- 31
0
votes
2
answers
3k
views
How can I pivot this data using a cte or the pivot syntax?
Let's say I have these two tables in an MSSQL-Database:
Is there any way to create a query that result in a table like this?
There is no case where there are multiple Leader/Assistant in the TEAMS-...
- 111
2
votes
1
answer
4k
views
How can I pivot a table then join multiple tables and change values based on information between tables
The database I'm using has multiple tables storing different information types...it's very convoluted.
I'm trying to join this information into one table, with all of the details for a single ...
- 31
1
vote
4
answers
3k
views
Can Group By and Count on MySQL be optimized?
I have a 100 million row table on MySQL. I need to count rows for certain ranges and I have the proper indices for filtering rows. Let's say a SELECT statement returns 20000 results, but all I need is ...
- 151
0
votes
2
answers
577
views
how to change this dynamic rows to columns [duplicate]
I have following columns (it is dynamic, more rows in table with same ID).Type can be duplicate.
+--------------+---------+------------
| ID | TYPE | VALUE |
+--------------+---------...
- 101
2
votes
1
answer
3k
views
Firebird query pivot
I have a Firebird sql query that returns 3 fields: Surname, Name and date as (YYYY-MM-DD HH:mm:ss)
select Surname, Name, "WHEN" from users
left join attendant on (users.id = attendant.userid);
This ...
- 337
0
votes
1
answer
42
views
How to find value for specific dates for a process stored in table
I have a table called as tab where we store all the process running on machine in below format
proc_name start_date Duration
proc1 24-Jul-18 0:0
proc2 24-Jul-18 0:0
...
- 1
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
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 ...
- 93
2
votes
2
answers
631
views
Pivot values to columns
I'm trying to create a view that groups score data for each week and displays it for each user but I don't know which commands to use.
Sample data:
// scorecards table
week | user | score |
1 ...
- 123
1
vote
0
answers
38
views
need help with multiple column aggregate using pivot in mssql
below is the temporary table for class assessments. this table contain list of class assessment which include the assessment code, date of assessment, total item and passing percentage.
create table #...
0
votes
0
answers
70
views
crosstab query : count a number occurence in a row
I have a crosstab query that shows 1 and 0 in the value area.
Function 1 Function 2 ..Function n
project 1 1 0 1
project 2 0 0 1
..
project ...
- 11
-1
votes
1
answer
746
views
How to do looping and use case when logic from dynamicaly variable in mysql
I'm new in mysql
Before I have question and have the answer from someone. But I need a improve again from the query.
This is my sampling data on mysql db
table test
With this query on below from ...
- 27
2
votes
1
answer
251
views
PIVOT with SQL Server
I have SQL Server table data returning the below data. (Green text is a note on the value of each cell in 'Outcome' column)
I want to PIVOT that query result so that it uses the latest 'ReceivedTime' ...
- 533
2
votes
1
answer
4k
views
Default values for crosstab results
I'm trying to write my first crosstab query in Postgres. Some of the results have no entries and so the rows that are returned have null entries. How do I set a default value for those entries?
Here ...
- 163
1
vote
1
answer
345
views
How to pivot a column showing its content as new columns
I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common.
I have a table like the following one:
direc ...
- 29
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
---------------------------------...
- 225