• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • 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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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