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,
...
- 30.7k
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 ...
- 69.1k
20
votes
Accepted
Help with PIVOT query
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 ...
- 9,466
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, ...
- 21.1k
15
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 ...
- 150k
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 , ...
- 3,462
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 ...
- 21.7k
13
votes
Accepted
How to pivot without fixed columns in TSQL?
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 ...
- 9,466
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 ...
- 2,400
13
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 ...
- 7,491
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 ...
- 27k
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(...
- 1,038
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,
...
- 22.7k
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. ...
- 176k
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 ...
- 21.1k
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, ...
- 69.1k
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
...
- 9,978
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
...
- 21.1k
8
votes
Accepted
QUERY - pivot multiple columns, variable number of rows
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 ...
- 2,303
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....
- 12.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 ...
- 55.7k
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 ...
- 4,134
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
...
- 9,978
7
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 ...
- 150k
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
...
- 21.1k
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 ...
- 9,466
6
votes
Accepted
Pull data from multiple tables in a view or function
I suggest a radically new approach with crosstab() from the additional module tablefunc. You need to install it once per database. Detailed instructions:
PostgreSQL Crosstab Query
SELECT k.name, ...
- 150k
6
votes
Accepted
Dynamically convert hstore keys into columns for an unknown set of keys
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 ...
- 150k
6
votes
Accepted
Indexed VIEW in SQL Server with PIVOT
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 ...
- 8,167
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)
...
- 10.9k
Only top scored, non community-wiki answers of a minimum length are eligible