The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. Not all databases support the PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc) and aggregate functions.
0
votes
1answer
54 views
How to create a table in MySQL with automatic numeration of columns?
Example. My data is from Table name:BIKE
Bike_No Repair_Date Repair_Cost
-------- ----------- ------------
ABC1234 2013-01-05 50.00
BMX5678 2013-02-04 75.00
ABC1234 ...
4
votes
1answer
41 views
Oracle pivot on a column with delimited data
My data is like:
keycol,col1,col2,col3
1,a;b;c,some data,some other data
2,x,some data,some other data
3,y;z,some data,some other data
Where a column is delimited in the source system with ...
0
votes
0answers
20 views
Aggregate query on multiple values of same field [migrated]
I am using sql server 2012, I have a table of credit card transactions as follows:
CardScheme RespCode
=========================
Visa Declined
MasterCard Approved
MasterCard Approved
...
1
vote
0answers
47 views
Why is dynamic pivoting not supported?
TL;DR What is the reason that in 2013 we still can't define a VIEW that displays some pivoted data with a dynamic column set.
TL;DR/2 Is pivoting data a denormalization technique?
First: Not going ...
7
votes
2answers
162 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 ...
0
votes
0answers
78 views
MySQL Pivot Table Naming Conventions
I have some tables (users, jobs, customers) that are each tied to 'groups'. As each table is linked to 'groups', I feel inclined to call the actual group tables user_groups, job_groups and ...
0
votes
1answer
92 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
votes
1answer
212 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 ...
6
votes
4answers
283 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 ...
-7
votes
2answers
814 views
Display month in columns and the sum of sales below the columns in sql [closed]
My result is this:
Sitecode Month Amount
-------- ----- ------
XX Jan 1000
XX Jan 3000
XX Apr 3000
XX Apr 1000
Result will show like ...
4
votes
3answers
298 views
SQL Server pivot query on Oracle source
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:
╔══════════╦══════════╗
║ ...
1
vote
1answer
93 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 ...
1
vote
1answer
92 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 ...
0
votes
0answers
483 views
Dynamically Converted rows into columns (Pivoting)
Query
SELECT
Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total,
COUNT(studfeedback.studfeedback_Rating) AS totastudents, ...
0
votes
0answers
197 views
with using PIVOT table sql server
I am facing a small issue while using PIVOT table.
My problem is as following.
I have a single table with the procedures id and their steps and i want all the steps needs to be displayed in 5 column ...
2
votes
2answers
699 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 ...
0
votes
3answers
162 views
MySQL data view
I have a table that looks like this:
+----------------------------------------+
|Name | kode | jum |
+----------------------------------------+
| aman |kode1 ...
1
vote
2answers
2k views
SQL query to return a column per date in date range?
Let me explain what I've got and what I'm trying to achieve.
Let's say I have Table A: BookingsPerPerson
Person_Id ArrivalDate DepartureDate
123456 2012-01-01 2012-01-04
213415 ...
3
votes
2answers
207 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
...
0
votes
3answers
93 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
...
3
votes
1answer
240 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 ...
1
vote
2answers
548 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
votes
1answer
2k 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)
...
3
votes
2answers
721 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'),
...
1
vote
1answer
295 views
How can I pivot this select?
SELECT
sc.CID,sc.CodeName as OverviewText,scRAG.CodeName as RAGStatusText
FROM
StatusCode sc
LEFT OUTER JOIN
ProjectOverview po
ON sc.CID = po.ProjectOverviewCID AND po.ProjectId ...
