• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

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
------------------------------------------------------------------------------------
0
koossa
Asked:
koossa
2 Solutions
 
Lee SavidgeCommented:
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.
0
 
koossaAuthor Commented:
Original Data is in the following format:


Name     Desc     Category     Purchase Price
-------------------------------------------------------------------------
0
 
exodusterCommented:
Of course to get total, you could use GROUP BY WITH ROLLUP.
Smth like that:
SELECT name, desc, SUM(Purchase Price)
FROM Furniture
GROUP BY name, desc
WITH ROLLUP

but it will give you only furnitures.

Probably you really need a report. And it looks like a report :)
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
exodusterCommented:
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
0
 
lwadwellCommented:
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

0
 
koossaAuthor Commented:
It seems like this will do the trick (See attached screenshot):
SELECT     Category, [My Group], Name, [Desc], [Purchase Price]
FROM         (SELECT     0 AS MySort, Category, '' AS [My Group], Name, [Desc], [Purchase Price]
                       FROM          test
                       UNION
                       SELECT     1 AS MySort, Category, 'Total (' + CAST(COUNT(*) AS varchar(10)) + ')' AS [My Group], '' AS Expr2, '' AS Expr3, SUM([Purchase Price]) 
                                             AS Expr4
                       FROM         test AS test_1
                       GROUP BY Category
                       UNION
                       SELECT     - 1 AS MySort, Category, Category AS [My Group], '' AS Expr2, '' AS Expr3, NULL AS Expr4
                       FROM         test AS test_1
                       GROUP BY Category) AS Temp
ORDER BY Category, MySort

Open in new window

untitled.PNG
0
 
koossaAuthor Commented:
Thank you!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now