Sql Server - Group rows

Question

The problem is that I have a table in which I have multiple rows like this:

Name    Container       DateOrder      HourOrder 
Test1 | Container1 |    04-21-2015 |    11:00
Test1 | Container2 |    04-22-2015 |    16:00
Test2 | Container2 |    04-15-2015 |    01:00
Test3 | Container3 |    04-15-2015 |    15:00

I need to do a report by Name, dates and containers... how can I do that, the result should look like this:

 Name     | Container  |    Container2  |   DateOrder  | DateOrder2 | HourOrder  | HourOrder2 
    Test1 | Container1 | Container2     |   04-21-2015 | 04-22-2015 |   11:00    | 16:00 
    Test2 | Container2 |                |   04-15-2015 |            |   01:00
    Test3 | Container3 |                |   04-15-2015 |            |   15:00

Also I need to know which date is from which container, for example the Container1 the date is DaterOrder1, Any suggest?

you basically want to concatenate (aggregate) `Container` and `DateOrder` group by `Name` - there are 2 ways to do this: XML or CLR. Example XML solution: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — YS., Apr 22 '15 at 03:54
I think, We can also use COALESCE for this....Check this : http://stackoverflow.com/questions/8700961/using-coalesce-function-to-make-values-separated-with-commas — Paresh J, Apr 22 '15 at 03:58

score 1 · Accepted Answer · answered Apr 22 '15 at 05:34

You can use ROW_NUMBER() to do something like this

;WITH TestTable AS 
(
SELECT 'Test1' AS Name,'Container1' AS Container,'04-21-2015' AS DateOrder,'11:00' AS HourOrder 
UNION ALL SELECT 'Test1','Container2 ','04-22-2015 ','16:00'
UNION ALL SELECT 'Test2','Container2 ','04-15-2015 ','01:00'
UNION ALL SELECT 'Test3','Container3 ','04-15-2015 ','15:00'
),
CTE AS 
(
SELECT Name,Container,DateOrder,HourOrder,ROW_NUMBER()OVER(PARTITION BY Name ORDER BY Container ASC) rn
FROM TestTable
)

SELECT 
    C1.Name,
    C1.Container as Container,
    C2.Container as Container2,
    C1.DateOrder as DateOrder,
    C2.DateOrder as DateOrder2,
    C1.HourOrder as HourOrder,
    C2.HourOrder as HourOrder2
FROM CTE C1
LEFT JOIN CTE C2
    ON C1.Name = C2.Name
        AND C1.rn + 1 = C2.rn
WHERE C1.rn = 1

Sql Server - Group rows

1 Answers1