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
0
votes
1answer
50 views

SQL don't display ORDER BY column

I'm quite new to SQL programming, so i apologize in Advance for all the irrelevant Information and stupid Questions :) I have a database in Microsoft SQL Server Management Studio. I select data from ...
2
votes
1answer
39 views

How do I separate the values of one column into multiple columns In MS SQL-Server

ID Contact No Ext Type -- ---------- ---- ---- 0001 75865558 123 work 0001 207586558 NULL home 0001 207586559 NULL cell 0001 746655558 321 work 0002 ...
0
votes
1answer
22 views

MySQL show data from Rows to columns

I am stuck at getting a query with joining 2 tables. Below is the database structure. What I want to achieve is to list all the information from one table with join on the second table but it has 3 ...
1
vote
1answer
13 views

Getting 2 columns from sets of 2 rows in Postgresql

So I have my data organized as: id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date 1 | netIncome | 100 | stockid1 | 2019-05-01 2 | totalRevenue |1000 | stockid1 | ...
0
votes
1answer
25 views

How to query in such a way that values becomes columns? [duplicate]

Having the table foo like: | id | name | | 1 | Foo1 | | 2 | Foo2 | And the table bar like: | id | foo_id | key | value | | 1 | 1 | a | foo1a | | 2 | 1 | b | foo1b | | 3 | 2 |...
0
votes
3answers
96 views

Select with dynamic columns

I have an InvoiceHeader, InvoiceItem tables InvoiceHeader content: InvoiceId InvoiceNumber BusinessId BusinessName BranchId 1 1 10 BSN INC 1 ...
0
votes
0answers
24 views

Pivot returning null columns in MariaDB / mySQL

I'm trying to pivot a table in a Wordpress Woocommerce database, just for some adhoc reporting, using Case statements. It works well for most columns (the shorter ones), but columns returned with ...
1
vote
1answer
45 views

Using PIVOT to convert multiple rows and columns

So my current query output is as follows: I would like to consolidate all of these rows to just one long row like so: MyID Customer relc_rate relc_consumption relc_daysofservice ...
1
vote
1answer
33 views

SQL sort by sum of two different where clauses in a join

table1 ID | Name 1 ID1 2 ID2 3 ID3 table2 ID | table1ID | Values 1 1 3 2 2 3 3 3 2 4 1 3 5 2 1 6 3 2 7 1 ...
1
vote
1answer
25 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 (...
0
votes
0answers
17 views

How to show dynamic data row to data column in mysql or within php?

I have problem with query mysql. So I have database table and I've tried with my query like this SELECT GROUP_CONCAT( 'MAX(IF(day(tanggal) = ', day(tanggal), ', presensi, NULL)) ...
1
vote
1answer
38 views

Dynamic pivoting in PostgreSQL

I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. I want all those products to be in a separate column, ...
0
votes
2answers
30 views

Query Joining on Column headings

I have Survey results given to me in a massive table. (300+ columns) A list of questions is provided in a table, and the Survey results in another. Questions. +-----+-----+-------------------------...
2
votes
1answer
29 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 ...
0
votes
1answer
61 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 ...
-1
votes
1answer
34 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
0answers
27 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
1answer
27 views

Group Concatenate In mysql

Please can some one help me out of this. I have two tables, One is like this And the other is like this How can i use one mysql query to insert all users subject, total scores of those terms to ...
0
votes
1answer
224 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
1answer
28 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, ...
2
votes
2answers
43 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
2answers
59 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
1answer
80 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 ...
0
votes
0answers
20 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 ...
2
votes
3answers
52 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 ...
2
votes
2answers
58 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
1answer
42 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, ...
1
vote
0answers
33 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 ...
0
votes
1answer
130 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, ...
0
votes
0answers
237 views

Display Dynamic Pivot MySql Table to PHP

i need help on how to display this on html or php. i have this dynamic pivot table code. SET @sql_dynamic:= (SELECT GROUP_CONCAT(DISTINCT CONCAT('if(sum(if(attendance_date = "', ...
0
votes
1answer
19 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
1answer
45 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 ,...
0
votes
0answers
50 views

Mysql How to get joined tables data horizontally

I'm trying to get the addresses with that compairing amounts horizontally which found twice in address table. I'm getting the address name and product name but couldn't figure out how to get amounts ...
1
vote
2answers
131 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
1answer
42 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:
2
votes
1answer
57 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 --------------------...
0
votes
1answer
215 views

Transposing repeated rows of datetime into columns in SQL 17

I am working with a dataset of inpatient data in SQL version 17. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, ...
-1
votes
1answer
55 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 ...
-3
votes
1answer
67 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, '...
1
vote
0answers
37 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. ...
0
votes
1answer
45 views

How to pivot tables view/results from row to columns

I have e-mail aliases tables that looks like this: +---------------------------------+--------------------------+-------------------------+-------------+---------------------+---------------------+---...
0
votes
1answer
26 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, ...
0
votes
2answers
151 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
2answers
315 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 ...
1
vote
2answers
78 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 ...
0
votes
1answer
543 views

Oracle convert multiple rows to column using pivot

I have a table that contains the values +-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+ |...
0
votes
1answer
254 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',...
2
votes
1answer
345 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 | ...
0
votes
2answers
274 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-...
2
votes
1answer
84 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 ...