Questions tagged [unpivot]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
75 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
1answer
52 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 ...
0
votes
1answer
26 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 ...
0
votes
2answers
2k 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 ----------------------------------...
1
vote
2answers
100 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: ...
0
votes
1answer
125 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 ...
0
votes
1answer
333 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',...
4
votes
1answer
297 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. ...
-1
votes
1answer
2k 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 ...
4
votes
2answers
9k 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 =...
-1
votes
1answer
790 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
1answer
130 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 ...
2
votes
4answers
210 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,...
2
votes
1answer
95 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-----...
5
votes
2answers
98 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 (...
3
votes
2answers
153 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 ...
1
vote
1answer
605 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 | ... --------+-----------+-----------+-----------+-----------+-----------...
2
votes
2answers
169 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 ...
13
votes
1answer
809 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 ...
5
votes
1answer
1k 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 ...
1
vote
2answers
95 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 ...
1
vote
1answer
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 ...
2
votes
1answer
446 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 ...
2
votes
1answer
1k 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 ...
4
votes
1answer
805 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 ...
12
votes
1answer
533 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) ) ...
3
votes
1answer
1k 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 ...
0
votes
1answer
99 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 , ...
3
votes
1answer
3k 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 ...
1
vote
0answers
44 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 ...
1
vote
2answers
7k 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 ...
2
votes
1answer
3k 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 ...
4
votes
1answer
675 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 ...
1
vote
1answer
117 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 ║ ╠═════╬═══════╬════╬════╬═════╬═════╬═════╬═════╬════...
8
votes
1answer
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... ...
0
votes
0answers
344 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 ...
28
votes
2answers
13k 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 ...
10
votes
1answer
25k 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 ...