Stack Overflow
Stack Overflow

Questions tagged [unpivot]

UNPIVOT describes a Query Language feature of some Relational Databases. The UNPIVOT feature converts columnar data into row data, and vice versa for the PIVOT feature.

Overview

UNPIVOT describes a Query Language feature of some Relational Databases. The UNPIVOT feature converts columnar data into row data, and vice versa for the PIVOT feature.

Databases that support PIVOT/UNPIVOT syntax:

  • Oracle 11g+
  • SQL Server 2005+

Alternative approaches

While MySQL does not have a UNPIVOT function, this can be replicated using a SELECT statement with UNION ALL for each column.

Alternative meanings

UNPIVOT is also a feature of other applications such as Business Intelligence Query tools and some spreadsheets.

1361 questions
3
votes
1 answer

CROSS APPLY versus UNPIVOT

I found that UNPIVOT is able to automatically exclude fields that have NULL values. However, CROSS APPLY-VALUES method is not able to do that. Does anyone know how to automatically exclude NULL value fields from CROSS APPLY-VALUES? Let's say,…
Casperonian
  • 176
  • 5
  • 19
3
votes
2 answers

Transpose Columns or unpivot SQL Query

I am currently attempting to transpose some data inside an SQL query however I can not seem to find a solution using un-pivot. Example of the Data I am working with is SELECT * FROM (SELECT 'ACCOUNTS' AS Dept , DATENAME(MONTH, GETDATE())…
user3615823
3
votes
3 answers

Pivot or unpivot

I have a table with hourly statistics that I need to pivot (or unpivot?) The starting table is of the form: [SystemID] [Hour] [CallStarts] [AvgDuration] 1 0 3 27 1 1 10 58 1 2 …
Dino
  • 121
  • 9
3
votes
1 answer

Type cast error when unpivoting columns, why?

Im trying to UNPIVOT some columns in the mdsb.sys.database table but having some trouble with some of the columns. I tried to CAST all columns to NVARCHAR(128) in a CTE before UNPIVOTing the table but it still doesnt work. Can someone explain why…
Daniel Björk
  • 2,475
  • 1
  • 19
  • 26
3
votes
1 answer

SQL Server , restrict UNPIVOT to order columns automatically

I have a table with data in one row: Account | OrderID | OrderName | Priority | Fasting |AssignedTo |ResultsTo …
Sri Reddy
  • 6,832
  • 20
  • 70
  • 112
3
votes
1 answer

unpivot table different datatypes with cases

With the sql below I get the error my datatypes are not equal. C1 is varchar and C2 is a number. I found out pivot tables must be of the same datatype, but how would I convert the number into a varachar while using case statements such as…
user2213892
  • 139
  • 1
  • 3
  • 10
3
votes
1 answer

How to replace a functional (many) OUTER APPLY (SELECT * FROM)

Applies to Microsoft SQL Server 2008 R2. The problem is If we have a few dozen Outer Apply (30) then they begin to work pretty slowly. In the middle of the Outer Apply I have something more complicated than a simple select, a view. Details I'm…
Tomasito
  • 1,864
  • 1
  • 20
  • 43
3
votes
3 answers

How to select all values that are not numeric across multiple columns in SQL Server?

I have one table Prices ID Price_1 Price_2 Price_3 P1 10 11 12 P2 13 14 15 P3 aa 16 bb P4 19 cc 20 As you can see from above, some values from columns Price_1, Price_2 and Price_3 might not be numeric. What…
Jackson Tale
  • 25,428
  • 34
  • 149
  • 271
3
votes
2 answers

SQL Server PIVOT functionality

If we PIVOT any table and UNPIVOT that table do we get our original table?
MaxRecursion
  • 4,773
  • 12
  • 42
  • 76
3
votes
1 answer

UNPIVOT on an indeterminate number of columns

How can I write a query that will unpivot a table that always has 1 row and many columns to a result set that has 2 columns: column_name and value. I understand the underlying structure of the table is where the real problem lies, but I cannot…
invertigo
  • 6,336
  • 5
  • 39
  • 64
3
votes
2 answers

How to group by values from different columns into one row?

I have a source table that look like this: How to make result table looks like this. I could use solution in sql or in java code. Please, could anyone help me. Thanks.
klipa
  • 145
  • 2
  • 3
  • 10
3
votes
2 answers

Display all columns in table with the count of populated records in each column

I need to write a statement to display every column in a table, the count and percentage of records with that column populated, and the distinct count of values. For example if I had a table Address containing: Addr1 | AddrText | PostalCode 11111 |…
user1985569
  • 183
  • 2
  • 8
3
votes
1 answer

Convert Join Result to Two Column with SQL

I have a JOIN Result as following Address1 Address2 Address3 ABC XYZ LMN I want to convert them to following format Address Level ABC 1 XYZ 2 LMN 3
InTheWorldOfCodingApplications
  • 2,526
  • 6
  • 46
  • 87
3
votes
1 answer

Transpose or unpivot every other column

Basically what I am trying to do is transpose every other column to a row with the following columns data beside it. The source can have only two columns up to one-thousand and millions of rows. I am only doing this on a per-row basis. I have tried…
DeusAphor
  • 259
  • 1
  • 8
3
votes
2 answers

Pivot query to return multiple repeating groups?

I'm trying to get a result set (which will be inserted into a table) that has multiple repeating groups. Here's a script that shows a very simplified version of the data I'm starting out with: CREATE TABLE #Aggregate( StoreKey int , …
Sylvia
  • 2,578
  • 9
  • 30
  • 37
1 2 3
…
90 91