Formatting SQL Output (Pivot)

Question

This is running on SQL Server 2008.

Anyway, I have sales data, and I can write a query to get the output to look like this:

id | Name       | Period  | Sales
1  | Customer X | 2013/01 | 50
1  | Customer X | 2013/02 | 45

etc. Currently, after running this data, I am rearranging the data in the code behind so that the final output looks like this:

id  | Name       | 2013/01 | 2013/02
1   | Customer X |   50    |   40

The issues are:

1. The date (YYYY/MM) range is an input from the user.
2. If the user selects more outputs (like, say, address, and a ton of other possible fields relating to that customer), that information is duplicated in every line. When you're doing 10-15 items per line, over a period of 5+ years, for 50000+ users, this causes problems with running out of memory, and is also inefficient.

I've considered pulling only the necessary data (the customer id -- how they're joined together, the period, and the sales figure), and then after the fact running a separate query to get the additional data. This doesn't seem like it would be efficient though, but it's a possibility.

The other, which is what I'm thinking should be the best option, would be to rewrite my query to go ahead and do what my current code behind is doing, and pivot the data together, that way the customer data is never duplicated and I'm not moving a lot of unnecessary data around.

To give a better example of what I'm working with, let's assume these tables:

Address

id | HouseNum | Street | Unit | City | State

Customer

id | Name | 

Sales

id | Period | Sales

So I would like to join these tables on the customer id, display all of the address data, assume the user inputs "2012/01 -- 2012/12", I can translate that into 2012/01, 2012/02 ... 2012/12 in my code behind to input into the query before it executes, so I have that available.

What I want it to look like would be:

id | Name | HouseNum | Street   | City | State | 2012/01 | 2012/02 | ... | 2012/12
1  | X    | 100      | Main St. | ABC  | DEF   |   30    |         | ... |   20

(no sales data for that customer on 2012/02 -- if any of the data is blank I want it to be a blank string "", not a NULL)

I realize I may not be explaining this the best way possible, so just let me know and I'll add more information. Thank you!

edit: oh, one last thing. Would it be possible to add a Min, Max, Avg, & Total columns to the end, which sum up all of the pivoted data? It wouldn't be a big deal to do it on the code behind, but the more sql server can do for me the better, imo!

edit: One more, the period is in the tables as "2013/01" etc, but I'd like to rename them to "Jan 2013" etc, if it's not too complicated?

Answer 1

You can implement the PIVOT function to transform the data from rows into columns. You can use the following to get the result:

select id,
  name,
  HouseNum,
  Street,
  City,
  State,
  isnull([2013/01], 0) [2013/01], 
  isnull([2013/02], 0) [2013/02], 
  isnull([2012/02], 0) [2012/02], 
  isnull([2012/12], 0) [2012/12],
  MinSales,
  MaxSales,
  AvgSales,
  TotalSales
from
(
  select c.id,
    c.name,
    a.HouseNum,
    a.Street,
    a.city,
    a.state,
    s.period,
    s.sales,
    min(s.sales) over(partition by c.id) MinSales,
    max(s.sales) over(partition by c.id) MaxSales,
    avg(s.sales) over(partition by c.id) AvgSales,
    sum(s.sales) over(partition by c.id) TotalSales
  from customer c
  inner join address a
    on c.id = a.id
  inner join sales s
    on c.id = s.id
) src
pivot
(
  sum(sales)
  for period in ([2013/01], [2013/02], [2012/02], [2012/12])
) piv;

See SQL Fiddle with Demo.

If you have a unknown number of period values that you want to transform into column, then you will have to use dynamic SQL to get the result:

DECLARE @cols AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(period) 
                    from Sales
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT distinct ', IsNull(' + QUOTENAME(period) + ', 0) as '+ QUOTENAME(period) 
                    from Sales
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT id,
                name,
                HouseNum,
                Street,
                City,
                State,' + @colsNull + ' ,
                MinSales,
                MaxSales,
                AvgSales,
                TotalSales
             from 
             (
               select c.id,
                c.name,
                a.HouseNum,
                a.Street,
                a.city,
                a.state,
                s.period,
                s.sales,
                min(s.sales) over(partition by c.id) MinSales,
                max(s.sales) over(partition by c.id) MaxSales,
                avg(s.sales) over(partition by c.id) AvgSales,
                sum(s.sales) over(partition by c.id) TotalSales
              from customer c
              inner join address a
                on c.id = a.id
              inner join sales s
                on c.id = s.id
            ) x
            pivot 
            (
                sum(sales)
                for period in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo. These give the result:

| ID |       NAME | HOUSENUM |    STREET |    CITY |  STATE | 2012/02 | 2012/12 | 2013/01 | 2013/02 | MINSALES | MAXSALES | AVGSALES | TOTALSALES |
---------------------------------------------------------------------------------------------------------------------------------------------------
|  1 | Customer X |      100 | Maint St. |     ABC |    DEF |       0 |      20 |      50 |      45 |       20 |       50 |       38 |        115 |
|  2 | Customer Y |      108 |   Lost Rd | Unknown | Island |      10 |       0 |       0 |       0 |       10 |       10 |       10 |         10 |