27
You can use the PIVOT function to perform this query. My answer will include both a Static and dynamic version because sometimes it is easier to understand it using a static version.
A Static Pivot is when you hard-code all of the values that you want to transform into columns.
-- first into into a #temp table the list of dates that you want to turn to ...
27
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 ...
22
There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.
Aggregate /Case version:
select personid,
max(case when optionid = 'A' then 1 else 0 end) OptionA,
max(case when optionid = 'B' then 1 else 0 end) OptionB,
...
22
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
As others have said this is known as a PIVOT. There are several ways in which you can transform your data from rows into columns of data.
If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT function you would use an aggregate function with a CASE statement.
Aggregate/CASE Version:
select sitecode,
sum(case when [...
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 ...
16
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 = [...
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)...
14
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 ...
12
HOW TO DO THIS WITH T-SQL:
As requested this is an alternative to my previous answer that showed how to do it per-user with Excel. This answer shows how to do the same thing shared/centrally using T-SQL instead. I do not know how to do Cubes, MDX or the SSAS stuff for this, so maybe Benoit or someone who does know that can post its equivalent...
1. Add ...
12
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" int, "H" int, "I" int, "J" int);
I order resulting columns by foo....
answered Dec 27 '16 at 21:17
Erwin Brandstetter
116k16 gold badges260 silver badges397 bronze badges
12
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 ...
11
If you have a known number of columns, then you can use a static version similar to the other answer. But if you have an unknown number then you can use a prepared statement similar to this:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when kode = ''',
kode,
''' then jum else 0 end) AS ',
kode
)
) INTO @...
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
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/...
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 ...
11
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 = '...
10
This would be the equivalent in SQL Server syntax. Based on my reading of the Oracle docs, NULLIF and PIVOT appear to have the same format as their SQL Server kin. The challenge will be the pivot list which needs to be static unless you make the query dynamic as Itzik demonstrates but I have no idea if that can be translated to P/SQL
WITH PersonOptions(...
10
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, ...
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
As Martin Smith said, you need to pivot the data, whether with an explicit PIVOT as referenced or something like this (SQL Fiddle):
SELECT SiteCode
, SUM(Case When Month='Jan' Then Amount Else 0 End) MonthJan
, SUM(Case When Month='Apr' Then Amount Else 0 End) MonthApr
FROM Result GROUP BY SiteCode;
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 ...
8
I'm old school, and find CASE easier to work out in my head than PIVOT. I'm sure bluefeet will show up shortly and put me to shame, but in the meantime you can play with this dynamic SQL query. Assuming your table stores DATE and not DATETIME (or even worse, VARCHAR):
USE tempdb;
GO
CREATE TABLE dbo.a
(
Person_Id INT,
ArrivalDate DATE,
...
8
HOW TO DO THIS WITH EXCEL
Here's how I would do it in Excel...
1. Add SalaryRanges Excel Table
Insert a new worksheet, call it "Salary Ranges". In row one add the text headers "Min", "Max" and "Range" in that order (should be cells A1, A2, A3, respectively).
In cell B2 add the following formula:
=IF(A2="","",IF(A3="","+",A3-1))
In cell C2 add this ...
8
To convert row to column you need to use CASE statement. And to get count for individual kode you need to use SUM function like this one:
SELECT NAME,
SUM(CASE kode WHEN 'kode1' THEN jum ELSE 0 END) AS kode1
,SUM(CASE kode WHEN 'kode2' THEN jum ELSE 0 END) AS kode2
,SUM(jum) AS `Count`
FROM Table1
GROUP BY Name
See this SQLFiddle
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
The most compact syntax I can find is:
SELECT *
FROM @T AS t
WHERE EXISTS
(
SELECT ISNULL(ISNULL(a, b), c)
INTERSECT
SELECT ISNULL(ISNULL(b, c), a)
INTERSECT
SELECT ISNULL(ISNULL(c, a), b)
);
Based on an idea from one of my old blog posts that describes how to use INTERSECT and EXCEPT to replace comparisons like a <> b OR (a ...
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],
[...
Only top voted, non community-wiki answers of a minimum length are eligible
