Invert rows and columns (PIVOT)

Hi everybody.

I have this table that contains how many items were sold (and their value) into two departments:

SellerID(PK)	Year(PK)	ItemsSoldDPT1	ItemsSoldDPT2	ValueSoldDPT1	ValueSoldDPT2
1	2002	10	20	300.00	400.00
1	2003	13	71	450.00	320.00
1	2004	8	4	350.00	640.00
1	2005	2	15	110.00	680.00
2	2001	3	1	130.00	100.00
2	2005	1	7	190.00	200.00
2	2006	6	9	170.00	500.00
...	...	...	...	...	...

I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..

I want to obtain this:

SellerID	2001 Items	2001 Values	2002 Items	2002 Values	2003 Items	2003 Values	2004 Items	2004 Values	2005 Items	2005 Values	2006 Items	2006 Values	...
1	(NULL)	(NULL)	30	700.00	84	770.00	12	990.00	17	790.00	(NULL)	(NULL)	...
2	4	230.00	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	8	390.00	15	670.00	...
...	...	..	...	...	...	...	...	...	...	...	...	...	...

Any ideas? I think I should use the PIVOT keyword to write the sql but I can't figure how it works and how can I do that sums.

Please note: I don't know how many distinct values of "Year" exists and the min and max year can be specified by the user.

Thank you for your help.

It is best to use the standard SQL approach of using GROUP BY and CASE expressions. Pivot can only be used to pivot one set of values. You have to write more complex query if you want to just use PIVOT operator and performance will be bad for those approaches.

select t.SellerID

, sum(case t.Year when 2001 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2001 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2002 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2002_Values

, sum(case t.Year when 2002 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2002_Values

...

from tbl as t

group by t.SellerID

|||Thank you for your interest and your time.
I already thought at this solution (it was the first approach I used) but this means that I must build a query that contains a couple of "sum(...) as ..." for each year I want to consider.. and I know neither how many different years are contained in the table nor how big is the date interval..
Moreover I discarted this approach because I can only replace the initial and final year value in the SQL query (something like a "tag replace"). We use a particular reporting tecnology that accepts queries at design time and replaces variables at runtime (with typed parameters).

So I have to write something like
SELECT.... xxx...xxx..xxx
WHERE...
and Year>=[%shortInitialYear] and Year<=[%shortFinalYear]

Even if I could use the PIVOT keyword I have a similar problem because the pivot keyword assumes that the user has to specify each value to pivot "IN ([value1], [value2], [value3])" and not a range (something like "BETWEEN [%shortInitialYear] and [%shortFinalYear]" )|||

hi

i understand exactly what you mean.

here is a short description on what i did to solve the problem, i apologize for not having the time to go into more detail but you'll figure this out quickly

first do a query where you would get the distinct date information with the amounts (sum it if you want), in a view would be best

then when you have got the distinct dates create a new temporary table, dynamically of course. i created the tabel and in a cursor added the columns (which was a result from the view)

then insert into your dynamic table the values into columns corresponding to the view you have created.

this whole approach works if your dynamic sql logic is sound, best is that you don't have to specify any thing other than what you want in the original view.

hope this helps

|||

It is a known annoyance :) You can use pivot by flattening out the set: http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!758.entry but like Umachandar said, the "classic" style is easier/faster. If you want to help change this, vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127071

The easiest thing to do is to just make your SUM clauses dynamic:

create table year
(
year char(4) primary key
)
insert into year
select 2001
union all
select 2002
go
declare @.query varchar(8000)
select @.query = 'select t.SellerID ' + char(10) + (
SELECT distinct
', sum(case t.Year when ''' + year + ''' then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as ' + year + '_Values' + char(10) +
', sum(case t.Year when ''' + year + ''' then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as ' + year + '_Values' + char(10)
AS [text()]
FROM
year y
FOR XML PATH('') ) + char(10) + ' from tbl as t group by t.SellerID'

select @.query

I used 2005 syntax to build the sum columns, since I think you have 2005, in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=488720&SiteID=1 there is another full example.

sql