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 |
mayoorsubbu
Yak Posting Veteran
95 Posts |
Posted - 2014-01-24 : 22:30:18
|
Hi All,I have three table in sql server 2000 like sotbDemandDemandNo varcharUnit varchartbDemandItemsDemandNo varcharItemcode intDemandQtyData in tablestbDemandDemandNo Unit2014ABC RST2014CDF XYZtbDemandItemsDemandNo Itemcode DemandQty2014ABC 1234 52014ABC 4567 102014ABC 7890 112014CDF 1234 102014CDF 7890 5I want a report as followsItemcode RST XYZ TotDemandQty 1234 5 10 154567 10 0 107890 11 5 16The pivot on unit has to be dynamic as per no of units, which is variable and not fixed in tbDemand table.Thanks. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-27 : 06:54:28
|
[code]SELECT di1.Itemcode,SUM(CASE WHEN Unit='RST' THEN DemandQty ELSE 0 END) AS RST,SUM(CASE WHEN Unit='XYZ' THEN DemandQty ELSE 0 END) AS XYZ,SUM(DemandQty) AS TptDemandQtyFROM tbDemand dCROSS JOIN (SELECT DISTINCT Itemcode FROM tbDemandItems) di1LEFT JOIN tbDemadnItems diON di.DemandNo = d.DemandNoAND di.Itemcode = di1.ItemcodeGROUP BY di1.Itemcode[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mayoorsubbu
Yak Posting Veteran
95 Posts |
Posted - 2014-01-27 : 09:45:04
|
Visakh,Thank u for the reply, but the case statement cannot be hard coded as the unit names will vary(CASE WHEN Unit='RST' THEN DemandQty ELSE 0 END)unit will vary and cannot be predicted before hand. The unit will have be selected from the tbDemand table and then the case statement constructed. Hope I am clear. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
mayoorsubbu
Yak Posting Veteran
95 Posts |
Posted - 2014-01-27 : 19:37:57
|
Thanks will comply |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-28 : 03:03:10
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|