SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 COMPLEX QUERY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cyberdaemon
Starting Member

Philippines
1 Posts

Posted - 03/21/2013 :  05:03:40  Show Profile  Reply with Quote
Good day,

I am new to SQL, and i am using SQL server 2005, i am having problem with my table. actually i have 2 table 1 is for maitenance and the other is transsaction table..

on my maitenance table i have (id - PK,material,type,percentage)
and in transaction table i have (id-PK , material,description)

both table are related to each other by material field..
i'll back on the maitenance table.. on the type field. the data there is static, 'IN TRANSIT','HAULAGE','PRODUCTION' and each of the has percentage

example id material type percentage
1 101011 IN TRANSIT 100
2 101112 HAULAGE 50
3 101113 PRODUCTION 100
4 101014 IN TRANSIT 20

my problem is that how could i create an ouput like this below

material description IN TRANSIT HAULAGE PRODUCTION
101011 sample 100 0 0
101112 example 0 50 0
101113 mysample 0 0 100
101014 other sample 20 0 0

any help guys

thanks

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/21/2013 :  05:22:29  Show Profile  Reply with Quote

SELECT m.material,m.description,
SUM(CASE WHEN t.type ='IN TRANSIT' THEN t.percentage ELSE 0 END) AS [IN TRANSIT],
SUM(CASE WHEN t.type ='HAULAGE' THEN t.percentage ELSE 0 END) AS [HAULAGE],
SUM(CASE WHEN t.type ='PRODUCTION' THEN t.percentage ELSE 0 END) AS [PRODUCTION]
FROM transaction t
INNER JOIN maintenance m
ON m.material = t.material
GROUP BY m.material,m.description


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000