The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. Not all databases support the PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc) and aggregate functions.

learn more… | top users | synonyms

0
votes
0answers
8 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 ...
2
votes
1answer
310 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
627 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 ...
5
votes
2answers
80 views

Combining data from multiple rows

I am relatively new when working with queries so let me apologize in advance. This is a MS 2012 Server that I will be working with. This data has been brought into a staging table through a nightly ...
-2
votes
2answers
20k views

Display month in columns and the sum of sales below

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 ...
0
votes
1answer
24 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
48 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 ...
4
votes
3answers
247 views

MySQL single table static and dynamic pivot

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
5
votes
2answers
92 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
50 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         ...
1
vote
2answers
35 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 ...
1
vote
1answer
172 views

MySQL Pivot Table Dynamic Rows

I have a table like the one below: +-------+-----------+-----------+-----+--------+-------------+ | grade | firstname | lastname | age | gender | student_num | ...
2
votes
1answer
372 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

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: ...
1
vote
1answer
76 views

Concatenate columns uniquely on the same table in MS ACCESS

The original table: ======================================================================================= | A | B | C | D | E | F | G | ...
1
vote
1answer
42 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
2answers
236 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 ...
0
votes
1answer
59 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
1answer
37 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
51 views

How to do a SELECT inside of a PIVOT

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 ...
0
votes
0answers
27 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 ...
2
votes
3answers
40 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
37 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
73 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 ...
0
votes
0answers
93 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
vote
1answer
219 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
65 views

SSIS or msSQL 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 ...
11
votes
2answers
535 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
71 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 ...
1
vote
1answer
56 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 ...
2
votes
1answer
45 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
84 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
43 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 ( ...
0
votes
0answers
112 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 ...
0
votes
1answer
72 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 ...
0
votes
0answers
15 views

Creating a Views on an non-relational table, can't rotate

I have a table that is set up similar to this. id | key | value1 | value2 | value 3 01 | fname | "john" | null | null 01 | lname | "doe" | null | null 01 | bday | null | YYYY-MM | ...
1
vote
2answers
37 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
76 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 ...
0
votes
0answers
150 views

Export the pivot-table-with-dynamic-columns as CSV

Export the pivot-table-with-dynamic-columns as CSV. I Need to export the following Query As CSV. SELECT id, name, description,GROUP_CONCAT(if (DAYS='2015-01-01',AMOUNT,NULL)) As ...
2
votes
1answer
4k 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 ...
1
vote
1answer
671 views

Attempting to Pivot a column of grades and coming up with NULL values

I have Data in a table that i'm attempting to pivot from rows to columns, and the script im using is working, but the pivot is coming up with NULL for everything. Here's the source data: ...
4
votes
1answer
12k views

Display Monthly Attendance Report in MySql

I am doing a School Management System in php using Mysql DB. I am stuck in my Project. Please anybody suggest what I am doing wrong. I have two tables in my database; one is to store Students records ...
3
votes
2answers
16k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
1
vote
1answer
57 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 ...
4
votes
1answer
435 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...
-1
votes
1answer
308 views

Sum of two columns from pivot

I have the following query: DECLARE @columns NVARCHAR(MAX),@columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX),@columns2 NVARCHAR(MAX),@columns3 NVARCHAR(MAX); SET @columns = '1,2,3,4'; SELECT @columns1 = ...
1
vote
1answer
46 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
73 views

Dynamic Pivot with overlapping years

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 ...
0
votes
0answers
206 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
265 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 ...