How Can I Order in an SQL Statement?

Question

I have these tables: Stock, Unit, Location, Category, StockBalance

At StockBalance: there is StockID from Stock, UnitId from Unit, LocationID from Location

I save at StockBalance Table like following

StockBalanceID | StockID | UnitID | LocationID | BalanceQuantity
1              | 1       | 1      | 1          | 20      
2              | 1       | 2      | 1          | 30    
3              | 1       | 3      | 1          | 40    
4              | 2       | 1      | 2          | 20      
5              | 2       | 2      | 2          | 30    
6              | 2       | 3      | 2          | 40

I would like to show on Classic ASP as :

Group By : CategoryName

  Stock Name                    Quantity                             Location Name    
                       Qty | Unit   | Qty | Unit | Qty | Unit         
  Stock One |          20  |  One   |  30 | Two  | 40  | Three       | Location One  
  Stock Two |          20  |  One   |  30 | Two  | 40  | Three       | Location Two  

How can I Select From StockBalance to get like above? How about my edit one?

Please help me !

Answer 1

PIVOT is what you want.

First some sample data setup:

create table yourTable (
StockBalanceID int, StockID int, UnitID int, LocationID int, BalanceQuantity int);

insert yourTable
select  1              , 1       , 1      , 1          , 20      
union all select  2              , 1       , 2      , 1          , 30    
union all select  3              , 1       , 3      , 1          , 40    
union all select  4              , 2       , 1      , 2          , 20      
union all select  5              , 2       , 2      , 2          , 30    
union all select  6              , 2       , 3      , 2          , 40
;

Now to do the work...

select StockID, LocationID,
    sum(case UnitID when 1 then BalanceQuantity end) as [Unit One],
    sum(case UnitID when 2 then BalanceQuantity end) as [Unit Two],
    sum(case UnitID when 3 then BalanceQuantity end) as [Unit Three]
from yourTable
group by StockID, LocationID;

Answer 2

Try the following

DECLARE @StockBalances TABLE(
        StockBalanceID INT,
        StockID INT,
        UnitID INT,
        LocationID INT,
        BalanceQuantity FLOAT
)

DECLARE @Stock TABLE(
        StockID INT,
        StockName VARCHAR(10)
)

DECLARE @Unit TABLE(
        UnitID INT,
        UnitName VARCHAR(10)
)

DECLARE @Location TABLE(
        LocationID INT,
        LocationName VARCHAR(10)
)

INSERT INTO @StockBalances SELECT 1,1,1,1,20       
INSERT INTO @StockBalances SELECT 2,1,2,1,30     
INSERT INTO @StockBalances SELECT 3,1,3,1,40     
INSERT INTO @StockBalances SELECT 4,2,1,2,20       
INSERT INTO @StockBalances SELECT 5,2,2,2 ,30     
INSERT INTO @StockBalances SELECT 6,2,3,2,40 

INSERT INTO @Stock SELECT 1, 'Stock 1'
INSERT INTO @Stock SELECT 2, 'Stock 2'

INSERT INTO @Unit SELECT 1, 'Unit 1'
INSERT INTO @Unit SELECT 2, 'Unit 2'
INSERT INTO @Unit SELECT 3, 'Unit 3'

INSERT INTO @Location SELECT 1, 'Location 1'
INSERT INTO @Location SELECT 2, 'Location 2'


SELECT  *
FROM    (
            SELECT  s.StockName,
                    sb.BalanceQuantity,
                    u.UnitName,
                    l.LocationName
            FROM    @StockBalances sb INNER JOIN
                    @Stock s ON sb.StockID = s.StockID INNER JOIN
                    @Unit u ON sb.UnitID = u.UnitID INNER JOIN
                    @Location l ON sb.LocationID = l.LocationID
        ) t
PIVOT   (SUM(BalanceQuantity) FOR UnitName IN ([Unit 1], [Unit 2], [Unit 3])) p