SQL ADP and Pivottables

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I have a fairly simple query (using SQL and ADP) that looks at an orders
view
and returns the folowing fields for all sales details:

Item
Quantity
Size

I have been trying to set the results onto a pivot table view so I can see a
table similar to the following:

-------------- Size 1--------Size 2--------Size 3--------Size 4
Item A 255 355 455 500
Item B 100 300 250 180
Item C 457 568 256 566
Item D 447 214 352 989

I am following the instruction to drag and drop the fields from the field
chooser into the pivottable view but I am getting the folloing problem:

The system is NOT allowing me to setup any of the three fields as the column
headers. I am used to using Excel where there are labeled column and row
sections to which fields can be dropped. When I try to do this now, it is
just dropping the fileds into the left column so that I cannot create any
column field. My data just looks like the original table:

Item A Size 1
Item A Size 2
Item A Size 3
Item B Size 1
Item B Size 2
Item B Size 3
Item C Size 1
Item C Size 2
Item C Size 3

I have tried to "squeeze" the field that is dragged into the upper-most edge
of teh table, but it does not recognize any column options, nor does it
allow me to sub-categorize the rows. It just gives me the list as show
above. In the "chooser" field list, there is an option at the bottom which
specifies to add the field to a column, or row, or data.... When I choose
column or row, the "add" button is greyed out and will not let me add the
field.

Can someone lend me some assitance?

-Stephen
 
If you know in advance what the columns should be, you can just do a manual
pivot as follows:

SELECT [Item],
SUM(CASE WHEN [Size]='Size 1' THEN [QUANTITY]
ELSE 0 END
) As [Size 1],
SUM(CASE WHEN [Size]='Size 2' THEN [QUANTITY]
ELSE 0 END
) As [Size 2],
SUM(CASE WHEN [Size]='Size 3' THEN [QUANTITY]
ELSE 0 END
) As [Size 3],
SUM(CASE WHEN [Size]='Size 4' THEN [QUANTITY]
ELSE 0 END
) As [Size 4]
FROM [SalesDetails]
GROUP BY [Item]
ORDER BY [Item]
 
Steve -

Your solution worked. Thank you. It was not how I wanted to make it work
because it limits the number of different sizes we can use, however if there
is no method for using the pivotable then this will have to do.

Any thoughts on setting up the pivot table?

-Stephen

Steve Jorgensen said:
If you know in advance what the columns should be, you can just do a
manual
pivot as follows:

SELECT [Item],
SUM(CASE WHEN [Size]='Size 1' THEN [QUANTITY]
ELSE 0 END
) As [Size 1],
SUM(CASE WHEN [Size]='Size 2' THEN [QUANTITY]
ELSE 0 END
) As [Size 2],
SUM(CASE WHEN [Size]='Size 3' THEN [QUANTITY]
ELSE 0 END
) As [Size 3],
SUM(CASE WHEN [Size]='Size 4' THEN [QUANTITY]
ELSE 0 END
) As [Size 4]
FROM [SalesDetails]
GROUP BY [Item]
ORDER BY [Item]

I have a fairly simple query (using SQL and ADP) that looks at an orders
view
and returns the folowing fields for all sales details:

Item
Quantity
Size

I have been trying to set the results onto a pivot table view so I can see
a
table similar to the following:

-------------- Size 1--------Size 2--------Size 3--------Size 4
Item A 255 355 455 500
Item B 100 300 250 180
Item C 457 568 256 566
Item D 447 214 352 989

I am following the instruction to drag and drop the fields from the field
chooser into the pivottable view but I am getting the folloing problem:

The system is NOT allowing me to setup any of the three fields as the
column
headers. I am used to using Excel where there are labeled column and row
sections to which fields can be dropped. When I try to do this now, it is
just dropping the fileds into the left column so that I cannot create any
column field. My data just looks like the original table:

Item A Size 1
Item A Size 2
Item A Size 3
Item B Size 1
Item B Size 2
Item B Size 3
Item C Size 1
Item C Size 2
Item C Size 3

I have tried to "squeeze" the field that is dragged into the upper-most
edge
of teh table, but it does not recognize any column options, nor does it
allow me to sub-categorize the rows. It just gives me the list as show
above. In the "chooser" field list, there is an option at the bottom
which
specifies to add the field to a column, or row, or data.... When I choose
column or row, the "add" button is greyed out and will not let me add the
field.

Can someone lend me some assitance?

-Stephen
Click to expand...
Click to expand...
 
Go out to rac4SQL.net. A great product we use here at the City of Mesa
Az to handle our crosstabs
 
Back
Top