how to

How to Pivot Data in SQL Without the PIVOT Operator

by

Not every DBMS includes a dedicated PIVOT operator. And even in DBMSs like SQL Server that do provide a PIVOT operator, you might prefer alternatives for better readability or more control over the transformation logic. Fortunately, you can pivot data using standard SQL techniques that work across virtually any relational database.

The main approach is conditional aggregation. This is where you use CASE statements within aggregate functions to selectively pull values into specific columns. It tends to be more verbose than using a PIVOT operator, but it’s also more transparent, more flexible, and completely portable across database platforms.

Read more

How to Add a Column in SQL Server: A Complete Guide

by

The basic syntax for adding a column in SQL Server is just two lines. But there are enough edge cases to really throw you off guard if you’re not careful. Your actual code will depend on things like, how you define the column, whether the table already has data, what constraints you need, etc. In the real world, there’s quite a bit more to know than just two lines.

This guide walks through all the common scenarios so you have a solid reference regardless of what you’re trying to do.

Read more

Building a Product Performance Matrix in SQL

by

When you’re managing multiple products across different sales channels or regions, raw data tables don’t usually cut it. You need to see everything at once. For example, which products are crushing it online but underperforming in retail, or which regions are driving growth while others stagnate. A product performance matrix gives you that bird’s-eye view, turning rows of transaction data into a grid that shows patterns instantly.

Read more

Fix Error 2744 “Multiple identity columns specified for table” in SQL Server

by

If you’re getting SQL Server error 2744 that reads “Multiple identity columns specified for table…“, it looks like you’re trying to define a table to have more than one identity column.

SQL Server restricts identity columns to just one per table. If you try to add another one, you’ll get the above error.

The easiest way to address this issue is to leave the table with one identity table and be done with it. But that might not always be practical. Maybe you need a column that increments a different value than the identity column. Fortunately, there are ways of doing that.

Read more

Pivoting JSON Data in SQL Server

by

JSON has become a common format for data exchange, and SQL Server’s built-in JSON support makes it straightforward to work with JSON data directly in your queries. But what happens when you need to pivot JSON data – transforming nested structures or array elements into a columnar format for reporting or analysis?

SQL Server provides functions like OPENJSON(), JSON_VALUE(), and JSON_QUERY() that let you extract and manipulate JSON data. Combined with standard pivoting techniques, you can reshape JSON data into whatever format you need. This can be particularly useful when you’re receiving JSON from APIs, storing semi-structured data, or working with configuration data that doesn’t fit neatly into traditional tables.

Read more

Fix Error 4901 “ALTER TABLE only allows columns to be added that can contain nulls… etc” in SQL Server

by

If you’re getting an error in SQL Server that reads something like “ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or… etc etc“, it’s probably because you’re trying to add a NOT NULL column to a table that already contains data.

This error occurs due to the fact that the NOT NULL constraint will be violated for every row in the table. Think about it for a second. When you first add the column, there’s no data. It’s not until you run a subsequent INSERT statement (or some other process that populates the table) that you will get data. In the meantime, all values in your new column will be NULL. And that, of course, violates the NOT NULL constraint.

Read more