Pivot question 1

[xxing]

Hi I am new to Sql. Using Sql 2005. Is it possible to do this.

e.g

#Fruit
Name Fruit
Tom Apple
Tom Apple
Dick Orange
Dick Apple
Harry Orange
Harry Pear

#FruitCount
Name Apple Orange Pear
Tom 2 0 0
Dick 1 1 0
Harry 0 1 1

Kind of like Pivot in excel

Thank you
Mark

 

[bborissov]

Did you know beforehand how many fruits will be in the query?
If not you should build a dynamic pivoting.
If yes check PIVOT in BOL.

The query BEFORE PIVOT became available would look like this:

SELECT Name,
       SUM(CASE WHEN Fruit = 'Apple' 
                     THEN 1
                ELSE 0 END) AS  Apple,
       SUM(CASE WHEN Fruit = 'Orange' 
                     THEN 1
                ELSE 0 END) AS  Orange,
       SUM(CASE WHEN Fruit = 'Pear' 
                     THEN 1
                ELSE 0 END) AS  Pear
FROM #FruitCount
GROUP BY Name

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[xxing]

Thank you for your post bborisslov. Here is a star for you

 

[bborissov]

Here an example how to use PIVOT in SQL Server 2005, but unfortunately you should know what values you have in the field you use for pivoting:

DECLARE @Test TABLE (Name varchar(20), fruit varchar(40))
INSERT INTO @Test VALUES ('Tom','Apple')
INSERT INTO @Test VALUES ('Tom','Apple')
INSERT INTO @Test VALUES ('Dick','Orange')
INSERT INTO @Test VALUES ('Dick','Apple')
INSERT INTO @Test VALUES ('Harry','Orange')
INSERT INTO @Test VALUES ('Harry','Pear')



SELECT Name, Apple, Orange, Pear
FROM (SELECT Name, Fruit
             FROM @Test) Test
      PIVOT ( COUNT(FRUIT)
              FOR FRUIT IN ([Apple], [Orange], [Pear])) AS PivotTbl

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.