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
0answers
19 views

Oracle: Select values and join result from multiple rows in one with seperat cells

I have to create a select Statement, which bring me a multiple row result in one. For better understanding here my table Id | Name | Typ | Date --------------------------------- 1 | John | ...
-1
votes
1answer
24 views

Pivoting date data [duplicate]

How to join all different rows with the same ID but a different DATE and return it as a unique row with new columns like DATE_0, DATE_1, DATE_2, ...? Consider this table: +----+------------+ | ID | ...
-1
votes
0answers
15 views

Return multiple columns from subquery as Columns [closed]

Here is my data. Products: Prod_ID | Prod_Desc | Base_Unit_ID 1 | Custom Product | 1 UOM: UOM_ID | Desc 1 | Piece 2 | Box 3 | Case UOM_Conversion: Prod_ID | from_Unit_ID | ...
-1
votes
1answer
27 views

Sql Server 2014 - Rows to Columns convert Dynamically- [duplicate]

Below are my query and table details create table #ReviewData ( ProjectId int, ReviewId int, ReviewDataPointKey varchar(max), ReviewDataPointValue varchar(max)) Insert into #ReviewData values (...
-1
votes
1answer
68 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) ...
1
vote
1answer
33 views

Multidimensional pivot in postgres without crosstabs

I have a bunch of subqueries that I've used to get the following data structure (that I've made in a table for brevity): CREATE TABLE newtable ( id int8 NULL, start_of_week varchar NULL, ...
-1
votes
1answer
22 views

I want to transpose Column values into Row of Specific values like 'GType'(values as rows) as mentioned in the below example

Example Table: Result Table: I want to transpose Column into Rows of Specific Row values like 'GType'(values) as mentioned in the below example. I want a results to be in the below example format. ...
1
vote
1answer
26 views

Invoice days and amount with Distinct Value [closed]

I am having a billing table like as above. I need to get a billing age wise report by using distinct of Branch, UserId, Customer, BillType. I need to wirte a Stored procedure for getting the ...
9
votes
2answers
659 views

Is it possible to PIVOT on a LIKE statement

Is it possible to group by elements (as in COLUMN LIKE='Value%') in a PIVOT table? I have a table [DBT].[Status] which contains various statuses (of databases, instances, etc.) and don't want to pivot/...
1
vote
1answer
27 views

Preparing a query to paste into excel to get growth by month

I have a table that looks like this: |DB | DATA_MB | LOG_MB | | | DB1 | 4,4375 | 2,25 | 2019-08-06 10:37:49.160 | | | DB2 | 8 | 0,5 | 2019-08-06 10:37:49....
0
votes
1answer
77 views

Join one-to-many field in a single result without concatenation

I suffer from the same problem as in Concatenate one-to-many field in single result?, but I need a result where the 'many' fields occupy their own columns and aren't concatenated in one column. Any ...
-1
votes
1answer
30 views

append the text to postgres crosstab query [closed]

Am using below crosstab query: SELECT * from crosstab('SELECT id,task_id, status_id FROM table ORDER BY 1,2') AS final_result(id character varying(50), task1 int,task2 int,task3 int,task4 ...
0
votes
1answer
39 views

SQL Join to put rows into columns

I have two Tables TABLE 1 ID Name 1001 XYZ 1002 ABC TABLE 2 ID GAME 1001 A 1001 B 1002 A I want the outcome of two tables join as ID NAME GAME1 ...
0
votes
0answers
16 views

How to use PIVOT keyword in Postgres [duplicate]

I have one table in Postgres which has following columns: processtransactionid bigserial, TenantID uuid, ProcessInstanceId bigint attributename varchar(250), attributevalue varchar(250), ...
-1
votes
1answer
39 views

Displaying data horizontally in mysql [duplicate]

I have this table in my db id | major | code_lesson | files Which the contents look like this id | major | code_lesson | files 1 | Math | MAT12 | mat.pdf 2 | Socio | SOC15 | soc.pdf ...
0
votes
0answers
23 views

mySQL query to create a matrix of a join table [duplicate]

I have two tables and a many to many join table, I would like to use mySQL to create a matrix counting the intersection. CREATE TABLE `wp_app_schools` ( `id` int(11) NOT NULL AUTO_INCREMENT, `...
2
votes
1answer
40 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 ...
0
votes
1answer
55 views

T-SQL query to merge all rows into one row [duplicate]

I have a T-SQL query, which pulls details from several locations (Something like below): IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable; CREATE table #mytable ([Locno] varchar(...
2
votes
1answer
44 views

PostgreSQL ORDER BY clause effect different number of rows to return in pivot table

I've worked on "Seven Databases in Seven Weeks" recently, and when implementing the the task with building a pivot table using crosstab() - the task was already described here: https://stackoverflow....
0
votes
2answers
727 views

Monthly Date and Hour wise report generation using PHP MYSQL

Attendance Portal in PHP I am facing problem at Final Attendance Report Generation I'm stuck at the final stage of the Project: I have a Table- attendance, If a student is Present I mark it as 1 if ...
0
votes
0answers
14 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
0
votes
1answer
60 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
46 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
25 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
17 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
28 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
103 views

Select with dynamic columns

I have an InvoiceHeader, InvoiceItem tables InvoiceHeader content: InvoiceId InvoiceNumber BusinessId BusinessName BranchId 1 1 10 BSN INC 1 ...
1
vote
1answer
63 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
38 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
57 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
46 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
508 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
32 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
334 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
37 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
31 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
28 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 ...
0
votes
1answer
677 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
32 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
55 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
277 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
24 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
57 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
64 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
47 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
36 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
401 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
390 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 = "', ...