asked on

MS SQL Server Query

Is there any way to create a query that would return data in the following format:

Category Name Desc Purchase Price
----------------------------------------------------------------------------------
Furniture
Table Wood table 299.99
Chair High back 99.99
Desk Long Desk 199.99
Total (3) 599.97

IT
Laptop HP Laptop 999.99
Mouse Optical 9.99
Total (2) 1009.98
------------------------------------------------------------------------------------

Lee

Difficult to help as you haven't given any indication of the table structure where the data is held.

To answer your question, this looks like a grouping will be required and it will not give you this output in this way. It looks like you need a report to do this where you can group and display data in a formatted way.

koossa

ASKER

Original Data is in the following format:

Name Desc Category Purchase Price
-------------------------------------------------------------------------

ASKER CERTIFIED SOLUTION

exoduster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

exoduster

Here you have an example which should help:
;WITH cte as (
      SELECT 'table' id, 'furniture' c, 1000 value
      UNION
      SELECT 'chair' id, 'furniture' c, 2000 value
      UNION
      SELECT 'table' id, 'furniture' c, 3000 value
      UNION
      SELECT 'chair' id, 'furniture' c, 4000 value
      UNION
      SELECT 'table' id, 'furniture' c, 5000 value
      UNION
      SELECT 'laptop' id, 'IT' c, 6000 value
      UNION
      SELECT 'mouse' id, 'IT' c, 7000 value
)
SELECT id, c, SUM(value)--, GROUPING(c)
FROM cte
GROUP BY id, c
WITH CUBE
HAVING GROUPING(c) = 0
ORDER BY c, id DESC

Lee Wadwell

Formatting should really be done in the presentation layer (reporting tool, code etc) ... SQL was not really designed for it. But here is my stab:

;with cte as (
select 'Furniture' as Category, 'Table'  as Name, 'Wood table' as Descr, 299.99 as Price union all
select 'Furniture' as Category, 'Chair'  as Name, 'High back'  as Descr,  99.99 as Price union all
select 'Furniture' as Category, 'Desk'   as Name, 'Long Desk'  as Descr, 199.99 as Price union all  
select 'IT'        as Category, 'Laptop' as Name, 'HP Laptop'  as Descr, 999.99 as Price union all
select 'IT'        as Category, 'Mouse'  as Name, 'Optical'    as Descr,   9.99 as Price
)
select case when rn = 1 then Category 
            when g2 = 1 then 'Total ('+convert(varchar,cnt)+')'
            else null end as Category
     , Name, Descr, tot_price
  from (select Category, Name, Descr, SUM(Price) tot_price, count(*) as cnt
             , grouping(Category) g1, grouping(Name) g2, grouping(Descr) g3
             , row_number()over(partition by Category order by ISNULL(Name,CHAR(255)), Descr) rn
          from cte
         group by Category, Name, Descr WITH ROLLUP
        having grouping(Category) = 1 or grouping(Name) = 1 or grouping(Descr) = 0)v
 where v.Category is not null
 order by v.Category, rn

Open in new window

SOLUTION

koossa

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

koossa

ASKER

Thank you!