Changing Display structure - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Changing Display structure

Author

Topic

mike1973
Starting Member

46 Posts

Posted - 2010-05-07 : 03:52:09

hey guys,
i have this query that contains 2 subs, is it possible to change it to display the types as columns and not in rows? I mean using the following structure

CategoryID,CategoryAR,CategoryEN,TotalSalesWeight,TotalSalesPrice,TotalSalesPriceUSD,TotalPurchaseWeight,TotalPurchasePrice,TotalPurchasePriceUSD

Select Distinct(SP.CategoryAutoID),Categories.CategoryAR, Categories.CategoryEN, SUM(SP.TotalWeight) As TotalWeight,
SUM(SP.TotalWeight * SP.UnitPrice) AS SoldAmount,
CONVERT(Decimal(18,2),SUM(SP.TotalWeight * SP.UnitPrice/ex1.value)*1000) AS SoldAmountUSD, 'Sales' AS Type
From SalesProducts AS SP
JOIN Categories ON Categories.CategoryAutoID=SP.CategoryAutoID
LEFT JOIN Exchange As ex1 ON ex1.DateExchange=SP.DateSold
Where DateSold BETWEEN CONVERT(DATETIME,@StartDate,101) + ' 12:00:00 AM' AND CONVERT(DATETIME,@EndDate,101) + '11:59:59 PM'
Group By SP.CategoryAutoID, Categories.CategoryAR,Categories.CategoryEN

UNION

Select Distinct(DO.CategoryID),Categories.CategoryAR, Categories.CategoryEN, SUM(DO.Weight) As TotalWeight,
SUM(DO.Weight * DO.UnitPrice) AS SoldAmount,
CONVERT(Decimal(18,2),SUM(DO.Weight * DO.UnitPrice/ex1.value)*1000) AS SoldAmountUSD, 'Purchases' AS Type
From DailyOperations AS DO
JOIN Categories ON Categories.CategoryAutoID=DO.CategoryID
LEFT JOIN Exchange As ex1 ON ex1.DateExchange=DO.DateOperated
Where DateOperated BETWEEN CONVERT(DATETIME,@StartDate,101) + ' 12:00:00 AM' AND CONVERT(DATETIME,@EndDate,101) + '11:59:59 PM'
Group By DO.CategoryID, Categories.CategoryAR,Categories.CategoryEN
ORDER BY Categories.CategoryEN

Thanks a lot for your help
Mike

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-07 : 06:57:02

You must do this from you front end application because backend is concern with the data not the display.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

mike1973
Starting Member

46 Posts

Posted - 2010-05-07 : 07:03:40

How i can do this? i tried but not able to bind it to a grid

Sachin.Nand

2937 Posts

Posted - 2010-05-07 : 07:17:26

But what sholud be the data under the columns.
From what I see there is just 'Sales' and 'Purchases' under the column Type.

PBUH

mike1973
Starting Member

46 Posts

Posted - 2010-05-07 : 09:17:28

here is my target format:

Catergory SalesTotalWeight SalesTotalPrice SalesTotalPriceUSD PurchaseTotalWeight SalesTotalPrice SalesTotalPriceUSD

Category1 - - - - - -
Category2 - - - - - -
Category3 - - - - - -
Category4 - - - - - -
Category5 - - - - - -
Category6 - - - - - -
Category7 - - - - - -
Category8 - - - - - -

Sachin.Nand

2937 Posts

Posted - 2010-05-07 : 09:32:32

Well you will have to use something like this


select 
   sum(case when Type='Sales' then TotalWeight end) as PurchaseTotalWeight, 
   sum(case when Type='Purchase' then TotalWeight end) as PurchaseTotalWeight 
  and so on
from
(
Your original query
)T

PBUH

mike1973
Starting Member

46 Posts

Posted - 2010-05-08 : 03:50:51

Using the following query

select
case when Type='Purchases' then CATID end as CATID,
case when Type='Purchases' then CAT end as CAT,
case when Type='Purchases' then ORDERCAT end as ORDERCAT,
case when Type='Purchases' then TotalWeight end as PurchaseTotalWeight, 
case when Type='Purchases' then SoldAmountUSD end as PurchaseAmountUSD, 
case when Type='Purchases' then SoldAmount end as PurchaseAmount, 

case when Type='Sales' then CATID end as CATID,
case when Type='Sales' then CAT end as CAT, 
case when Type='Sales' then ORDERCAT end as ORDERCAT,
case when Type='Sales' then TotalWeight end as SalesTotalWeight, 
case when Type='Sales' then SoldAmount end as SalesAmount, 
case when Type='Sales' then SoldAmountUSD end as SalesAmountUSD
--case when Type='Sales' then CAT end as CAT,
from
( Select Distinct(SP.CategoryAutoID) AS CATID,Categories.CategoryAR AS CAT, Categories.CategoryEN AS ORDERCAT, SUM(SP.TotalWeight) As TotalWeight,
SUM(SP.TotalWeight * SP.UnitPrice) AS SoldAmount, 
CONVERT(Decimal(18,2),SUM(SP.TotalWeight * SP.UnitPrice/ex1.value)*1000) AS SoldAmountUSD, 'Sales' AS Type
From SalesProducts AS SP
JOIN Categories ON Categories.CategoryAutoID=SP.CategoryAutoID
LEFT JOIN Exchange As ex1 ON ex1.DateExchange=SP.DateSold
Where DateSold BETWEEN CONVERT(DATETIME,@StartDate,101) + ' 12:00:00 AM' AND CONVERT(DATETIME,@EndDate,101) + '11:59:59 PM'
Group By SP.CategoryAutoID, Categories.CategoryAR,Categories.CategoryEN 
UNION
Select Distinct(DO.CategoryID) AS CATID,Categories.CategoryAR AS CAT, Categories.CategoryEN AS ORDERCAT, SUM(DO.Weight) As TotalWeight,
SUM(DO.Weight * DO.UnitPrice) AS SoldAmount, 
CONVERT(Decimal(18,2),SUM(DO.Weight * DO.UnitPrice/ex1.value)*1000) AS SoldAmountUSD, 'Purchases' AS Type
From DailyOperations AS DO
JOIN Categories ON Categories.CategoryAutoID=DO.CategoryID
LEFT JOIN Exchange As ex1 ON ex1.DateExchange=DO.DateOperated
Where DateOperated BETWEEN CONVERT(DATETIME,@StartDate,101) + ' 12:00:00 AM' AND CONVERT(DATETIME,@EndDate,101) + '11:59:59 PM'
Group By DO.CategoryID, Categories.CategoryAR,Categories.CategoryEN
)T

i got the following results

mike1973
Starting Member

46 Posts

Posted - 2010-05-09 : 06:06:39

Anyone can help with this one please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources