Sql group rows with same value, and put that value into header?

Question

I want to group rows with SQL, my result set is following

name  size  date
data1  123  12/03/2009
data1  124  15/09/2009
data2  333  02/09/2010
data2  323  02/11/2010
data2  673  02/09/2014
data2  444  05/01/2010

I want to group result set like this one:

data1
  123  12/03/2009
  124  15/09/2009
data2
  333  02/09/2010
  323  02/11/2010
  673  02/09/2014
  444  05/01/2010

is it possible to do this with pure SQL?

Cheers.

Answer 1

There might be something like:

select name, size, date from
(
-- select only distinct rows, and empty values for size and date (header rows)
select ROWNUM rown, name, '', ''
from T
group by name
order by name

union all

-- select all data (non-header records)
select ROWNUM+1 rown, name, size, date 
from T 
order by name
)
order by name, rown

Explanation: First select from the union selects the records for the group header. It sorts the results by name. The row number gives the order. Second select from the union selects all the records for the group header. It sorts the results by name, and the row number gives the order. The union put all the information together. ROWNUM+1 for the second select ensures that records for the header (from the first select) are ordered before the detailed records.

Now... what you need to do and I don't recover so much SQL to know how to do it... is to put '' for name when size or date are '', in the main select (with a case/swich operation). Some help is needed here :).

Just as an observation, in the provided SQL, ROWNUM is a special column that provides the row number for a select (see Oracle for example).

The query is displayed just as principle, I am not 100% sure it works.

Update: ... thats a solution sketch. But I still believe this is a formatting problem, and not an SQL problem.

Answer 2

GROUP BY WITH ROLLUP (you're not really grouping - so you would actaully GROUP BY every column)

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

http://chiragrdarji.wordpress.com/2008/09/09/group-by-cube-rollup-and-sql-server-2005/

http://databases.about.com/od/sql/l/aacuberollup.htm

http://www.adp-gmbh.ch/ora/sql/group_by/group_by_rollup.html

http://msdn.microsoft.com/en-us/library/bb522495.aspx

Based on Lieven's code:

DECLARE @Table TABLE (
     name varchar(32)
    ,Size integer
    ,Date datetime
    )

INSERT  INTO @Table
VALUES  ('data1', 123, GETDATE())
INSERT  INTO @Table
VALUES  ('data1', 124, GETDATE())
INSERT  INTO @Table
VALUES  ('data2', 333, GETDATE())
INSERT  INTO @Table
VALUES  ('data2', 323, GETDATE())
INSERT  INTO @Table
VALUES  ('data2', 673, GETDATE())
INSERT  INTO @Table
VALUES  ('data2', 444, GETDATE())

SELECT  *
FROM    (
         SELECT *
         FROM   @Table
         GROUP BY NAME
               ,size
               ,date
                WITH ROLLUP
        ) AS X
WHERE   NAME IS NOT NULL
        AND (
             (
              Size IS NOT NULL
              AND Date IS NOT NULL
             )
             OR (
                 Size IS NULL
                 AND date IS NULL
                )
            )
ORDER BY NAME
       ,size
       ,date

Answer 3

Michael Todd is definitly right when he says this should be done on the client side but for the fun of it, this is one option

DECLARE @Table TABLE (name VARCHAR(32), Size INTEGER, Date DATETIME)

INSERT INTO @Table VALUES ('data1', 123, getdate())
INSERT INTO @Table VALUES ('data1', 124, getdate())
INSERT INTO @Table VALUES ('data2', 333, getdate())
INSERT INTO @Table VALUES ('data2', 323, getdate())
INSERT INTO @Table VALUES ('data2', 673, getdate())
INSERT INTO @Table VALUES ('data2', 444, getdate())

INSERT INTO @Table 
SELECT DISTINCT name, NULL, NULL
FROM @Table

SELECT 
  CASE WHEN Size IS NULL THEN Name ELSE NULL END
  , Size
  , Date
FROM @Table
ORDER BY Name, Size