Questions tagged [unpivot]
The unpivot tag has no usage guidance.
53
questions
0
votes
0
answers
66
views
In Snowflake, and in SQL Server, how do I unpivot 83 columns into 83 rows?
I need to compare the SUMs of 83 columns in my source table (in SQL Server) to the SUMs of 83 columns in my target table (in Snowflake) to see if the SUMs of the source and target table columns match. ...
- 65
0
votes
0
answers
60
views
PIVOT for "groups" - rows to columns
How-to convert rows (for group) into columns.
Here is the source table that have:
update: added Id and IdGestion columns
Id IdGestion PeticionId Field Value
48 17 16 Type ...
- 145
0
votes
1
answer
67
views
How to UNPIVOT an aliased query result using PHP and MySql
I would be grateful for your assistance in helping me get solution for my project weather query problem. I have arrived at the query below and is working:
");
$first_row = true;
while ($row = ...
- 13
0
votes
1
answer
150
views
Sync different table structure in MySQL Database
I have two table in different database host host. activity_table in Host A and presence_table in Host B. presence_table used by binary app where i don't have access to the source code but i have full ...
0
votes
2
answers
2k
views
complete transpose a table with dynamic rows and column
I have a mysql database with this structur:
Name
2019
2020
2021
2022
...
Name1
124
98
34.5
NULL
Name2
102
NULL
34
NULL
Name3
34
56
97
123
Name4
NULL
NULL
34.5
NULL
...
...
Every year comes new ...
1
vote
2
answers
53
views
Pivoting table in MySQL
I was wondering if it is possible to change the output of this:
User Database Select Insert Update Delete Create References Alter Drop
------------- ------------------...
- 13
2
votes
2
answers
198
views
MSQL - Switching rows to columns
I've been stuck on the following for quite a while, it seems like it should be relatively simple but I can't quite get my head round it.
- 23
0
votes
2
answers
70
views
Pivoting 4 by 44 result set
I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each ...
- 152
2
votes
1
answer
1k
views
SQL: pivot non numeric data [duplicate]
I have a set of data which basically looks like the following:
+---------+--------+
| EventId | Field |
+---------+--------+
| 1 | apple |
| 1 | paper |
| 1 | boy |
| 2 | ...
- 23
-2
votes
2
answers
585
views
Turning columns result set into independant rows in MySQL
I am pretty new to this and have been struggling over one issue.
I have a result set which for each Opportunity in a table (id as primary key), provides a date for a first cashflow (DFCFW) as a column ...
1
vote
1
answer
31
views
Transposing a row data in multiple rows based on multiple columns
I have data in the database in the below format
And I want output data in this format
BudgetCategoryId | CostCenterDepartmentId | CostCenter | AccountNo | Amount | Timestamp
714 | ...
- 111
1
vote
1
answer
81
views
out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into?
I sometimes have to unpivot tables, that means, converting all columns into rows.
Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second ...
- 15.8k
0
votes
1
answer
1k
views
Creating MySQL table with 100+ columns
I have a dataset to be loaded to MySQL that contains 100+ columns of assorted data items. I'd planned to pivot the dataset to be row-oriented in the vein of RDBMS best practice but absent a PIVOT ...
- 181
-1
votes
2
answers
788
views
Ignoring null values in columns. pivot and unpivot
I have below data format
I am looking for below data format
I try all options but unable to reach the below result.
Query :
create table dbo.FS_Daily_HSE_SAPS_Audit(
formId int default ...
- 101
2
votes
2
answers
1k
views
Build array from boolean columns
I have columns like role1, role2, role3, etc. They are all booleans.
I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the ...
- 290
0
votes
1
answer
2k
views
Unpivot in MYSQL?
Please I need help on how I can transform the data that appears in the attached photo. Maybe Unpivot SQL?
1
vote
1
answer
558
views
Using PIVOT to convert multiple rows and columns
So my current query output is as follows:
I would like to consolidate all of these rows to just one long row like so:
MyID Customer relc_rate relc_consumption relc_daysofservice ...
- 55
2
votes
1
answer
264
views
How to efficiently extract data from a csv file and save it in mysql
I have a csv file, maybe 1000 or more rows, and echo row’s data is related to two table in mysql. Example,
table `book`(id, title, …)
table `scene`(id, book_id, short_content, position…)
And the ...
- 21
3
votes
2
answers
14k
views
Unpivot multiple similar columns
I am trying to normalize a few tables. Here is one of my tables
SalesID|Order1Name|Order1Date|Order1Amt|Order2Name|Order2Date|Order2Amt|Order3Name......Order10xxxx
----------------------------------...
- 105
1
vote
2
answers
689
views
unpivot 2 columns into one column
I have tables like this:
ID|ServiceName|ServiceCode|Age |Gender|Number|TotalCost|Location
0 | service1 | 0000234 |under 12 | Male | 121 |1234 |loc1
I want to change the table like this:
...
- 37
0
votes
1
answer
255
views
Unpivot avoid cartesian product on empty columns
I have an issue with UNPIVOT.
The initial situation is that I receive some files with a fixed number of columns and I have to import them in my Database like that. That means my table has for example ...
- 11
0
votes
1
answer
731
views
SQL query to convert Column to Row using Pivot/Unpivot [duplicate]
My Select Table. I tried Pivot query to get my expected result but I was struct to generate the expected result.
WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
SELECT 'A',...
- 103
4
votes
1
answer
899
views
Convert columns into rows for use with indexed/materalized view
I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. ...
- 275
0
votes
1
answer
4k
views
Unpivot a table in PostgreSQL [duplicate]
I need to unpivot a table in PostgreSQL. The first dataset is what I have, the second is what I want:
Below is my schema with sample dataset:
http://www.sqlfiddle.com/#!17/3f45c/1
My understanding ...
- 11
11
votes
2
answers
41k
views
Please explain what does "for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)')" do in this code
In this code I am converting the subjects(columns) English , Hindi , Sanskrith , into rows
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsUnpivot
=...
- 165
-1
votes
1
answer
2k
views
how to insert multiple columns in a single column of another table in sql
I have the following table:
Then I want to get the following:
I want to achieve this in postgres. What should I do?
0
votes
1
answer
270
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
2
votes
4
answers
290
views
Converting a 50+ column table into 1 much smaller table
I'm currently dealing with a legacy PLC device which is spitting out data in an annoying MYSQL table. The table structure is:
PK | FK | FK | FK | Result1 | Result2 | ... | ResultN
As you can imagine,...
- 21
3
votes
1
answer
148
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
5
votes
2
answers
286
views
How to produce an output of fields that are NULL (combined from multiple columns+tables)? [duplicate]
I manage an application that has multiple users entering data through a web front-end into an MSSQL database. Each individual 'record' can have around 100 database columns across multiple tables (...
- 51
3
votes
2
answers
300
views
Return batches of rows based on one row
Say I have a table like this in SQL Server 2008:
id | name | qty
-------------------
1 | john | 1
2 | bill | 3
3 | mary | 2
4 | jill | 5
I would like to query this ...
- 33
1
vote
1
answer
889
views
Take 1 row of data from MySQL and Split the Results into a Table
I have a mysql database that looks like this:
proj_id | job1_name | job1_type | job1_time | job2_name | job2_type | job2_time | ...
--------+-----------+-----------+-----------+-----------+-----------...
- 13
2
votes
2
answers
304
views
SQL Server 2014 extract value from table with double join in row and column and subtotal
I have a table with daily exchange rates from ECB
FXDate EUR USD AUD BGN BRL CAD CHF ....
30/06/2017 00:00 1.00 1.1412 1.4851 1.9558 3.76 1.4785 ...
- 359
12
votes
1
answer
1k
views
How can I prevent UNPIVOT from being transformed into UNION ALL?
I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here's is a screenshot ...
- 31.8k
7
votes
1
answer
5k
views
Double Unpivot?
I'm needing to unpivot the below table so that the output looks as shown in below image.
Does this require me to perform an UNPIVOT twice on the dataset or can I accomplish my expected output by ...
- 3,235
1
vote
2
answers
354
views
TSQL help my feeble mind grasp Pivot/Unpivot on two columns
I've been trying to wrap my feeble mind around this one for some time, but I am having difficulty. I have looked at a few other examples like this SE.DBA answer, but I am having difficulty applying it ...
- 607
1
vote
1
answer
3k
views
Like UNPIVOT in SQL!
How to change the way that cols orders it self?
If we have a table like this
a b c
1 xx xy
2 xy yx
3 yy xx
Since both of b and c are same type I ...
- 123
2
votes
1
answer
994
views
Unpivot columns based on select
It is possible to do Unpivot operation, where column names are done via select, without using dynamic SQL ?
I tried this, but it fails.
SELECT *
FROM [dbo].[Report]
UNPIVOT
(
Value
FOR WeekName ...
- 1,059
2
votes
1
answer
3k
views
Dynamic SQL UNPIVOT and putting results into existing table
I know that there are a few questions out there on Dynamic SQL and Pivoting, but I'm unable to figure out the issue that I came across since I'm not familiar with Dynamic SQL/Pivoting.
So, I have the ...
- 21
2
votes
1
answer
513
views
Split a row into two for attendance set
I'm redesigning an attendance system used in my company. Originally, an attendance sheet was rendered on a grid that was getting week-by-week data pivoted. I've opted for a scheduler control by ...
- 145
4
votes
1
answer
3k
views
Finding changed column values
Table 1
ID |COL2|COL3|COL4|COL5|
ROW1| 1 | 1 | 1 | 1 |
ROW2| 1 | 3 | 2 | 6 |
ROW3| 1 | 1 | 1 | 1 |
ROW4| 1 | 1 | 1 | 1 |
ROW5| 1 | 1 | 1 | 1 |
ROW6| 1 | 1 | 1 | 1 |
Problem
...
- 43
12
votes
1
answer
742
views
How to make use of Batch Mode with an UNPIVOT (a loop join)?
I have a query of the following form:
SELECT ...
FROM ColumnstoreTable cs
CROSS APPLY (
SELECT *
FROM (VALUES
('A', cs.DataA)
, ('B', cs.DataB)
, ('C', cs.DataC)
) ...
- 1,289
3
votes
1
answer
2k
views
Unpivot with rows converted to columns as well as columns converted to rows
I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows. To try and ...
- 629
0
votes
1
answer
108
views
How to pivot this unpivot?
This is the pivot I made:
SELECT *
FROM (
SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao
FROM (
SELECT *
FROM (
SELECT 39 as codigo
, ...
- 7,344
5
votes
1
answer
5k
views
Including null values in unpivoted column
Consider the following table:
id title genre1 genre2 genre3 genre4 genre5
1 Movie1 Thriller Crime NULL NULL NULL
2 Movie2 Fantasy NULL NULL NULL NULL
3 Movie3 ...
- 65
1
vote
0
answers
63
views
Simple Unpivot - erroneous Column Conflict [duplicate]
Did I stumble across a reserved word here or something?
For, the following statement:
SELECT [Type]
FROM (SELECT 'Receive' AS [Rec], 'Transfer' AS [Trx]) [data]
UNPIVOT
(
[Orders] FOR [Type] IN ...
- 111
1
vote
2
answers
9k
views
Splitting a result row into multiple rows
I have the following query:
SELECT
vs.MonthName AS Month,
vs.Region,
vs.Category,
CAST (MIN (a.TotalAmount) AS money) AS ActualAmount,
MIN (a.TotalCases) AS ...
- 113
2
votes
1
answer
6k
views
How can one select a static value as a column based when using UNPIVOT?
I'm trying to unpivot a few columns in a table. This is for a reporting tool. The source data is already normalized. I am able to get my desired results with a series of UNION ALL statements. I can ...
- 1,677
4
votes
1
answer
744
views
Single year column and multiple weeks columns into single year/week column with PIVOT
I have a table that looks roughly like this.
(Pk int, Year int, Week1, Week2, .., Week53)
The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
- 195
1
vote
1
answer
169
views
Single row to multiple rows with column combination
I have a table like this:
╔═════╦═══════╦════╦════╦═════╦═════╦═════╦═════╦════╦════╗
║ ZIP ║ STATE ║ F ║ C ║ F1 ║ c1 ║ f2 ║ c2 ║ f3 ║ c3 ║
╠═════╬═══════╬════╬════╬═════╬═════╬═════╬═════╬════...
- 11