SQL Query help

[picazo]

Hi, I have tables:

Fruits:
FruitID | FruitName

1 | Apple
2 | Orange
3 | Pear

Baskets:
Basket | FruitID | Amount

Bask1 | 1 | 3
Bask2 | 1 | 5
Bask3 | 1 | 2
Bask1 | 1 | 1
Bask2 | 1 | 2
Bask1 | 3 | 2
Bask3 | 3 | 4

What query can I use to get the following table:

Basket | Apple | Orange| Pear

Bask1 | 3 | 1 | 2
Bask2 | 5 | 2 | NULL
Bask3 | 2 | NULL | 4

Thanks in advance,

----------------- Genaro

[albCode]

select fruits.*,baskets.* from fruits,baskets where fruits.FruitID = Baskets.FruitID

[Khawar Abbas1]

SELECT b.Basket AS Basket, b.Amount AS APPLE FROM Fruits INNER JOIN Baskets b ON Fruits.FruitID = b.FruitID WHERE (Fruits.FruitID = 1) SELECT c.Basket AS Basket, c.Amount AS ORANGE FROM Fruits INNER JOIN Baskets c ON Fruits.FruitID = c.FruitID WHERE (Fruits.FruitID = 2) SELECT d.Basket AS Basket, d.Amount AS PEAR FROM Fruits INNER JOIN Baskets d ON Fruits.FruitID = d.FruitID WHERE (Fruits.FruitID = 3) This will not give u the exact solution but will give you the partial solution.

Do good and have good.

[i j russell]

This is the standard way of doing a Crosstab/PIVOT in SQL. SELECT Basket, SUM(CASE FruitID WHEN 1 THEN Amount ELSE 0 END) AS Apple, SUM(CASE FruitID WHEN 2 THEN Amount ELSE 0 END) AS Orange, SUM(CASE FruitID WHEN 3 THEN Amount ELSE 0 END) AS Pear FROM Baskets GROUP BY Basket However, as you can see, you need to know in advance what the columns are. There is a new PIVOT command in SQL Server 2005 that may extend this functionality. The other way is to de-normalise the data using SELECT b.basket, f.FruitName, b.Amount FROM Baskets b INNER JOIN Fruit f on b.FruitID = f.FruitID and import it into Excel where you can use the Pivot tool to do the crosstabbing for you. Ian

[Eric Dahlvang]

You could make a Stored Procedure that does this:

DECLARE @iColumns INT, @sql VARCHAR(2500),@cFruitName VARCHAR(10),@cFruitID varchar(10)

SET @sql = ''

DECLARE curFruits CURSOR FOR SELECT DISTINCT FruitID,FruitName FROM Fruits
OPEN curFruits
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', SUM(CASE FruitID WHEN ' + @cFruitID + ' THEN Amount ELSE 0 END) as [' + @cFruitName + ']'
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
END

close curFruits
DEALLOCATE curFruits

SET @sql = 'SELECT Basket' + @sql + ' FROM Baskets GROUP BY Basket'
print @sql
exec(@sql)

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters