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
0
votes
1
answer
67
views
Pivot on datetime with matching multiple column
I have the following table with column school, state, subject and timedate.
I would like to have output with column school, state, act and english
filter subjects to only Act and English
pivot table ...
- 1
0
votes
1
answer
189
views
Query to insert records from one table into another table based on the column of the first table [duplicate]
I have a table with the structure below :
Table(customer_Num int , Channel_Id Varchar(50) , Branch_Code int)
and some example data for this table is :
Table
...
- 1,412
6
votes
3
answers
8k
views
How to pivot data in SQLite
I've a table which contains text data containing a bunch of translations for different language. Each translation is for a specific label.
I need to generate a pivot table so to get quickly what's ...
- 225
1
vote
1
answer
3k
views
Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)
I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.
with checked_adgroup AS (
...
0
votes
2
answers
70
views
How to improve multiple case when query performance?
I have the following query which counts when the event_time is equal to the day of the month.
I also have indexes on the id of each table, template_name, from_email_address, event_time, event_status....
1
vote
1
answer
35
views
Transposing a row data in multiple rows based on multiple columns
I have data in the database in the below format
And I want output data in this format
BudgetCategoryId | CostCenterDepartmentId | CostCenter | AccountNo | Amount | Timestamp
714 | ...
- 111
1
vote
1
answer
998
views
Convert rows into columns and group some colums
I saw a lot of example on how to convert rows into columns and I was able to accomplish that.Here is the table that have:
Userid action date TRACKING zip Subject
...
- 49
-3
votes
1
answer
40
views
Reformatting Date with Pivot or Unpivot [closed]
I'm struggling trying to covert the format of a table with the following fields:
Customer|Fiscal Year| September Amount| October Amount|November Amount|...
to:
Customer|Fiscal Year|Month|Amount
I'...
2
votes
2
answers
79
views
Converting properties to columns
I have a list of types:
SELECT * FROM type;
id name
----------- --------------------------------------------------
1 person
2 other god
3 location
4 ...
- 165
0
votes
0
answers
32
views
Pivot applications per student [duplicate]
I have two tables:
STUDENTS
APPLICATION
A student can apply to 1-3 programs. Student_ID is a column on Application.
Table structure
APPLICATION
ID | Student_ID | ProgramName
1 | 65432 | ...
- 101
-2
votes
1
answer
496
views
crosstab dynamic in mysql
this is output from query true like this:
bln bt cl kj ma pj sb sn st wh
1 421 465 437 474 196 29 146 392 37
2 398 443 328 462 181 0 30 438 0
3 209 258 105 298 51 0 0 213 0
...
- 11
1
vote
0
answers
757
views
How to properly name a database table
First of all, I understand this question may seem opinion-based, but I know there are some standards for naming conventions and I would like to know which is the best and why. It's not so complex ...
- 111
-1
votes
2
answers
879
views
Ignoring null values in columns. pivot and unpivot
I have below data format
I am looking for below data format
I try all options but unable to reach the below result.
Query :
create table dbo.FS_Daily_HSE_SAPS_Audit(
formId int default ...
- 101
0
votes
1
answer
241
views
Dynamic Pivot Table syntax error
What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.
mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> GROUP_CONCAT(DISTINCT
...
- 101
1
vote
1
answer
26
views
Aggregating Attributes
I have a product review table in an oracle 12.1 EE database that has different attributes rated on a scale of 1 through 5. How do I query this table to count up all of the reviews and group by the ...
- 631
1
vote
2
answers
44
views
Add data from rows to different columns if more than 1 row have a partial match
I tried to find a solution but do not find anything to add multiple columns from partially matching rows.
Data:(Maximum of 3 programs per person)
name surname program
Jan Peters ...
0
votes
1
answer
61
views
Convert rows into columns MySQL DataBase
I have two tables and I would like to generate a query relating the tables.
Until now I have this query:
SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID
but I ...
- 105
0
votes
1
answer
46
views
Create dynamic column [duplicate]
My table has this data
START_DATE | STOP_DATE | SAMPLE_DATE | SAMPLE_VALUE
:-------------------- | :-------------------- | :--------------------- | -----------:
11/...
- 101
-3
votes
1
answer
66
views
two columns in two rows [duplicate]
I have this table:
ID Value
-----------------
3 A1
4 A2
8 A20
10 A10
I have to create ...
- 3
0
votes
1
answer
617
views
Convert rows to column for every nth row by ID
I'm trying to convert rows to columns.
I have the following columns: user_id, meta_key, meta_value
I have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'
Right now I can ...
- 11
-2
votes
1
answer
852
views
Trying to combine multiple rows into one row with different values [closed]
I have a table that looks like the following:
item_code | whse_desc | Height | Width | Window
--------------------------------------------------
WD123 | KC | 1.25 | 4.25 | 1
WD123 | ...
- 3
0
votes
0
answers
374
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
1
answer
79
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 | ...
- 21
-1
votes
1
answer
35
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 (...
- 11
-1
votes
1
answer
81
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)
...
- 11
1
vote
1
answer
3k
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,
...
- 113
-1
votes
1
answer
260
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
1
answer
32
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 ...
- 13
10
votes
2
answers
2k
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/...
- 17.4k
1
vote
1
answer
38
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....
- 7,374
0
votes
1
answer
177
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 ...
- 103
-1
votes
1
answer
446
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 ...
- 3
0
votes
1
answer
2k
views
SQL Join to put rows into columns [duplicate]
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 GAME2 ...
- 9
0
votes
0
answers
27
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
0
votes
1
answer
1k
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
...
- 13
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 ...
- 61
0
votes
1
answer
134
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(...
- 101
2
votes
1
answer
544
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....
- 123
0
votes
2
answers
5k
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 ...
- 51
0
votes
1
answer
165
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
1
answer
6k
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 ...
0
votes
1
answer
289
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 ...
- 25
1
vote
1
answer
29
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 | ...
- 13
0
votes
1
answer
594
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 |...
- 103
0
votes
3
answers
108
views
Select with dynamic columns
I have an InvoiceHeader, InvoiceItem tables
InvoiceHeader content:
InvoiceId InvoiceNumber BusinessId BusinessName BranchId
1 1 10 BSN INC 1
...
- 103
1
vote
1
answer
627
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 ...
- 55
1
vote
1
answer
56
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 ...
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 (...
- 31
1
vote
1
answer
4k
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, ...
- 113
0
votes
2
answers
203
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.
+-----+-----+-------------------------...
- 3,203