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)

3
votes
1answer
97 views
+100

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
27 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 ...
1
vote
1answer
35 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 ...
1
vote
2answers
104 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 ...
2
votes
1answer
395 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
2
votes
1answer
26 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 ...
4
votes
1answer
402 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
0
votes
0answers
31 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
44 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
1answer
231 views

How to convert rows into columns and query on them in Mysql?

I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the ...
0
votes
2answers
46 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
54 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
2answers
117 views

Rows into Column

I want to make the above view vConsolPrchOrd like below: Database: PostgreSQL 9.2 I want to create a Crystal Report with this view (I asked about this on Stack Overflow).
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 ...
5
votes
3answers
320 views

MySQL single table static and dynamic pivot

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
1
vote
0answers
12 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
39 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 ...
4
votes
1answer
18k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
3
votes
3answers
180 views

Dynamically convert hstore keys into columns for an unknown set of keys

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns. Users can ...
1
vote
0answers
26 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
24 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
39 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
224 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
56 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
54 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
59 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
0answers
81 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
54 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 ...
2
votes
1answer
55 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 ...
3
votes
1answer
389 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 ...
-1
votes
1answer
37 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 ...
1
vote
3answers
145 views

SSIS or SQL Server way of sectioning a flat file

I have a few hundred thousand flat text files which contain a single column of values. The first 40 values are parameters followed by between 4000 - 8000 rows of sensor data. The files are imported ...
-2
votes
2answers
24k views

Dynamic pivot: sum of sales per month

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to ...
11
votes
2answers
583 views

Help with PIVOT query

I have a table with below structure : CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION]( [SCHEMA_VER_MAJOR] [int] NOT NULL, [SCHEMA_VER_MINOR] [int] NOT NULL, [SCHEMA_VER_SUB] [int] NOT NULL, ...
2
votes
1answer
6k views

How to create a table in MySQL with automatic numeration of columns?

Example. My data is from Table name:BIKE Bike_No Repair_Date Repair_Cost -------- ----------- ------------ ABC1234 2013-01-05 50.00 BMX5678 2013-02-04 75.00 ABC1234 ...
0
votes
2answers
28k views

Convert Rows to columns using 'Pivot' in SQL Server when columns are string data type [duplicate]

I need to know whether 'pivot' in SQL Server can be used for converting rows to columns if there is no aggregate function to be used. I saw lot of examples with aggregate function only. My columns are ...
10
votes
3answers
29k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
8
votes
2answers
31k views

Return a column per date in a range

Let's say I have Table A: BookingsPerPerson Person_Id ArrivalDate DepartureDate 123456 2012-01-01 2012-01-04 213415 2012-01-02 2012-01-07 What I need to achieve with a ...
0
votes
0answers
37 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 ...
0
votes
1answer
174 views

How does one create a Matrix Report to convert sql columns as rows? [duplicate]

I'm trying to create a product listing. Naturally Sql prints the data vertically forming rows after rows like this: Rowa. product_img#1|Product_name|Product_price|Product_short_description Rowb. ...
0
votes
1answer
4k views

pivot a table with cell name as column name

I have a table structure is : year | code | name| value | wef | 2014 | a001 | abe | 2000 | 2014-04-01 | 2014 | a001 | def | 3000 | 2014-05-01 | 2014 | a002 | abe | 2000 | 2014-06-01 | ...
2
votes
1answer
234 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
2answers
61 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 ...
0
votes
1answer
54 views

SSRS Matrix Report - Count for NULL data

I have a SSRS Matrix Report for data quality in our warehouse. We use this report to display invalid values in specific fields during specific time period. For example, Missing/Invalid Postcode, My ...
4
votes
1answer
332 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 ...
6
votes
2answers
110 views

Combining data from multiple rows

I am relatively new when working with queries so let me apologize in advance. This is SQL Server 2012 that I will be working with. This data has been brought into a staging table through a nightly ...
2
votes
1answer
132 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
84 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
0answers
93 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 ...
1
vote
1answer
946 views

Cross tab query with concat field

I want to create a cross tab query for my table. When I use CONCAT I am unable to get output successfully. Can anyone help me with my query? SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT ...