Questions tagged [pivot]
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.
473
questions
2
votes
1
answer
3k
views
Join tables and pivot
tbl_orders
order_id, qty
123, 2
124, 1
125, 3
126, 1
127, 1
128, 1
129, 4
tbl_score
order_id, name, created
123, product 1, 2018-06-01
124, product 1, 2018-06-01
125, product 1, 2018-06-02
126, ...
- 123
1
vote
0
answers
1k
views
How to transposing rows into columns in MySQL?
I have three table (student's_info,subject and marks):
this is table student's_info:
----------------
|s_id | s_name |
----------------
| 1 | Ali |
| 2 | Jon |
---------------
This is ...
- 23
3
votes
2
answers
2k
views
Merge multiple rows into fewest number of rows of distinct values
In SQL Server does anyone know of a nice way to merge/flatten multiple rows of data into the fewest possible rows containing only the distinct non null values.
I.e.
A dataset like this:
As this:
...
- 117
0
votes
1
answer
133
views
Show Rows as Columns in SQL Server [duplicate]
I havent worked with Pivot. Not sure if pivot is the right thing to use. Is it possible to show this data:
-------------------
| Name | Code |
-------------------
|Test A | 1 |
|Test B | ...
- 435
3
votes
1
answer
3k
views
Pivot with rollup in sql server
Is it possible to use a cursor to create a dynamic table and then use those columns to aggregate data in SQL server 2008?
For example take the following table.
CREATE TABLE Billing (
BillingId ...
- 1,100
-1
votes
1
answer
122
views
Selecting roles from multiple rows into a single row
I have a table like this:
As you can see studentId: 43 has two different GIDs (these are roles, with a max of 3).
I have to select the record like:
- 101
0
votes
1
answer
2k
views
Add a New Column with ALIASES on 'date' by automation based on search results
I am trying to create a new column based on date search result.
I have written a code in the link SQL fiddle as I am looking for date automation on each column based on the date format. the date ...
- 139
2
votes
1
answer
2k
views
How to optimize a fetch into a crosstab
I have a schema like:
[ad_id] . [name] . [valueofname]
1 . name . "brian"
1 . age . "23"
2 . job . "IT"
2 . name . "Jack"
The row name contains ...
- 37
4
votes
2
answers
8k
views
Dynamic dates Pivot
Sample Data from StartKPA table:
I wrote the following query:
SELECT *
FROM
(
SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN '>80' WHEN (EndKPA*100) ...
- 43
1
vote
1
answer
2k
views
Transpose Rows to column
Can anyone Please help me to transpose below result
+------+------------+-------+
| SVR | dates | count |
+------+------------+-------+
| SRV1 | 03/25/2018 | 1 |
| SRV1 | 04/06/2018 | 59 ...
- 11
6
votes
1
answer
557
views
How to Pivot in T-SQL
I have a question I can't solve.
My query would be:
SELECT
f.Title, p.StartOfShowing
FROM
PlayDates p
INNER JOIN
PlayDateSections ps ON p.PlayDateId = ps.PlayDateId
INNER JOIN
...
- 163
0
votes
1
answer
430
views
Convert rows into columns using Pivot in SQL [duplicate]
I have this temp table named #DynamicDB, with below data.
Date Actual_Prod Forecast_Prod MRB_Parts
01-04-18 1 2 2
02-04-18 3 2 NULL
...
2
votes
1
answer
6k
views
PIVOT, Row_Number() returning multiple rows
I'm trying to pivot a list of emails onto a single row, but for some reason my primary email address is getting a row of its own, so Im ending up with two rows per contact.
CREATE TABLE #tmp (
ID ...
- 23
4
votes
2
answers
70
views
Add column for subsequent values for a record
I have a table structured as follows:
CREATE TABLE t (
HHID varchar2(1),
INDIV_ID int,
PRIM varchar2(1)
);
insert into t values ('A', 10, 'Y');
insert into t values ('A',...
- 53
0
votes
1
answer
1k
views
How to get 0 in the null value fields
select *
from
(
select type,SUM(ISNULL(amount,0)) AS amount,month(date) as date from tbl_cashmaster
where date between '2016/12/31' and '2017/12/31'
group by type,month(date)
) src
...
- 1
1
vote
1
answer
5k
views
Multiple PIVOTs SQL Server
I have a TSQL query that uses PIVOT to create columns called Analyst and Supervisor from the ApprovalLevelName column.
This is the current output:
In my query, I have ApprovedTime column, and Status ...
- 533
4
votes
1
answer
2k
views
Tips on reshaping data, pivot-like?
I have a table of levels and years that looks roughly like this:
INSERT INTO demo(first_name, rank, year) AS VALUES
('Peter',2,2017), ('Robert',3,2016), ('Evelyn',2,2017),
('Rand',3,2017), ('...
- 305
0
votes
2
answers
120
views
Simplify a SQL table result with less rows
I have a table where there are some names with 3 sports. If the person practices the sport it shows a '1' else '0'.
How can I change the table with SQL so that it only shows a line per person, like ...
- 159
1
vote
0
answers
32
views
Tier Dimension based on measure value
I have a case where i need to have a dimension based on measure value.
example :
the fact table is linked to date dimension through DateID,
and what i want to add is another dimension for Tier, but ...
- 11
0
votes
1
answer
272
views
Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL
I am looking at moving a large Excel spreadsheet to an SQL database - most likely MySQL.
The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to ...
- 1
0
votes
2
answers
2k
views
select distinct -> join 3 tables
Hi i need to mix (more than join) three tables, but the id could be present or not on each table (Please see the example below).
I have spend a few days trying join and union but all time some data ...
- 1
0
votes
1
answer
30
views
Need help in Pivot
I am using sql server 2014
I wanted to get Id (identity) field for maximum date as per order status id
Customer - Order Table:
Id (identity) | Customer ID | Order Status ID | Order Date
10000 |...
2
votes
1
answer
1k
views
Why isn't my count aggregate respecting my pivot columns?
In the pivot query below, why is every cell in a given row identical?
I was able to fix the query by replacing count(voo) with sum(voo), but I don't understand why count(voo) does not respect the ...
- 185
3
votes
2
answers
4k
views
Rotating a table with multiple value columns
I'm not sure if this can be done with crosstab(). I've been using this questions/answer as a reference, but am having difficulty putting together the query.
My table looks like this, just that I have ...
- 145
3
votes
1
answer
150
views
Name for table with metric_name and metric_value columns
A typical database table might look something like this:
date-------geography---clicks---cost---conversions
___________________________________________________
1/1/2010---Kansas------56-------12-----...
- 31
0
votes
1
answer
108
views
How to order fields returned by a PIVOT query [closed]
I Have 3 tables names like companystructure,projectallocation,introducermaster
I have tried this
select *
from
(
select projectallocation.proAllocationID as Sno,
introducermaster.intro_name ...
- 1
1
vote
1
answer
7k
views
How to have row values as column headers with the associated values in postgresql?
This is hard to explain but I'll do my best. So to make it simple, I have 3 tables with the following format:
Creator table
id | name
1 | jack
2 | doge
3 | cardano
roads
id | creator_id | ...
- 167
1
vote
0
answers
1k
views
MySQL dynamic number of columns
I'm looking for a way to dynamically build up the number of columns in a query.
Imagine a school assessment database.
I desire a way to get, for each question the percentage of students who got a ...
- 262
1
vote
1
answer
2k
views
postgres crosstab with unknown number of categories [duplicate]
I have below table
CREATE TABLE bar (project,month,hours)
AS VALUES
('A', 'aug', 1 ),
('A', 'sep', 3 ),
('B', 'aug', 2 ),
('B', 'sep', 5 );
I can crosstab this with month as ...
- 263
0
votes
3
answers
5k
views
Dynamic conversion of long data to wide with multiple columns
I'm trying to convert long data:
ID|SchoolID|Section|RepScore|SportsScore|PartyScore
1 |20 |1 |23.2 |70.2 |42.3
2 |20 |6 |65.2 |75.8 |52.3
...
- 476
10
votes
3
answers
115k
views
How to pivot on multiple columns in SQL Server?
What is the best way to 'flatten' tables into a single row?
For example, with the following table:
+-----+-------+-------------+------------------+
| Id | hProp | iDayOfMonth | dblTargetPercent |
+-...
- 2,300
3
votes
2
answers
9k
views
Subquery for column names of tablefunc crosstab queries
Is it possible to get the values for the names of the columns for the crosstab query from a subquery?
CREATE TABLE mytable(employee_name text, month date, allocation double precision);
INSERT INTO ...
- 51
2
votes
1
answer
1k
views
How to crosstab with a composite "row name"
I have following table with id(intger), project (varchar), month (varchar), hours (integer)
CREATE TABLE foo (id,project,month,hours)
AS VALUES
( 1 , 'A', 'aug', 1 ),
( 1 , 'A', 'sep', 3 )...
- 263
-1
votes
1
answer
5k
views
Turn Rows Into Dynamic Number Of Columns
I have a table in SQL Server that is horrid design, but am in need of a way to display ONE row per username with a breakdown of each item and qantity of that item the person sold. This is sample DDL
...
0
votes
1
answer
4k
views
Merging two rows into one by creating two new columns based on criteria from same table (SQL) [closed]
the data that I have looks like this
name year quarter type score
a 2016 Q1 x 40
a 2016 Q1 y 50
I want ...
- 11
1
vote
1
answer
179
views
Rotate table for year-over-year comparison
I have a road_insp table:
create table road_insp
(
insp_id int,
road_id int,
insp_year int,
condition number(10,2)
);
insert into road_insp (insp_id, road_id, insp_year, condition) ...
- 1,447
6
votes
1
answer
964
views
SUM CASE WHEN CLAUSE
I have the below Query and I want the Ageing profile to show as a new column
my ageing profile is 0-30, 31-60, 61-90, 91-120, 121-180, 181-365, 365+
I basically want the results to appear lie this ...
- 61
2
votes
1
answer
761
views
Is it possible to rename the columns of a resultset?
I have two tables joined on an ID.
they might look like this:
table_overview
| id | version | description |
------------------------------
| 1 | 1.2.3 | new stuff.. |
table_files
| id | type ...
- 181
2
votes
1
answer
8k
views
Postgres crosstab query on multiple columns
I am trying to build a crosstab query on multiple columns.
To accomplish that I first created a type:
create type product_status as (product varchar(255), status varchar(255))
Then I created my ...
- 235
1
vote
1
answer
3k
views
How do I add extra columns to crosstab()?
I am using PostgreSQL 9.6 and have following schema
CREATE TABLE activitylog (
sessionid text NOT NULL,
studentid integer NOT NULL,
activity text NOT NULL,
created timestamp without ...
- 113
1
vote
1
answer
321
views
Pivot add-on in cross-tab table Postgresql
Given the schema here I wonder how I can use Pivot add-on to calculate a cross-tab table in the below format please?
Africa America Asia Australia/Oceania Europe Total
#cities #cities ...
- 143
4
votes
2
answers
4k
views
Query monthly having group_concat and group by need help
I’m stuck on a join tables query presenting data monthly involving GROUP BY and GROUP_CONCAT.
Here’s a simple client table (DDL and DML at the bottom of this post):
id | Name
1 | Sony
2 | Toshiba
3 ...
- 43
1
vote
2
answers
1k
views
Querying data from a pivoted MySQL table takes a long time to execute
TL;DR
Querying data from a two table database I've setup a long time ago takes a really long time to execute.
Back when I was a student, quite some time ago, I developed a simple survey tool for a ...
- 11
1
vote
3
answers
1k
views
Coding Problem - Do I Use Pivot, XML, or Something Else
I'm having a brain-not-working moment and could really use some help. This is SQL Server 2014 Standard ed.
I have 3 Tables - for simplicity, we'll call them "name", "address", and "international". ...
- 173
0
votes
1
answer
35
views
Create Pivot Based Off Variables [duplicate]
I have DDL like the below, which returns data like such:
GP17 GP16
0 103
90 0
375 0
I am wanting to pivot the data somehow so that it returns only one row, like such:
Jan 17 ---- ...
0
votes
2
answers
15k
views
Join tables with same keys, second table has multiple values for key
I have two tables with shared keys and I'm trying to join them and output the results to one table. The problem I'm having is the second table has multiple values for the shared key.
hosts
...
1
vote
1
answer
1k
views
Referring to function parameters inside a Postgres function
I have a function like this in Postgres 9.5:
CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
BEGIN
RETURN ...
- 1,163
2
votes
1
answer
34k
views
postgres row to column
I have this pretty straightforward SQL query
SELECT count(date_mdy) AS unique_date_count,
county,
date_s
FROM (
SELECT date_mdy,
county,
date_s
FROM air_quality2
GROUP BY county, ...
- 147
1
vote
1
answer
369
views
Calculate and show DATETIME difference on PIVOTING query
I have the following PIVOTING query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
SELECT *
FROM (select jsc.applicationid
,[the date of ...
- 15.8k
1
vote
1
answer
539
views
Generating Pivot In SQL Server 2008
I am attempting to create a Pivot Table in SQL Server 2008, but I am getting an error of
Incorrect syntax near the keyword 'FOR'
Below is sample DDL and the query I tried to execute - how should ...
- 261