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

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 2

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

Answer 3

Shaping the data ahead of time simplifies things for the application developer, especially if all they're doing is displaying a static report with no interactive editing, sorting, or paging --a common enough occurrence.

Lieven's solution (selecting the columns distinctively while inserting nulls for other columns, then checking for the nulls of the other columns) is the only solution here that actually works. Kind of.

data1   NULL    NULL
NULL    123 2011-05-24 19:42:29.577
NULL    124 2011-05-24 19:42:29.577
data2   NULL    NULL
NULL    323 2011-05-24 19:42:29.577
NULL    333 2011-05-24 19:42:29.577
NULL    444 2011-05-24 19:42:29.577
NULL    673 2011-05-24 19:42:29.577

--unless the column you're nullalizing already has nulls, in which case we're back to square 0. Add one more row.

INSERT INTO @Table VALUES ('data2', NULL, getdate())

Now run the query again. Bummer dude.

data1   NULL    NULL
NULL    123 2011-05-24 19:53:36.437
NULL    124 2011-05-24 19:53:36.437
data2   NULL    NULL
data2   NULL    2011-05-24 19:53:36.440
NULL    323 2011-05-24 19:53:36.440
NULL    333 2011-05-24 19:53:36.440
NULL    444 2011-05-24 19:53:36.440

Roux's proposed ROLLUP solution doesn't work at all, at least not in SQL Server. Actually, it makes things more worserer.

data1   NULL    NULL
data1   NULL    NULL
data1   NULL    NULL
data1   123 2011-05-24 20:16:26.693
data1   124 2011-05-24 20:16:26.693
data2   NULL    NULL
data2   NULL    NULL
data2   NULL    NULL
data2   323 2011-05-24 20:16:26.693
data2   333 2011-05-24 20:16:26.693
data2   444 2011-05-24 20:16:26.693
data2   673 2011-05-24 20:16:26.693

Pitiș' ROWNUM solution may work in Oracle (I haven't tried it and there appears to a missing beginning parentheses), but the equivalent SQL Server code using ROW_NUMBER() OVER definitely taint working no good neither --about as well as my grammer and speling.

SELECT 
    ROW_NUMBER() OVER(ORDER BY [name]) AS [rown]
    , name
    , ''
    , ''
FROM @Table
GROUP BY name

UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY [name]) + 1 AS [rown] , name, size, date 
FROM @Table 

produces

data1   NULL    NULL
data1   NULL    NULL
data1   NULL    NULL
data1   123 2011-05-24 20:16:26.693
data1   124 2011-05-24 20:16:26.693
data2   NULL    NULL
data2   NULL    NULL
data2   NULL    NULL
data2   323 2011-05-24 20:16:26.693
data2   333 2011-05-24 20:16:26.693
data2   444 2011-05-24 20:16:26.693
data2   673 2011-05-24 20:16:26.693

As a software professional your job is to ensure billions of 1s and 0s line up at the right place, in the right order, at the right time. You know the details, often times down to an individual bit, are important.

A half vast answer is worse than no answer at all because it wastes everyone's time. So... no offense intended because intentions are good, but please at least test your "solution" before posting it as a "solution".

I would be perfect if I weren't so darned humble. And even I test.

Answer 4

You can use two queries and then group/format in the application code.

First query

SELECT DISTINCT(name) AS group_name FROM TABLE ORDER BY name LIMIT 5;

Second query

SELECT size, date FROM TABLE WHERE name IN ('comma separated group_name values from firstQueryResult');

Application code

for every row in firstQueryResult{
    group_array[group_name] = secondQueryResult;
}

The resulting group_array will be like;

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