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)

1
vote
2answers
47 views

Calculating the number of times a motive was cited over a given period

I'm using PostgreSQL 9.4. I have the following surveys table. Each row includes a motive that was cited by the respondent, represented by the motive_value column (think of it as motive_id). ----------...
2
votes
1answer
458 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. ...
1
vote
1answer
32 views

Consolidate multiple rows into single row

In PostgreSQL 9.5, I have a table named reports: CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, ...
1
vote
1answer
230 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 ...
1
vote
1answer
81 views

How to improve query to include 2 critical pieces of employee data

I’m going to preface by saying that I’m fairly green with SQL. My code below works, however I am missing two vital pieces of my employee population: (1) those employees who have been invoiced but ...
1
vote
1answer
39 views

MYSQL Database PIVOT and wants in specific format

I have below table and its data in mysql CREATE TABLE `pointhistory` ( `id` bigint(20) NOT NULL auto_increment, `channelid` varchar(64) NOT NULL, `deviceid` varchar(64) NOT NULL, `pointid` ...
0
votes
1answer
65 views

Database MySQL - Transpoing Table from one form to other

SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(function_NAME = ', function_NAME, ',budget, NULL)) AS ', function_NAME)) INTO @sql FROM country_report dr ...
-3
votes
1answer
37 views

How link data - delimiter

How can I make script take mydata from mytable instead of providing values like in an example below? SO instead of set values to Code and Location link these to mycolumns in mytables? with data as ( ...
4
votes
0answers
171 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
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 ...
1
vote
0answers
32 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
0answers
116 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 ...
1
vote
0answers
419 views

how to get a sum total of each column and row - postgresql [Crosstab or Case]

I have to count the records according to the criterion, they are much more filters but I have summarized. I could use either of two ways. 1# SELECT * FROM crosstab('SELECT dim_espacio.desc_estab, ...
1
vote
0answers
208 views

Efficient way to transpose large categorical column to one-hot table row

I have a varchar column containing ~ 8000 unique values and 500k rows. I need the column transposed so that the column headers are the unique values and the values in the rows are 1 where the row has ...
1
vote
0answers
300 views

SQL: Dynamic Pivot Column, In the column's row value How to get Top 1 value

In my following query I need to get the top 1st record's value in pivot row value. So basically I get all parts and if there is a service entry with that part and a revision number I used that ...
0
votes
0answers
32 views

Unable to fetch data based on latest date and time

I have a table which stores application details. Applications are nothing but a set of data showing scores. Each application has 6 parts namely 1,2,3,4,5, and 6. So, in the database table parts can ...
0
votes
0answers
20 views

Issue in pivot operator

Find the employee is in which shift and shifttype If he is shift 1 shifttype 1then the record comes from employee table if he is shift 2 shifttype 4 then the record comes from shiftscheduler table ...
0
votes
0answers
19 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 ...
0
votes
0answers
38 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 ...
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 | ...
0
votes
0answers
30 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 ...
0
votes
0answers
48 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
0answers
73 views

Combine multiple same rows into one row by using Id

This is my table: ID Contact Id Ad-type Ad-Title Images_Path 1 2 Houses For Sale lands/im1.jpg 2 2 Houses For Sale lands/im2.jpg I need output like this: ...
0
votes
0answers
95 views

Need to show Row Data in The form of Column using MYSQL?

need to show the renewl_year data in the form of column like MEM_Code renewl year 2003 renewl year 2004 renewl year 2005 Paid_amount sum using query:
0
votes
0answers
270 views

SQL Server 2008 single-line PIVOT/UNPIVOT combo

I have a request to get information from a table with a stored procedure that shows all the necessary data for in one line. This table includes a hierarchyID, so I am trying to use the HID methods in ...
0
votes
0answers
354 views

Partly rotated table with pivot/dynamic SQL without aggregation

Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have ...
0
votes
0answers
126 views

Seeding Pivot table with percentage

Given two tables - users and games - and a pivot table - userGames, I'm trying to efficiently seed the userGames table For testing, so that each user has close to a set percentage of games. The query ...
0
votes
0answers
2k views

Dynamically Converted rows into columns (Pivoting)

Query SELECT Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total, COUNT(studfeedback.studfeedback_Rating) AS totastudents, **...