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.

learn more… | top users | synonyms (2)

0
votes
2answers
31 views

How to separate values from one column into two columns in SQL Server

I have this dataset as input: Name Contact No ------- ---------- Deepika 75865558 Deepika 207586558 Rnveer 746655558 Rnveer 946655558 vaibhav 2356841 vaibhav 6655558 I want to get ...
1
vote
2answers
29 views

How to Join two table using pivot with combining two field in one table

I have two table in this format Job_Skill ID_Skill Min_Job Idea_Job Max_Job ===================================== 1 0 0 1 2 ...
0
votes
1answer
19 views

How to use pivot when there are three fields and u want to put them in one category

I have data in this format Skill Min Ideal Max ========================== C++ 0 1 1 I want to display it like this Criteria C++ ================= Min ...
0
votes
0answers
29 views

Convert table values into column header without PIVOT [number of column values is dynamic] - Oracle 10g [duplicate]

I have a table in the following format ID NUM P_VAL Q_VAL ---------------------------------------- 445120000 00 101 25 445120000 10 103 25 445120002 00 ...
2
votes
1answer
30 views

How can I improve my query, using joins & improving performance?

I have a really smelly query which I'm not sure how to simplify, this is quite an intensive query. I have tried various union and inner joins in an attempt to simplify it and my biggest problem is ...
3
votes
3answers
47 views

How to Pivot in PostgreSQL

Having the following data in a table: ID Category Value 1234 Cat01 V001 1234 Cat02 V002 1234 Cat03 V003 1234 Cat03 V004 1234 Cat03 V005 I want to have the ...
1
vote
2answers
103 views

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 ...
0
votes
1answer
31 views

SQL Server Pivot Multiple Column and Value [duplicate]

I have a sample table attendance look like this : PERIOD EMPL_NMBR EMPL_NAME DRPT DAYX [IN ] [OUT] 201603 00001 John QA 01 08:00 17:00 ...
6
votes
4answers
320 views

Convert row into concatenated string with column names included

I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in ...
2
votes
1answer
63 views

Problem using pivot to display data

I currently have a table called sample, the structure of my attendance table is: create table sample ( Student_ID VARCHAR2 (10), Late_Attendance number (10) Default 3, Attendance_Date Date, ...
0
votes
0answers
18 views

MySQL IF Statement works for PIVOT scenario… Until I GROUP BY

I've been trying for days to figure out how to pivot data to give me this appearance: I've written the following code to generate the data requested and it works. But When I use GROUP BY to ...
3
votes
3answers
188 views

Two rows into two columns

I have this table: Name Value Sequence ------------------------------- test A1 1 test A2 3 test2 A20 5 ...
0
votes
0answers
10 views

How to create Pivot table with dynamic columns in MySQL [duplicate]

I'm using the following table for storing dynamic form data data: Using the following query to select the records from my table SELECT response_no, ...
0
votes
0answers
34 views

Table based function with variable columns

I have a situation where I need to create a table based function but don't know the number of columns that need to be returned at the point of defining it. Essentially, I'm using a pivot table to ...
1
vote
0answers
32 views

Pivot table with date_shutdown and factory_name and type_shutdown and time_shutdown

I need to make a pivot table that includes a factory name column along with a column for every type of shutdown; in the intersection the period of the shutdown. Factory names and shutdown types are ...
0
votes
1answer
22 views

Pivoting Dynamic Rows into Columns in MySQL [duplicate]

I have two tables Student_Attendance and Students. I am using this query: SELECT sa.Student_Id, sa.Attendance_Date, CASE WHEN sa.Attendance_Status as Status FROM student_attendance sa ...
1
vote
0answers
30 views

how to edit kvp data as crosstab/pivot

I am keeping data in key/value pairs. I know how to convert that data into crosstab form using queries like this: <!-- language: lang-sql --> SELECT master.tag, MAX(CASE WHEN data.key = ...
1
vote
1answer
56 views

column alias in dynamic pivot [duplicate]

I need help with a dynamic pivot query. I would like to modify the column name (alias) but I can't get it done in the pivot. Please help. The query itself works perfectly but the username contains the ...
4
votes
1answer
66 views

How to get multiple row data into a row with multiple columns

I have a MySQL table like this: User_Id course_name course_location course_id 1 course name 1 location 1 1 1 course name 2 location 2 2 1 course name 3 ...
1
vote
1answer
47 views

Nested, inline pivot table (oracle)

I'm trying to go from a table like this: ID | NAME | METADATA ------------------------------- 1 | test | '{"a":"x","b":"y"}' where the metadata column is a JSON string. What I'd like to end up ...
3
votes
1answer
119 views

RDS crosstab: ERROR: permission denied for language c

We're currently using PostgreSQL 9.2 in our production database, we want to migrate to a new RDS instance, using PostgreSQL 9.4. However, we need to have permission to create a function, but only ...
6
votes
1answer
203 views

Insert data from multiple rows into multiple columns

I cannot find, after much searching, how this would be done. My web searches get Pivots and Concats and Cases and Subqueries etc, none of it is quite solving the issue for me. The ...
2
votes
1answer
37 views

How can I have flexible PIVOT column names?

Following the example in https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/, I have a PIVOT that works but is not very flexible. It is basically SELECT * FROM ...
0
votes
0answers
32 views

How can I display values as columns dynamically using postgresql

I have a data table containing finance data about some filings like: filing_id | custom_label | value ------------------------------------ 987345 | cash | 567 987345 | ...
1
vote
2answers
115 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
1
vote
2answers
49 views

How to turn this query into a Pivot?

If theres something I can't learn, is PIVOT. People are trying to teach me this, I'm reading blogs about this but I just can't understand the logic. How can I turn this query , into a pivot? The ...
0
votes
2answers
54 views

How to transpose rows to column in datatabse [duplicate]

I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below Col_ID Col_Group Col_Price 1 AA 0 1 BB 2000 1 ...
0
votes
3answers
62 views

Using start and end event logs to create a table/view containing spans between the times of each log

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
0
votes
0answers
27 views

How to split one column data into multiple columns [duplicate]

I have a table called Items. First column(Items) have n number of Split Items. select * from items ITEMS SPLIT ITEMS 1111 2525 1111 4587 1111 9874 1111 8787 ... Instead of giving ...
1
vote
0answers
13 views

MYSQL to PIVOT tables [duplicate]

SELECT person.firstnames, person.surname, crm_question.label, crm_answer.value FROM crm_interaction JOIN person ON person.id = crm_interaction.person_id JOIN ...
3
votes
1answer
48 views

Join table into different columns

I have two tables (simplified). table1: - id (serial) table2: - table1_id (integer) - value (text) - type (integer) I want to join these two tables, but depending on the type, I want ...
0
votes
0answers
27 views

Grouping and Transposing Rows to Columns in PostgreSQL

I have the follwing situation: I have profiles and tasks. One profile can be used once for each task. I track usage in profiles_used table. I need to build a report that shows how many profiles of ...
3
votes
1answer
28 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 ...
0
votes
1answer
41 views

How to pivot this unpivot?

This is the pivot I made: SELECT * FROM ( SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao FROM ( SELECT * FROM ( SELECT 39 as codigo , ...
2
votes
1answer
226 views

Concatenate with SELECT *

I want to concatenate columns with a SELECT *. For example: SELECT * FROM Table1 Result: Col1 Col2 ---- ---- Joe Smith I want to do something like SELECT CONCAT(*) FROM Table1 The reason I ...
0
votes
1answer
66 views

SQL Server Multiple Pivots

I would like some help please with the following. Here is my SQL Server 2014 table schema: Date [DateTime] Invoice1 [float] Invoice2 [float] Operator [nvarchar] Client [nvarchar] In my Operator ...
1
vote
2answers
77 views

Select column to be inserted based on input column value

Assume the following two tables (and ignore the quality of the database design): Table 1 has columns id, sequence_number, value: it contains many rows. Table 2 has columns id, seq1, seq2, seq3, ...
2
votes
1answer
113 views

How to pivot without fixed columns in TSQL?

I'm struggling with pivoting a quite simple table. All examples and tutorials on the web, are not what I'm looking for, so maybe you guys can help me out here (I must say that my T-SQL knowledge isn't ...
1
vote
1answer
153 views

MySQL query to create a Matrix view using pivot

I have a table with the following fields: p1 p2 s1 s2 where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The number of players can vary from 3 - 10. The table have the results between ...
0
votes
1answer
71 views

SQL Server self recursive horizontal join

I need to generate a query which provides hierarchical data in a horizontal way. The levels of depth are variable, it is ok if there are many empty fields. The only rows that should exist should be ...
3
votes
1answer
66 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 ...
-1
votes
1answer
43 views

Pivot to three named columns

Students may have more than one race.I want to add races to report with new headings: Race1, Race2 and Race3 only. select s.lastfirst, s.grade_level, max(case when sr.racecd = 100 then 100 else 0 ...
3
votes
1answer
590 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 ...
0
votes
0answers
46 views

How to pivot a CSV file into a table

Input: a CSV file which cannot be changed prior to import Desired Output: A table where CSV_File.Name is a column and CSV_File.TagValue is the data in that column with a Key field Timestamp column ...
2
votes
2answers
72 views

Dynamic Pivot SQL table [duplicate]

Table dbsize loading with data of every database size every night. dbsize (id, database_name, database_size, insert_date) It looks like this: - 1 dbname1 1000 1/1/1900 - 2 dbname2 ...
2
votes
1answer
809 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 ...
2
votes
1answer
250 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 ...
1
vote
0answers
104 views

MS Access 2010 report - SQL pivot query WHERE criteria not working

I have created a report that has a list of actions which are populated from an "Actions" table. (See Below) However, each action will have multiple updates populated from an "Updates" tables and I ...
4
votes
1answer
762 views

Creating crosstab() pivot table in PostgreSQL 9.0

I have a rather complicated issue for myself which I hope someone can help me with. I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table. To keep it simple I will ...
4
votes
0answers
116 views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ...