SQL Group Rows with Same Value, and Put That Value into Header

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

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

How to group rows with same value in sql?

Try this

DECLARE @temp TABLE(col1 varchar(20),col2 int, col3 varchar(20))
insert into @temp values ('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')

SELECT 
    (CASE rno WHEN 1 THEN col1 ELSE '' END )AS col1,
    col2,
    col3
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY col2) AS rno,
        col1,col2,col3
    FROM @temp
) As temp

This gives the following output

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

PARTITION BY is grouping the data with the given column name, and a row number is generated in that group based on the order by.

Here is the SQL Fiddle

I have created another fiddle based on the schema provided .fiddle2

Group SQL rows with same value and add group headings

You can keep your current PHP code as is, with a slight modification. You will want to add a variable that tracks the current category, and write out a new one when it changes:

$result = $con->query($sql);        
if ($result->num_rows > 0) {        
    $curCategory = '';
    while($row = $result->fetch_assoc())
    {
        if($curCategory != $row["category"])
        {
            $curCategory = $row["category"];
            echo '<p class="menuCategory"><strong>' . $curCategory . '</strong></p>';
        }
        echo '<p class="menuItem">' . $row["item"] . ' - £' . $row["price"] . '</p>';
    }
}

Of course, you will want to ensure your resultset from your query is sorted accordingly.

Group Values under a header MSSQL

Using GROUP BY in SQL Server query will not solve your problem - you need all data returned by original query. The desired grouping is not at data level but while displaying the data. Listview does support grouping - see this multi-part article that illustrates how to do the grouping as desired by you (specifically see part titled "Grouping By a Data Field").

SQL Server : using column values as table headers in a view

If you want to perform this in SQL Server and not the presentation layer, Dynamic SQL would be required

Example

Declare @SQL varchar(max) = '
Select *
From (
        Select Serial
              ,Machine
              ,Date
              ,Parameter
              ,Value
        From #YourTable
     ) src
 Pivot (sum(Value) For [Parameter] in (' + stuff((Select Distinct ',' + QuoteName(Parameter) From  #YourTable Order By 1 For XML Path('') ),1,1,'')  + ') ) pvt'
Exec(@SQL);

Results

How can I create header records by taking values from one of several line items?

use first_value window function

select * from (select *,
first_value(DESCRIPTION) over(partition by id order by Date) as des,
row_number() over(partition by id order by Date) rn
from table
) a where a.rn =1

How to convert specific column value as header for set of values in SQL?

This can be achieved with a group by rollup.

Initial solution

-- create sample data
declare @data table
(
    id int,
    code nvarchar(10),
    department nvarchar(20)
);

insert into @data (id, code, department) values
( 1, 'code A', 'Science 1'),
( 2, 'code B', 'Science 1'),
( 3, 'code A', 'Science 2'),
( 4, 'code C', 'Science 1'),
( 5, 'code B', 'Science 2'),
( 6, 'code A', 'Science 3'),
( 7, 'code C', 'Science 2'),
( 8, 'code B', 'Science 3'),
( 9, 'code A', 'Science 4'),
(10, 'code C', 'Science 3'),
(11, 'code B', 'Science 4');

Single result query:

-- solution
select coalesce(d.department, d.code) as 'Department'
from @data d
group by d.code, d.department with rollup
having grouping(d.code) = 0
order by d.code, d.department;

Run this query to understand how I came to the previous solution:

-- solution explained
select  grouping(d.department) as 'grouping_dep',
        d.department,
        grouping(d.code) as 'grouping_cod',
        d.code,
        coalesce(d.department, d.code) as 'Department',
        case
            when grouping(d.code) = 1 then 'aggregation across codes, filtered out with "having"'
            else 'select "department", replace with "code" when null'
        end as 'explanation'
from @data d
group by d.code, d.department with rollup -- group on every level and "roll up" the aggregations
order by d.code, d.department;

Expanded solution

Handles 2 extra columns (based on additional input in comment).

-- create sample data
declare @data table
(
    id int,
    code nvarchar(10),
    department nvarchar(20),
    colA nvarchar(10),
    colB nvarchar(10)
);

insert into @data (id, code, department, colA, colB) values
( 1, 'code A', 'Science 1', 'A1_A', 'A1_B'),
( 2, 'code B', 'Science 1', 'B1_A', 'B1_B'),
( 3, 'code A', 'Science 2', 'A2_A', 'A2_B'),
( 4, 'code C', 'Science 1', 'C1_A', 'C1_B'),
( 5, 'code B', 'Science 2', 'B2_A', 'B2_B'),
( 6, 'code A', 'Science 3', 'A3_A', 'A3_B'),
( 7, 'code C', 'Science 2', 'C2_A', 'C2_B'),
( 8, 'code B', 'Science 3', 'B3_A', 'B3_B'),
( 9, 'code A', 'Science 4', 'A4_A', 'A4_B'),
(10, 'code C', 'Science 3', 'C3_A', 'C3_B'),
(11, 'code B', 'Science 4', 'B4_A', 'B4_B');

Because of the grouping you will need to use an aggregation function (like the min function use here) to select the values.

-- solution
select  coalesce(d.department, d.code) as 'Department',
        case when grouping(d.department)=1 then '' else min(d.colA) end as 'colA',
        case when grouping(d.department)=1 then '' else min(d.colB) end as 'colB'
from @data d
group by d.code, d.department with rollup
having grouping(d.code) = 0
order by d.code, d.department;

This gives:

Department           colA       colB
-------------------- ---------- ----------
code A
Science 1            A1_A       A1_B
Science 2            A2_A       A2_B
Science 3            A3_A       A3_B
Science 4            A4_A       A4_B
code B
Science 1            B1_A       B1_B
Science 2            B2_A       B2_B
Science 3            B3_A       B3_B
Science 4            B4_A       B4_B
code C
Science 1            C1_A       C1_B
Science 2            C2_A       C2_B
Science 3            C3_A       C3_B

---

Related Topics