Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [unpivot]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-2 votes
2 answers
71 views

Insert data from one table to another

I have a table in which new entries come in every second. The table is just like this: value1 data1 Value2 data2 1 Er 2 Er 3 Er 4 Er Now insert these value in other table as Value data 1 Er 2 ...
charu gupta's user avatar
0 votes
0 answers
220 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. ...
Jimbo's user avatar
  • 65
0 votes
0 answers
169 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 ...
Kiquenet's user avatar
  • 145
0 votes
1 answer
77 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 = ...
BenGik's user avatar
  • 13
0 votes
1 answer
173 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 ...
Abu Dawud's user avatar
0 votes
2 answers
3k 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 ...
maphy-psd's user avatar
1 vote
2 answers
55 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 ------------- ------------------...
the_ruffler's user avatar
2 votes
2 answers
223 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.
Wizard324's user avatar
0 votes
2 answers
71 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 ...
Poseidon's user avatar
  • 152
2 votes
1 answer
2k 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 | ...
Suresh Kinta's user avatar
-2 votes
2 answers
2k 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 ...
FredK Trashmail's user avatar
1 vote
1 answer
35 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 | ...
Mayur Agarwal's user avatar
1 vote
1 answer
87 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 ...
Marcello Miorelli's user avatar
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 ...
Chris's user avatar
  • 191
-1 votes
2 answers
1k 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 ...
Saroop Trivedi's user avatar
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 ...
lurf jurv's user avatar
  • 320
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?
Christian Fleidl's user avatar
1 vote
1 answer
714 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 ...
MindXpert's user avatar
2 votes
1 answer
290 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 ...
hstk's user avatar
  • 21
3 votes
2 answers
16k 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 ----------------------------------...
abbhey's user avatar
  • 105
1 vote
2 answers
763 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: ...
alish's user avatar
  • 37
0 votes
1 answer
272 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 ...
MTH's user avatar
  • 11
0 votes
1 answer
814 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',...
Dhana's user avatar
  • 103
4 votes
1 answer
1k 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. ...
user3621633's user avatar
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 ...
sqldbforum's user avatar
12 votes
2 answers
45k 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 =...
Harish Kumar's user avatar
-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?
Farhaan Patel's user avatar
0 votes
1 answer
285 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 ...
Jay's user avatar
  • 1
2 votes
4 answers
302 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,...
roffster's user avatar
3 votes
1 answer
152 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-----...
user2989038's user avatar
5 votes
2 answers
316 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 (...
SynapseHub's user avatar
3 votes
2 answers
313 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 ...
pvdjay's user avatar
  • 33
1 vote
1 answer
894 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 | ... --------+-----------+-----------+-----------+-----------+-----------...
jcapo8856's user avatar
2 votes
2 answers
325 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 ...
Joe's user avatar
  • 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 ...
Joe Obbish's user avatar
  • 32.2k
7 votes
1 answer
6k 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 ...
Juan Velez's user avatar
  • 3,253
1 vote
2 answers
392 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 ...
Jeff.Clark's user avatar
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 ...
Suhayb's user avatar
  • 123
2 votes
1 answer
1k 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 ...
Muflix's user avatar
  • 1,099
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 ...
Eustass's user avatar
  • 21
2 votes
1 answer
534 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 ...
DoomerDGR8's user avatar
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 ...
magniche's user avatar
12 votes
1 answer
765 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) ) ...
boot4life's user avatar
  • 1,279
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 ...
MrVimes's user avatar
  • 629
0 votes
1 answer
109 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 , ...
Racer SQL's user avatar
  • 7,386
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 ...
Test User's user avatar
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 ...
Brett Caswell's user avatar
1 vote
2 answers
10k 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 ...
NaNerd's user avatar
  • 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 ...
Nate's user avatar
  • 1,697
4 votes
1 answer
759 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 ...
codepringle's user avatar