28
It's easiest with SUM() and a CASE statement:
select CompanyName,
sum(case when StatusID=1 then 1 else 0 end) as TotalOpenClaims,
sum(case when StatusID=2 then 1 else 0 end) as TotalClosedClaims,
sum(case when StatusID=3 then 1 else 0 end) as TotalReOpenedClaims,
sum(case when StatusID=4 then 1 else 0 end) as TotalPendingClaims
from Claims
group by ...
23
Are there examples where PIVOT is slower?
This is unlikely in simple cases. As Itzik Ben-Gan notes in his SQL Server Pro article, Pivoting Data when looking at the plan for a PIVOT query (emphasis added):
Figure 3 shows the plan for the PIVOT query. As you can see, this plan is very similar to that of the standard solution—so much so that if you look at ...
20
This is a bit messy to get the final result because you have multiple SCHEMA_VER for each date. Before I demonstrate how to do this with dynamic SQL, I'll first show how to do it with static code to get the logic correct. In order to get the final result you can utilize both pivot and unpivot.
But first, I'd change your original query to use the following:
...
18
This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.
My first suggestion would be to determine if you have a calendar table or a table that contains all of the dates that you want to display. If not, then I would suggest ...
17
This is a typical pivot transformation, and conditional aggregation, as suggested by Phil, is the good old way of implementing it.
There is also a more modern syntax of achieving the same result, which uses the PIVOT clause:
SELECT
CompanyName,
TotalOpenClaims = [1],
TotalClosedClaims = [2],
TotalReOpenedClaims = [3],
TotalPendingClaims = [...
15
To your data set, I added a row number (ROW_NUMBER), and this field is divided by 5 (you wanted 5 columns in the output) and hold the rest and the hole number of this division in fields inRows , inColumns. Base on this, will do the pivot.
There is a special case when you divide by 5 and have rest = 0 ; for this I use case...
,RN / 5 + CASE WHEN RN % 5 = 0 ...
14
Attribute names in XML are not allowed to start with a number, see NameStartChar.
You have to come up with alternative names for your attributes and encode that in a separate @cols variable specifying column aliases for your dynamic pivot query.
SELECT @cols2 = STUFF((SELECT distinct ',' +
quotename(convert(char(10), [StayDate] , 120)...
13
Simple case, static SQL
The non-dynamic solution with crosstab() for the simple case:
SELECT * FROM crosstab(
'SELECT b.x, f.name, f.x * b.x AS prod
FROM foo f, bar b
ORDER BY 1, 2'
) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
, "F" int, "G" ...
answered Dec 27 '16 at 21:17
Erwin Brandstetter
130k17 gold badges308 silver badges453 bronze badges
13
Repeating the tests from Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - By Jeff Moden, 2010/08/06 (first published: 2008/08/19) on rextester
Unfortunately I can not access statistics for IO, Time or execution plans on rextester, but it has the unique benefit of being a common test environment that anyone here can tinker with and examine. I ...
13
Here is one way of getting the result set you want without doing the multiple joins. It takes a little more setup and uses two pivot operations instead of one, but avoids the multiple joins.
I admit that I had to look it up, but Ken O'Bonn had a great article. https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/...
12
Prior to writing a dynamic SQL query, you should always write a version that is hard-coded so you can get the syntax correct. So the first thing you need to do is write a working PIVOT query for any of the TruckId values that you need.
Static Version:
Let's say you need TruckID = 3, your code for the PIVOT would be similar to the following:
select Name, ...
12
SUM(CASE
For a limited number of Names you can use a SUM(CASE solution in this way:
SELECT
'Database status' as [DB Status],
SUM(CASE WHEN Name LIKE 'Test%' THEN 1 ELSE 0 END) As Test,
SUM(CASE WHEN Name LIKE 'Prod%' THEN 1 ELSE 0 END) AS Prod,
SUM(CASE WHEN Name = 'Migrated' THEN 1 ELSE 0 END) AS Migrated,
SUM(CASE WHEN Name = '...
11
What's the best we can do in PostgreSQL without a "column definition
list" to generate that kind of presentation?
If you frame this as a presentation problem, you might consider a post-query presentation feature.
Newer versions of psql (9.6) come with \crosstabview, showing a result in crosstab representation without SQL support (since SQL can't produce ...
11
The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. leave_type didn't seem relevant to the problem at hand.
CREATE TABLE dbo.EmpLeave
(
EmployeeID int,
leave_date date,
leave_type_color char(6)
);
INSERT ...
10
Terminology and Methodology
This kind of transformation – rows to columns – is called pivoting. It is typical to pivot data simultaneously with their aggregation, as seems to be a requirement in your case too. In SQL you can do both operations as a single logical step. Other SQL products even offer special syntactical extensions for pivoting, but there is a ...
9
What you are trying to do looks very much like a pivot operation. You could probably achieve the result you are looking for without a derived table, using conditional aggregation:
SELECT
sts_supplier_number,
COUNT(CASE WHEN orl_conditie_code = '001' THEN orl_conditie_code END) AS beschadigd,
COUNT(CASE WHEN orl_conditie_code = '002' THEN ...
9
GROUP BY and using MAX or SUM is the most used standard pivot way.
SELECT
results.sims_id
, results.subject
, MAX(CASE WHEN results.progress_check = "C1" THEN results.result END) "C1"
, MAX(CASE WHEN results.progress_check = "C2" THEN results.result END) "C2"
, MAX(CASE WHEN results.progress_check = "C3" THEN results.result END) "C3"
FROM
...
8
If you have the following table of data that you want to pivot:
CREATE TABLE yourtable (id int, name varchar2(1), value varchar(10));
INSERT ALL
INTO yourtable (id, name, value )
VALUES (1, 'A', '1500')
INTO yourtable (id, name, value )
VALUES (1, 'B', '4500')
INTO yourtable (id, name, value )
VALUES (2, 'C', '3.5')
...
8
You must Pivot data using GROUP BY with MAX aggregate and use CASE to filter by User_id.
Query:
SELECT User_id
, MAX(
CASE WHEN course_id = 1 THEN course_location END
) as Course_1
, MAX(CASE WHEN course_id = 2 THEN course_location END) as Course_2
, MAX(CASE WHEN course_id = 3 THEN course_location END) as Course_3
, MAX(CASE WHEN ...
8
Given:
DECLARE @T table
(
ID integer NOT NULL PRIMARY KEY,
hProp integer NOT NULL,
iDayOfMonth integer NOT NULL,
dblTargetPercent decimal(6,4) NOT NULL
);
INSERT @T
(ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES
(117, 10, 05, 0.1400),
(118, 10, 10, 0.0500),
(119, 10, 15, 0.0100),
(120, 10, 20, 0.0100);
You can get ...
7
This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.
If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:
select loannumber,
[First Name],
[Middle Name],
[...
7
The problem here seems to me to be largely more of a scoping issue - you are likely having difficulty solving this problem on account of the requirements not being defined well enough. With the description and sample data provided, there are at least three partial solutions, none of which may be applicable to your particular use cases. With the test data ...
7
The question can be split into 3 main operations:
Partition rows by IndividualNumber (using variables)
Pivot Partitioned rows to columns (using CASE)
Add Name and other information and remove NULL
You can look at the sample here: SQL Fiddle
Partition rows by IndividualNumber:
The behavior of this query is similar to the ROW_NUMBER() Window Function ...
7
Assuming 2016-04-05 0:27:15 instead of 2016-04-05 1:27:15 in the underlying table, the question would make more sense to me:
CREATE TABLE tbl (created_at timestamp, status text);
INSERT INTO tbl VALUES
('2016-04-05 00:27:15', 'info')
, ('2016-04-05 03:27:15', 'info')
, ('2016-04-05 05:27:15', 'warn')
, ('2016-04-05 10:27:15', 'info')
, ('2016-04-05 11:27:...
7
Yes, more concise ways of pivoting exist for your scenario.
Option 1
For instance, you could use the PIVOT operator:
SELECT
*
FROM
(
SELECT
road_id,
insp_year,
condition
FROM
road_insp
) derived
PIVOT
(
MAX(condition)
FOR insp_year IN
(
2009 AS condition_2009,
2010 AS condition_2010,
...
7
Unpivot Table
Being the product of a transformation, your desired form here is called an "Unpivot table". The form you're starting from here is either normalized data, or the result of a transformation itself called a "pivot."
You can pivot and unpivot without losing data. Pivoting and operations like this emerge from OLAP functionality, where they're ...
7
This can be done with PIVOT and it can be done dynamically, but before you jump in trying to do this dynamically, you should try to get the result you want using a static or hard-coded version of the query, then convert it to dynamic sql.
Since you are using SQL Server 2008 and you want a total column for both Usage and Cost, I would first start by looking ...
7
I think it's important to strictly separate the two tasks you're trying to perform in one step here.
Classification
Transformation
For classifying the data, my instinct here is to recommend a lookup table to rigorously map records to a parent class. e.g.
CREATE TABLE StatusType (
ID INT IDENTITY PRIMARY KEY,
[Name] VARCHAR(10) NOT NULL ...
6
This can be done, very efficiently, too. Not in a single statement, though, since SQL demands to know the return type at call time. So you need two steps. The solution involves a number of advanced techniques ...
Assuming the same table as @Denver in his answer:
CREATE TABLE hstore_test (
id serial PRIMARY KEY
, hstore_col hstore
);
Solution 1: Simple ...
6
Given that the data is changing very infrequently and there will therefore be very little overhead in maintaining the indexed views, an approach using indexed views could help performance. As you likely noticed when trying to create an indexed view that uses PIVOT, there are a lot of restrictions on what syntactic constructs can be used in indexed views.
...
Only top voted, non community-wiki answers of a minimum length are eligible
