COMPLEX QUERY - 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

General SQL Server Forums

New to SQL Server Programming

COMPLEX QUERY

Author

Topic

cyberdaemon
Starting Member

1 Post

Posted - 2013-03-21 : 05:03:40

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

52326 Posts

Posted - 2013-03-21 : 05:22:29

[code]
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
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources