Cross Tab ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross Tab ???

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2009-11-23 : 02:07:59

Hi

I like to retrieve rows as cols . Any idea is appreciated, thanks.

Eg.
Table1
itemcode item
1 apple
2 orange

Table2
itemcode quantity
1 30
2 25
1 10

Result
apple orange

40 25

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-11-23 : 02:36:01

CREATE TABLE #Table1 (itemcode INT, item VARCHAR(25))
INSERT INTO #Table1
SELECT 1,'apple'
UNION ALL
SELECT 2 ,'orange'

GO

CREATE TABLE #Table2 (itemcode INT, quantity INT)
INSERT INTO #Table2
SELECT 1,30
UNION ALL
SELECT 2,25
UNION ALL
SELECT 1, 10

GO

SELECT [APPLE] ,[ORANGE] FROM
(SELECT item,quantity
FROM #Table1 INNER JOIN #Table2
ON #Table1.itemcode = #Table2.itemcode

) p
PIVOT
(SUM(quantity) FOR item IN ([APPLE],[ORANGE]))
AS pvt