Using the PIVOT operator on SQL Server 2005+

The PIVOT operator is used to transform rows into column by an aggregate function. One big limitation is that you have to know the columns, but still, it's useful on some use cases, like when you want to aggregate something by hours (for example).

About having to know the column names limitation: This is probably a design decision, because someone could write a query that has too many columns and cause all sort of problems, like exceeding the limit of columns or return column that the application is not prepared to receive.

To keep the example easy to understand we will create a simple Authentication table (which holds authentications):

IF OBJECT_ID('Authentication') IS NOT NULL
 DROP TABLE Authentication

CREATE TABLE Authentication
(
 AuthenticationId INT,
 Date SMALLDATETIME,
 DocumentId UNIQUEIDENTIFIER
)

INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (1, DATEADD(hh, +1, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (2, DATEADD(hh, +1, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (3, DATEADD(hh, +2, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (4, DATEADD(hh, +3, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (5, DATEADD(hh, +4, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (6, DATEADD(hh, +4, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (7, DATEADD(hh, +5, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (8, DATEADD(hh, +6, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (9, DATEADD(hh, +7, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (10, DATEADD(hh, +7, GETDATE()), NEWID())
INSERT INTO Authentication (AuthenticationId, Date, DocumentId) VALUES (11, DATEADD(hh, +8, GETDATE()), NEWID())

Now we have some authentications. Let's say some manager wants a report of authentications by hour to know how many people comes to pay bills on the company stores (assuming that some kind of authentication is done when receiving payments). The information can be obtained by doing a query like this:

SELECT [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
  [11], [12], [13], [14], [15], [16], [17], [18], [19],
  [20], [21], [22], [23]
FROM (SELECT AuthenticationId, DATEPART(HOUR, Date) AS Hour FROM Authentication) AS TBL
PIVOT (
 COUNT(AuthenticationId)
 FOR Hour IN
 ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
  [11], [12], [13], [14], [15], [16], [17], [18], [19],
  [20], [21], [22], [23])
) AS PVT

The result will show the authentication count for each hour. Now you can copy it and paste on Excel. Maybe generate some charts (assuming your use case has more data and more rows).