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
3answers
36 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 ...
1
vote
2answers
34 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
281 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
74 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
33 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` ...
1
vote
1answer
353 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
1answer
97 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
42 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
1answer
52 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 ...
0
votes
1answer
63 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
1answer
530 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 ...
0
votes
0answers
23 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 ...
0
votes
0answers
30 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 ...
0
votes
0answers
91 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
0answers
14 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 | ...
0
votes
0answers
121 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 ...
0
votes
0answers
182 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
52 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
147 views

MySQL - Pivot-style (row -> column) results in subquery

SELECT j.departure, stopDepartures.* FROM journey j JOIN journey_day ON journey_day.journey = j.id JOIN service ON service.id = j.service JOIN pattern ON j.pattern = pattern.id JOIN ( SELECT ...
0
votes
0answers
37 views

Is it possible to get this output in Oracle 8i?

I am using Oracle 8i. I have a table Ord that stores data on such, with two columns that I'm interested in OrdNum and OrdDate. Here is an example SELECT OrdNum, OrdDat FROM Ord; OrdNum OrdDate ...
0
votes
0answers
221 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
114 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
1k 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, ...