Pivot more than one column

Question

i have two tables in SQL Server 2008, in one i have the needs of articles per week, and in another i have the stock, somethin like

NEEDS:

Article                      Week                           Need

--------------------------------------------------------------------

aa                            1                                25
aa                            2                                13
aa                            4                                33
aa                            6                                21
aa                            25                               40
ab                            1                                 1
ab                            2                                 3
ab                            16                               14
ab                            50                               50

STOCKS

  Article                       Units
---------------------------------------------------------------------------
    aa                            80    
    ab                            14
    ac                             8

and i need to show something like:

 Article       WEEK1          WEEK2       WEEK4         WEEK6        WEEK16       WEEK25   
            needs stock   needs stock  needs stock   needs stock   needs stock  needs stock


 aa           25    80      13   55      42    22      21    20      0   -1        40  -1
 ab            1    14       3   13       0    10        0   10     14  10          0  -4

This will show in Encel, for this i use C# and T-SQL, my problem is that if use PIVOT i can oly do it with the needs per week but i expect the needs and the stock, if i do without the pivot i can obtain the article the week an the stock, save the data in objects and do the calculation of the stock per week and then format the Excel, but i have one problem i need in the object something like:

puclic class needsPerWeek
{
string Article{get;set;}
int needsWeek1{get;set;}
int stockWeek1{get;set;}
int needsWeek2{get;set;}
int stockWeek2{get;set;}
int needsWeek{get;set;}
int stockWeek4{get;set;}
int needsWeek6{get;set;}
int stockWeek6{get;set;}
int needsWeek16{get;set;}
int stockWeek16{get;set;}
int needsWeek25{get;set;}
int stockWeek25{get;set;}
int needsWeek50{get;set;}
int stockWeek50{get;set;}
....
....
}

but i don´t know how much weeks i will recibe.

So, can i do this with pivot?, or any other way to obtain this?

What i need is to export the data to a Excel file, but to do this i supose(I alwais do it like this) previously i need a list and then construct the Excel and load data from the list. — Ion, Feb 11 '16 at 11:39
Yes, it´s not very clear, excuse me, i pretend to obtain the stock from the stocks table, and put in the first column, the next week i do the stock - needs from the previous week, so fro the 'aa' article in the week 1 i put the stock from the tha table, for the 'aa' article in the week 2 what i want is to take the stock and rest the needs from the week 1(stock week2=80(stock week 1) - 25(needs week 1). But i supose i can´t do it this in sql, if you explain me how to obtain something like i put it but showing always the stock from the stocks table for all weeks i can modify later in c#. — Ion, Feb 11 '16 at 12:36

score 1 · Accepted Answer · edited May 23 '17 at 12:15

Just copy and paste the code below and do some changes according to your need. We are just making dynamic column strings (UnitsWeek1, UnitsWeek2, NeedsWeek1... etc). And get two tables using Pivot and obtain a final table by joining the two.

DECLARE @cols1 AS NVARCHAR(MAX),
   @query1  AS NVARCHAR(MAX),
   @cols2 AS NVARCHAR(MAX),
   @query2  AS NVARCHAR(MAX);

select @cols1 = STUFF((SELECT distinct ',' + QUOTENAME('NeedsWeek'+Week) 
                   from NeedsMaster FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)') 
       ,1,1,'')

set @query1 = 'SELECT Article, ' + @cols1 + ' from 
            (
                select Article
                       , Need
                       ,''NeedsWeek''+Week as Week 
                       from
                   NeedsMaster 
           ) x
           pivot 
           (
               min(need)
               for Week in (' + @cols1 + ')
           ) p '

select @cols2 = STUFF((SELECT distinct ',' + QUOTENAME('UnitsWeek'+n.Week) 
                   from NeedsMaster n left join StocksMaster s
on s.Article=n.Article
           FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)') 
       ,1,1,'')

set @query2 = 'Select * from (SELECT Article, ' + @cols2 + ' from 
            (
                select n.Article
                       , s.Units
                       ,''UnitsWeek''+n.Week as Week

                   from StocksMaster s
                   inner join NeedsMaster n
                      on s.Article=n.Article
           ) x1
           pivot 
           (
               min(Units)
               for Week in (' + @cols2 + ')
           ) p1 ) a inner join 
           (SELECT Article, ' + @cols1 + ' from 
            (
                select Article

                       , Need
                      ,''NeedsWeek''+Week as Week 
                       from
                   NeedsMaster 
           ) x2
           pivot 
           (
               min(need)
               for Week in (' + @cols1 + ')
           ) p2 ) b on a.Article=b.Article'

execute(@query2)

(@query1 is just for better understanding purpose). Give it a try and i hope you will edit it as your requirements.

Reference

Thanks, Works fine. Now i will see how to retrive in C# to do same calculations — Ion, Feb 11 '16 at 15:31

Pivot more than one column

1 Answers1