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
95 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, seq4,...
2
votes
1answer
137 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
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 ...
0
votes
1answer
86 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
75 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
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 end)...
3
votes
1answer
803 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
81 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
341 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
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 ...
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
173 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
88 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
114 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
39 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
187 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
64 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
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: ...
2
votes
1answer
538 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
109 views

Concatenate columns uniquely on the same table in MS ACCESS

The original table: ======================================================================================= | A | B | C | D | E | F | G | ====...
1
vote
1answer
110 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
865 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 [EVAULTTEST].[dbo]....
2
votes
3answers
134 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 ...
1
vote
1answer
229 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 ...
-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
212 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
998 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
965 views

MySQL Pivot Table Dynamic Rows

I have a table like the one below: +-------+-----------+-----------+-----+--------+-------------+ | grade | firstname | lastname | age | gender | student_num | +-------+-----------+-----------+-----+...
0
votes
1answer
730 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
602 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
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 ...
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, ...
2
votes
1answer
87 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
123 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
242 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
103 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
209 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
118 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
384 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
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). ----------...
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
223 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
76 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
80 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
136 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 ...