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
votes
1answer
45 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
727 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
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 ...
2
votes
2answers
77 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
1k 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
295 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
111 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
1k 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
145 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 ...
0
votes
1answer
82 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 ...
6
votes
2answers
112 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 ...
0
votes
1answer
37 views

Pivot Results By Day [duplicate]

I have a table in SQL Server 2008, with mails received from a pop account. The table has the following columns: +------+----+---------+------+------+ | from | to | subject | body | date | ...
2
votes
2answers
54 views

Optimizing query using a WHILE statement

I'm trying to convert a view that extracts a yearly amount into something more WHILEish The query is more or less: SELECT * FROM (SELECT SUM(EmployeePaid)*2 AS Total2013 FROM TblRecords WHERE FYear ...
1
vote
2answers
177 views

Turning a multiple row resultset into columns with multiple rows [duplicate]

I currently have a resultset that looks like this: Title Value Start Time              1998-01-04 01:38:00.000 End Time                1998-01-04 01:43:00.000 Product Name         ...
5
votes
2answers
96 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
1
vote
2answers
62 views

Group by month in result columns?

I'm using this DBMS: Caché I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need. Given the following sample data: ID Name Result Date ...
0
votes
0answers
72 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: ...
2
votes
1answer
508 views

Laravel MySQL relation difficulties

I have a "users" table: And an organisations table ("orgs"): And this is the pivot table: I have some relations: On the "users" table, I have a function called "orgs()" which is a ...
1
vote
1answer
105 views

Concatenate columns uniquely on the same table in MS ACCESS

The original table: ======================================================================================= | A | B | C | D | E | F | G | ...
1
vote
1answer
104 views

Dynamic SQL in a pivot table

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant ...
1
vote
1answer
755 views

How to do a SELECT inside of a PIVOT [duplicate]

I am trying to SELECT values from a column in my database in order to perform a PIVOT on those values without hard-coding them. SELECT * FROM (SELECT ItemType, [Status] FROM ...
2
votes
3answers
117 views

Calculating overlap between groups

I have a table with two columns of interest, item_id and bucket_id. There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to. Each item_id can appear multiple ...
0
votes
0answers
61 views

Pivot in SQL Server 2000

In my table record contains 'year' as a column that should pivot as table header in SQL Server 2000. Finally I tried these but I couldn't get it to update the table set @columns = @yearspan SET ...
1
vote
1answer
216 views

SQL Server pivot filter underlying tables by aliases specified in view

I have table structure as follows: CREATE TABLE [dbo].[Test] ( [ID] [int] IDENTITY(1, 1) NOT NULL , [ProductID] [int] NOT NULL , [CountryID] [int] NOT NULL , CONSTRAINT [PK_Test] PRIMARY ...
0
votes
0answers
327 views

Pipelined function returning anydataset

I am using the dynamic pivot see here to pivot a data set where it returns an object of type anydataset. It works great in SQL but I am having a hard time using it in a package. In the package the ...
-1
votes
1answer
98 views

Using different aggregate functions in a pivot

I have a table of data collected in the time as shown below. I would pivot them using different aggregate functions: average for C1 and sum for the others. How could I achieve it ? DECLARE @T AS ...
2
votes
1answer
1k views

Indexed VIEW in SQL Server with PIVOT

A simplified version of my problem is as follows: I have three tables Records, Inputs, and Outputs. Records Id DTS   1  2015-01-01 00:00 ... Inputs Id RecordId ...
0
votes
3answers
203 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 ...
1
vote
2answers
852 views

Select Multiple Values from Same Column; one sql statment and split into separate columns

This post derives from following - Select Multiple Values from Same Column; one sql statment Based on this query - SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) FROM t WHERE meta_key ...
1
vote
1answer
863 views

MySQL Pivot Table Dynamic Rows

I have a table like the one below: +-------+-----------+-----------+-----+--------+-------------+ | grade | firstname | lastname | age | gender | student_num | ...
0
votes
1answer
622 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 ...
11
votes
2answers
598 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, ...
0
votes
1answer
64 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 ...
1
vote
0answers
399 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, ...
2
votes
1answer
85 views

How to perform a pivot on multiple columns

I have a list of data that includes values and the time those values were recorded. I would like to pivot this data so that i have as columns the individual types of observations and the date/time ...
3
votes
1answer
112 views

Display multiple IP address values for one system in a single row

I cannot for the life of me get the systems with multiple IPs to display under one row. Below is the current SQL statement I have. systems with more than one IP show up as duplicated on the report. ...
0
votes
1answer
230 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
2answers
99 views

Transform 14 columns into 2 (SQL Server 2008) - Pivot/Cursor not helping

After hours googling pivot and cursors. I couldn't think or figure out how to do this. I have this query: declare @banco varchar(100) set @banco = 'CascVW' declare @tbltemp1 table ( ...
1
vote
0answers
191 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
1answer
112 views

SQL query to return a columns with given range

I have a table tblItems with records having the same UniqueID and Description but with different dates and amount. Now I want to show the records by grouping them together using the UniqueIDs and ...
3
votes
3answers
342 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
2answers
46 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). ...
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
3answers
213 views

Add fields to view from select (transpose fields)

I've got a table with following structure: CREATE TABLE test ( id integer, field character varying, value character varying ); field value ------ ----- field1 value1 field2 ...
1
vote
1answer
75 views

MySql pivot table for scatter chart [closed]

I would like to transform the following sql (e.g. select Name,X,Y from mytable) result: Name | X | Y bla1 | 0.1 | 0.2 bla2 | 0.3 | 0.4 Into the following form that is displayed correctly in an ...
1
vote
1answer
79 views

Pivoting with NULL Dates

I'm attempting the pivot below: SELECT EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] ...
0
votes
1answer
129 views

Dynamic Pivot with overlapping years [duplicate]

I have looked and cannot find how to show the last # months in a pivot table. Everything I seem to run across codes months statically. I'm seeking a solution that shows something similar to the ...
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
1answer
469 views

Using pivot on datetime

I have the following table: Date,Type 2012-01-25 13:48:00.000,1 2012-01-25 13:49:00.000,2 2012-01-26 13:48:00.000,1 2012-01-26 13:49:00.000,2 I tried the pivot function to get this layout: 1,2 ...
0
votes
1answer
299 views

create foreign keys for pivot tables

I'm trying to optimise and speed up a mysql 5.6 datatabase. I have two tables with a many to many relationship: Reservation and Resource. Each Reservation can have many Resources and each Resource ...