Pivot In SQL Query

We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

SELECT *
FROM (
    SELECT
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
        InvoiceAmount as Amount
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr,
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

SQL Wildcards

A wildcard character can be used to substitute for any other character(s) in a string.

Using the SQL % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

SELECT * FROM table_Name
WHERE column_Name LIKE 'ber%';

Using the SQL _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by "erlin":

SELECT * FROM table_Name
WHERE column_Name LIKE '_erlin';

Using the SQL [charlist] Wildcard

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

SELECT * FROM table_Name
WHERE column_Name LIKE '[bsp]%';


The following SQL statement selects all customers with a City starting with "a", "b", or "c":

SELECT * FROM table_Name
WHERE column_Name LIKE '[a-c]%';

The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":

SELECT * FROM table_Name
WHERE column_Name LIKE '[!bsp]%';