Questions tagged [pivot]
A pivot (or crosstab) converts row data into columnar data, and vice versa for an unpivot. Not all databases support explicit PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc), aggregate functions, or an extension.
473
questions
10
votes
2
answers
12k
views
How to flatten results of a table with two related "many" tables?
I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them.
I have the following tables (somewhat abbreviated ...
- 245
7
votes
1
answer
14k
views
can I replace nulls in a PIVOT with zeroes?
I am using the PIVOT function in Oracle and am curious if I can replace the null values with zeroes? I know I can wrap the entire query in another SELECT and then use COALESCE on the values, but I am ...
- 2,844
7
votes
4
answers
2k
views
Returning rows where all non-NULL values are equal
Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say:
WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL
this ...
- 6,612
5
votes
2
answers
74k
views
Dynamic pivot: sum of sales per month
My table is like this:
Sitecode Month Amount
-------- ----- ------
XX Jan 1000
XX Jan 3000
XX Apr 3000
XX Apr 1000
What I want is, to show ...
- 127
1
vote
1
answer
621
views
Pivot a table to repeat the first column for each following column
i'm not sure how to explain so I'll try and write out an example.
I'm currently using an access database with a table in the following format
1 A B C D
2 A B C D
I'm trying to perform a query that ...
- 45
1
vote
1
answer
172
views
Transposing data
I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to ...
- 13
3
votes
2
answers
1k
views
Showing data in a table horizontally
I have a table that stores sensor data (in SQLite) and looks like this;
TABLE Timestream:
idTimestream PK autoincrementing integer,
time int not null,
value float not null,
idSensor integer not ...
- 531
22
votes
3
answers
82k
views
Pivot rows into multiple columns
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:
╔══════════╦══════════╗
║ ...
- 887
4
votes
1
answer
8k
views
Security permission to only read SSAS Cube with Excel
I have an SSAS database set up on a SQL Server 2008 server. I have a Role set up with a group of users that I want to be able to access the Cubes in that database. I set up "Read definition" and "...
- 3,028
8
votes
3
answers
10k
views
MySQL single table static and dynamic pivot
I have a table that looks like this:
+----------------------------------------+
|Name | kode | jum |
+----------------------------------------+
| aman |kode1 |...
- 213
2
votes
1
answer
930
views
PIVOT SQL Server
SQL Server 2008 R2
PIVOT IO Stats
I have a script that collects IO stats from sys.dm_io_virtual_file_stats once a day and stocks them in a table. I want to be able to use these stats to generate ...
- 9,630
17
votes
3
answers
78k
views
Return a column per date in a range
Let's say I have Table A: BookingsPerPerson
Person_Id ArrivalDate DepartureDate
123456 2012-01-01 2012-01-04
213415 2012-01-02 2012-01-07
What I need to achieve with a view ...
- 443
3
votes
2
answers
930
views
Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?
I have something that looks like this (SQL Fiddle here):
Date Location Value
1/1/2012 Baltimore 36
1/1/2012 Houston 36
1/1/2012 Chicago 55
2/1/2012 Baltimore 49
2/1/...
- 143
0
votes
3
answers
143
views
How to convert query results into groups of common results
I have a query that returns results like this:
MyValue MyGroup
_______________|_______________
56 | I
_______________|_______________
12 | I
...
- 2,484
18
votes
3
answers
11k
views
Dynamically Define a Range in a Dimension
I have an issue that I face every time I decide to build a cube, and I haven't found a way to overcome it yet.
The issue is how to allow the user to define a range of things automatically without ...
- 2,776
2
votes
2
answers
63k
views
Convert Rows to columns using 'Pivot' in SQL Server when columns are string data type [duplicate]
I need to know whether 'pivot' in SQL Server can be used for converting rows to columns if there is no aggregate function to be used. I saw lot of examples with aggregate function only. My columns are ...
- 193
5
votes
1
answer
1k
views
SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected
This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this ...
- 151
2
votes
1
answer
679
views
Oracle Discoverer like app native for SQL Server?
Can someone point me at some web based apps that work like Oracle Discoverer but native for SQL Server. We already the SQL connector running to actually use disco, but was wondering what other ...
- 21
3
votes
1
answer
31k
views
Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation
I've developed a timesheet recording system, that allows users to record:
A Period of time (9:00 to 12:00)
What they did during that time (DutyActivity: 1 hour report writing, 2 hours training)
When ...
- 141
1
vote
2
answers
3k
views
why does my pivot give me an error?
Msg 207, Level 16, State 1, Line 1
Invalid column name 'FullName'.
select FullName,
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, total from Mdata) AS ST
...
- 2,032
2
votes
2
answers
7k
views
SQL statement to combine main table and child table as columns?
Having a main table and a child table in a Microsoft SQL Server database (2005+), I want to show the user a single table in a grid view which includes rows from the main table and rows from the child ...
- 887
3
votes
1
answer
499
views
Implementing a PIVOT query
How can I pivot the following SELECT statement:
SELECT sc.CID,
sc.CodeName as OverviewText,
scRAG.CodeName as RAGStatusText
FROM StatusCode ...
- 2,032
5
votes
2
answers
2k
views
SQL Server 2005 : How to use pivot instead of a bunch of case statements?
Please show me how I can modify this SQL select to use pivot instead of all the case statements.
SELECT t.ProjectId,
COALESCE(MAX(ExecutiveChampion), 'n/a'),
COALESCE(MAX(BusinessOwner),...
- 2,032