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)
 Combing Tables into 1 table

Author  Topic 

paulmoss
Starting Member

14 Posts

Posted - 2012-01-27 : 09:54:24
Hi I am hoping that you will be able to help. I am currently trying to combine information from various sources into 1 view. Below is a sample of the Data I want to combine

warehouse product list price cost month
00 001AMIL 121.65 24.466 January
00 001AMIL 121.65 25.558 February
00 001AMIL 121.65 25.968 March
00 001AMIL 121.65 24.062 April
00 001AMIL 121.65 24.662 May
00 001AMIL 34.5 25.542 June
00 001AMIL 121.65 25.281 June
00 001AMIL 34.5 28.066 July
00 001AMIL 121.65 25.304 July

This data comes from several tables and I want to combine it into 1 table and get the information to dislay like this;

Wh Product JanPrice JanCost FebPrice FebCost MarchPrice MarchCost AprilPrice AprilCost MayPrice MayCost JunePrice JuneCost
00 001AMIL 121.65 24.466 121.65 25.558 121.65 25.968 121.65 24.062 121.65 24.662 34.5 25.542
00 001AMIL 121.65 24.466 121.65 25.558 121.65 25.968 121.65 24.062 121.65 24.662 121.65 25.281

I have tried a view using innerjoins but it dupplicates the line for every difference. Please can you point me in the right direction


Many thanks

Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 10:53:53
use PIVOT

SELECT m.warehouse,m.productlist,m.January AS JanPrice,n.January AS JanCost,
m.February AS FebPrice,n.February AS FebCost,...
FROM (select warehouse, productlist, January,February ,.. from table
pivot(price for month in (January,February,...))p
) m
INNER JOIN
(select warehouse, productlist, January,February ,.. from table
pivot(cost for month in (January,February,...))p
) n
ON n.warehouse = m.warehouse
AND n.productlist = m.productlist


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -