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.
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 percentageexample 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 belowmaterial description IN TRANSIT HAULAGE PRODUCTION 101011 sample 100 0 0101112 example 0 50 0101113 mysample 0 0 100101014 other sample 20 0 0any help guysthanks |
|
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 tINNER JOIN maintenance mON m.material = t.materialGROUP BY m.material,m.description[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|