Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [unpivot]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
2 answers
40 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 ------------- ------------------...
user avatar
2 votes
2 answers
73 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.
user avatar
0 votes
1 answer
34 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 ...
user avatar
2 votes
1 answer
719 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 | ...
user avatar
-2 votes
2 answers
46 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 ...
user avatar
1 vote
1 answer
22 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 | ...
user avatar
1 vote
1 answer
70 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 ...
user avatar
0 votes
1 answer
474 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 ...
user avatar
  • 181
-1 votes
2 answers
382 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 ...
user avatar
2 votes
2 answers
489 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 ...
user avatar
  • 220
0 votes
0 answers
1k views

Unpivot in MYSQL?

Please I need help on how I can transform the data that appears in the attached photo. Maybe Unpivot SQL?
user avatar
1 vote
1 answer
247 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 ...
user avatar
2 votes
1 answer
137 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 ...
user avatar
  • 21
2 votes
2 answers
9k 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 ----------------------------------...
user avatar
  • 95
1 vote
2 answers
420 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: ...
user avatar
  • 37
0 votes
1 answer
225 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 ...
user avatar
  • 11
0 votes
1 answer
533 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',...
user avatar
  • 103
4 votes
1 answer
676 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. ...
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 ...
user avatar
10 votes
2 answers
29k 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 =...
user avatar
-1 votes
1 answer
1k 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?
user avatar
0 votes
1 answer
238 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 ...
user avatar
  • 1
2 votes
4 answers
279 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,...
user avatar
3 votes
1 answer
127 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-----...
user avatar
5 votes
2 answers
244 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 (...
user avatar
3 votes
2 answers
196 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 ...
user avatar
  • 33
1 vote
1 answer
780 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 | ... --------+-----------+-----------+-----------+-----------+-----------...
user avatar
2 votes
2 answers
246 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 ...
user avatar
  • 349
12 votes
1 answer
990 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 ...
user avatar
  • 30.3k
7 votes
1 answer
3k 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 ...
user avatar
  • 3,165
1 vote
2 answers
180 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 ...
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 ...
user avatar
  • 123
2 votes
1 answer
754 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 ...
user avatar
  • 815
2 votes
1 answer
2k 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 ...
user avatar
  • 21
4 votes
1 answer
2k 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 ...
user avatar
12 votes
1 answer
676 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) ) ...
user avatar
  • 1,249
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 ...
user avatar
  • 619
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 , ...
user avatar
  • 7,100
5 votes
1 answer
4k 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 ...
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 ...
user avatar
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 ...
user avatar
  • 113
2 votes
1 answer
5k 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 ...
user avatar
  • 1,667
4 votes
1 answer
702 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 ...
user avatar
1 vote
1 answer
128 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 ║ ╠═════╬═══════╬════╬════╬═════╬═════╬═════╬═════╬════...
user avatar
8 votes
1 answer
2k views

Why Does UNPIVOT Work on Compatibility Level 80 DB?

I'm trying to pull data using UNPIVOT on a SQL Server 2008 SP3 database running at Compatibility Level 80. This should mean UNPIVOT doesn't work, but in my case it does under certain circumstances... ...
user avatar
  • 95
29 votes
2 answers
19k views

Why does SQL Server require the datatype length to be the same when using UNPIVOT?

When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT ...
user avatar
  • 9,466
11 votes
1 answer
33k views

Passing column names dynamically to UNPIVOT

I have a table with following data First Second Third Fourth Fifth Sixth 2013-08-20 2013-08-21 2013-08-22 2013-08-23 2013-08-24 2013-08-25 And ...
user avatar