Skip to main content
29 votes
Accepted

How to get count for different columns on same table

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, ...
Philᵀᴹ's user avatar
  • 31.8k
23 votes

Which is faster, SUM(CASE) or CTE PIVOT?

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 ...
Paul White's user avatar
  • 84.3k
18 votes

How to get count for different columns on same table

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, ...
Andriy M's user avatar
  • 23k
16 votes

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

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 ...
Erwin Brandstetter's user avatar
15 votes
Accepted

How to Pivot in T-SQL

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 , ...
Sabin B's user avatar
  • 4,401
14 votes
Accepted

When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?

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 ...
Mikael Eriksson's user avatar
14 votes
Accepted

How to pivot on multiple columns in SQL Server?

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 ...
Jonathan Fite's user avatar
13 votes

Which is faster, SUM(CASE) or CTE PIVOT?

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 ...
SqlZim's user avatar
  • 2,420
12 votes

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

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 ...
Daniel Vérité's user avatar
12 votes

How to pivot rows into columns MySQL

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(...
Raymond Nijland's user avatar
12 votes
Accepted

Is it possible to PIVOT on a LIKE statement

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, ...
McNets's user avatar
  • 23.8k
11 votes
Accepted

How to create recurrent weekdays as columns in a pivot?

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. ...
Aaron Bertrand's user avatar
10 votes
Accepted

Query monthly having group_concat and group by need help

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 ...
Andriy M's user avatar
  • 23k
10 votes

How to pivot on multiple columns in SQL Server?

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, ...
Paul White's user avatar
  • 84.3k
9 votes

How to get multiple row data into a row with multiple columns

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 ...
Julien Vavasseur's user avatar
9 votes
Accepted

group a multiple union statement

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 ...
Andriy M's user avatar
  • 23k
8 votes

Converting rows to columns

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 ...
Erwin Brandstetter's user avatar
8 votes
Accepted

How to pivot rows into columns MySQL

This is one way to pivot using standard SQL (and the part of the standard that MySQL implements). That means it not only works in MySQL, but also in most SQL databases: SELECT r0.sims_id, r0....
joanolo's user avatar
  • 13.4k
8 votes

Name for table with metric_name and metric_value columns

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 ...
Evan Carroll's user avatar
  • 63.2k
8 votes

Is it possible to PIVOT on a LIKE statement

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 ...
Peter Vandivier's user avatar
7 votes
Accepted

Insert data from multiple rows into multiple columns

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 ...
Julien Vavasseur's user avatar
7 votes
Accepted

Rotate table for year-over-year comparison

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 ...
Andriy M's user avatar
  • 23k
7 votes
Accepted

Pivot with rollup in sql server

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 ...
Taryn's user avatar
  • 9,676
6 votes
Accepted

RDS crosstab: ERROR: permission denied for language c

RDS doesn't let you install arbitrary C functions. It's a restricted environment. crosstab is part of the tablefunc extension. You may be able to CREATE EXTENSION tablefunc if it's on the whitelist ...
Craig Ringer's user avatar
  • 56.4k
6 votes
Accepted

Two rows into two columns

Here's a solution with a crosstab: DECLARE @SampleData TABLE ( Name varchar(50), Value varchar(50), Sequence int ) INSERT INTO @SampleData VALUES ('test' ,'A1', 1) ...
spaghettidba's user avatar
  • 11.3k
6 votes
Accepted

Convert row into concatenated string with column names included

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regular expressions" search-and-replace functionality included in SQL Server ...
Hannah Vernon's user avatar
  • 70.1k
6 votes

Convert row into concatenated string with column names included

Depending on how strict your formatting requirements are, you could use the FOR XML syntax. This will generate an XML document for your results, which will give you the field name and value on each ...
AMtwo's user avatar
  • 16.2k
6 votes

How to Pivot in PostgreSQL

This is a tricky one. crosstab() expects one (or no) value per category for each row_name. We can work around this restriction like this: SELECT id , COALESCE(cat01, max(cat01) OVER w) , ...
Erwin Brandstetter's user avatar
6 votes
Accepted

How to Pivot Multiple Columns

Instead of the PIVOT you can simply use the CASE WHEN .... By using GROUP BY then use MAX(CASE will return your expected result: Sample execution with the given sample data: DECLARE @...
Arulkumar's user avatar
  • 1,127
6 votes
Accepted

SELECT multiple values from one table having matching record in another table in one row

Actually, since there can be n number of values for a single record of table first. the number of columns to return is not exactly arbitrary. There is a maximum of columns, and this has a clean ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible