Questions tagged [unpivot]
53 questions
30
votes
2 answers
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 require the length to be the same?
Let's say that I…
Taryn
- 9,746
- 4
- 48
- 74
13
votes
1 answer
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 using UNPIVOT
SELECT Data
,DATENAME(DW, Data)…
Sai Chaitanya M
- 343
- 1
- 3
- 9
12
votes
1 answer
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 of the SQL Monitor report for the isolated…
Joe Obbish
- 32,976
- 4
- 74
- 153
12
votes
1 answer
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)
) x(Col0, Col1)
) someValues
This takes every row…
boot4life
- 1,289
- 1
- 11
- 19
11
votes
2 answers
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
=stuff(
(select ',' + quotename (C.name)
…
Harish Kumar
- 165
- 1
- 2
- 9
8
votes
1 answer
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...
Works:
Independent SELECT query of the…
Andrew
- 95
- 1
- 5
7
votes
1 answer
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 using UNPIVOT once and specifying all the available…
Juan Velez
- 3,303
- 19
- 57
- 75
5
votes
2 answers
How to produce an output of fields that are NULL (combined from multiple columns+tables)?
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 (sometime multiple rows in the same table). It is…
SynapseHub
- 51
- 1
5
votes
1 answer
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 Political Philosophical Historical NULL NULL
4…
Test User
- 65
- 1
- 5
4
votes
1 answer
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 table in the form of -
(Pk int, WeekYear,…
codepringle
- 195
- 5
4
votes
2 answers
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
---------------------------------------------------------------------------
1001 …
abbhey
- 115
- 1
- 1
- 8
4
votes
1 answer
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. first name, middle name, last name, home…
user3621633
- 275
- 2
- 10
4
votes
1 answer
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
After updating table 1, I have a new row with…
magniche
- 43
- 1
- 4
3
votes
1 answer
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-----1
When transposed, it looks like…
user2989038
- 31
- 2
3
votes
2 answers
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 table and return 1 row for each batch of, at most, a…
pvdjay
- 33
- 3