CODEWITHSUNDEEP
sql-serversql-server-2008pivotpivot-table
Richard Gale
Richard Gale

Reputation: 1952

SQL Results data from rows to columns

I have created a query which returns me the following:

[Branch], [SaleDate], [ReceiptNo]

My [SaleDate] column is a concatenation of the SaleMonth and SaleYear in the format '01/2013'

What I require is these results to come out as a form of a table with:

A Column for each [SaleDate]

A Row for each [Branch]

The value for each Row/Column will be a COUNT([ReceiptNo])

Any pointers will be greatly appreciated.

Thanks in advance.

Upvotes: 1

Views: 77

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Use the PIVOT table operator:

SELECT *
FROM
(
   SELECT ReceiptNo, SaleDate, Branch
   FROM tablename
) AS t
PIVOT
(
  COUNT(ReceiptNo)
  FOR SaleDate IN([01/2013], [02/2013], [03/2013], ...)
) AS p;

To do this dynamically instead of listing the values of SaleDate manually, you have to use dynamic SQL, like this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);


select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(SaleDate)
                      FROM tablename
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT *
FROM
(
   SELECT ReceiptNo, SaleDate, Branch
   FROM tablename
) AS t
PIVOT
(
  COUNT(ReceiptNo)
  FOR SaleDate IN(' + @cols + ')) AS p;';

execute(@query);

Upvotes: 1

Related Questions