SQL query for displaying product sales.

Question

I have sales table which consists, ItemSize, GroupName, Quantity, ProductID, etc...

Now I want to display sales according to following format

GroupName   ItemSize  Quantity   ItemSize  Quantity

means

BEER    350ml   500    650ml   1000 

How I can achieve this in SQL SERVER 2005 EXPRESS (T-SQL)? Thanks

UPDATED:

its my sales table structure

CREATE TABLE [dbo].[SalesLog](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[ProductCode] [int] NULL,
[Quantity] [int] NULL,
[Price] [int] NULL,
[ProductGroup] [int] NULL,
    CONSTRAINT [PK_SalesLog] PRIMARY KEY CLUSTERED 
    (
        [SalesID] ASC
    ) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

its my Product table structure

CREATE TABLE [dbo].[Products](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[pName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pSize] [int] NULL,
[pPrice] [int] NULL,
[pPackQty] [int] NULL,
[pGroup] [int] NULL,
[pCode] [int] NULL,
[pStock] [int] NULL,
[pYrStock] [int] NULL,
[pClearStock] [int] NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
   (
   [ProductId] ASC
   ) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

It looks like you are trying to do a PIVOT table, where the different item sizes are represented as different columns rather than rows. Take a look at http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 2

You could aggregate the data using SUM and CASE statements.

Using your table definitions (and some very minimal made up data), here is an example of how you could do it:

--** Create test tables
DECLARE @SalesLog TABLE ( 
SalesID int IDENTITY(1,1) NOT NULL, 
MemoNo int NULL, 
ProductCode int NULL, 
Quantity int NULL, 
Price int NULL, 
ProductGroup int NULL)

DECLARE @Products TABLE( 
ProductId int IDENTITY(1,1) NOT NULL, 
pName nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
pSize int NULL, 
pPrice int NULL, 
pPackQty int NULL, 
pGroup int NULL, 
pCode int NULL, 
pStock int NULL, 
pYrStock int NULL, 
pClearStock int NULL)

--** Setup test data
INSERT INTO @SalesLog ( MemoNo, ProductCode, Quantity, Price, ProductGroup)
SELECT   0, 1, 500, 0, 1 UNION
SELECT   0, 2, 700, 0, 1 UNION   
SELECT   0, 2, 333, 0, 1 UNION   
SELECT   0, 3, 200, 0, 2 UNION
SELECT   0, 4, 125, 0, 2 ;

INSERT INTO @Products (pName, pSize, pPrice, pPackQty, pGroup, pCode, pStock, pYrStock, pClearStock)
SELECT 'Beer', 350, 1 , 1, 1, 1, 0, 0, 0 UNION
SELECT 'Beer', 650, 1 , 1, 1, 2, 0, 0, 0 UNION
SELECT 'Beer', 1000, 1 , 1, 1, 3, 0, 0, 0 UNION
SELECT 'Wine', 750, 1 , 1, 2, 4, 0, 0, 0 UNION 
SELECT 'Wine', 1000, 1 , 1, 2, 5, 0, 0, 0 ;

--** Example query
SELECT t.pName AS 'Product'
     , MAX(CASE WHEN t.Col = 1 THEN t.pSize END) AS 'Item Size'
     , ISNULL(SUM(CASE WHEN t.Col = 1 THEN t.Quantity END),0) AS 'Quantity'
     , MAX(CASE WHEN t.Col = 2 THEN t.pSize END) AS 'Item Size'
     , ISNULL(SUM(CASE WHEN t.Col = 2 THEN t.Quantity END),0) AS 'Quantity'
     , MAX(CASE WHEN t.Col = 3 THEN t.pSize END) AS 'Item Size'
     , ISNULL(SUM(CASE WHEN t.Col = 3 THEN t.Quantity END),0) AS 'Quantity'
FROM (
SELECT pName
     , pCode
     , pGroup
     , pSize
     , sl.Quantity
     , DENSE_RANK() OVER(PARTITION BY p.pGroup ORDER BY p.pSize) AS Col
  FROM @Products AS p
  LEFT JOIN @SalesLog AS sl
    ON p.pGroup = sl.ProductGroup
   AND p.pCode = sl.ProductCode
     ) AS t
 GROUP BY t.pGroup
     , t.pName
;   

The query uses the DENSE_RANK function to group items of a size together and to order them in assending order of size and this is used to work out which column the data should be written to.

Although there is a PIVOT operator in SQL Server 2005 and above, it isn't very helpful when you have different column heading types (item size and quantity in this case).

You will have to decide on the maximum number of product sizes that you want to report on as this is hard coded into the query. So if the maximum number of product sizes is 3 then you code the query as shown above. If, however, one of your products has 4 different sizes, then you are going to add an additional Item Size and Quantity pair of columns for t.Col = 4 and so on.

I hope this helps.