Combine multiple rows into 1 row

Question

Say for example I have a table that contains a description of a customer's activities while in a cafe. (Metaphor of the actual table I am working on)

Customer    Borrowed Book   Ordered Drink   Has Company
1               1       
1                                1  
1                                               Yes
2                                1  
3                                1  
3                                               Yes
4               1                1  
4                                1

I wish to combine the rows in this way

Customer    Borrowed Book   Ordered Drink   Has Company
1               1                 1            Yes
2                                 1 
3                                 1            Yes
4               1                 2

I did self join with coalesce, but it did not give my desired results.

Presumably you just want to sum([borrowed book]), sum([ordered drink]), max([has company]) and group by customer? — ZLK
– ZLK, Commented Sep 16, 2016 at 4:26
@ZLK, yes, kinda.. but the initial table is actually a temporary table of a result set in which I can't do a group by because of aggregate issues — Leaf Node
– Leaf Node, Commented Sep 16, 2016 at 4:30
Hm. Hard to help without knowing what the original data is and why you're having aggregate issues. Side note: when doing the sum, you probably need an isnull(x, 0) statement as well. — ZLK
– ZLK, Commented Sep 16, 2016 at 4:32

K.K · Accepted Answer · 2016-09-16 04:31:00Z

1

You can do this by group by,

select Customer,sum([borrowed book]), sum([ordered drink]), max([has company])
from customeractivity group by Customer

answered Sep 16, 2016 at 4:31

K.K

3663 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Unnikrishnan R · Accepted Answer · 2016-09-16 04:37:34Z

0

As per your comment, initial table is a temp table, Try to make the result as a cte result, then do aggregation on that, like the below query.

   ; WITH cte_1
      AS
     (  //your query to return the result set)
   SELECT customer,sum([borrowed book]) BorrowedBook, 
        sum([ordered drink]) OrderedDrink, 
        max([has company]) HasCompany
   FROM cte_1
   GROUP BY Customer

edited Sep 16, 2016 at 4:37

answered Sep 16, 2016 at 4:30

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Comments

Sandip - Frontend Developer · Accepted Answer · 2016-09-16 06:05:35Z

0

Use Group By:

DECLARE @tblTest as Table(
    Customer INT,
    BorrowedBook INT,
    OrderedDrink INT,
    HasCompany BIt
)

INSERT INTO @tblTest VALUES
(1,1,NULL,NULL)
,(1,NULL,1,NULL)
,(1,NULL,NULL,1)
,(2,NULL,1,NULL)
,(3,NULL,1,NULL)
,(3,NULL,NULL,1)
,(4,1,1,NULL)
,(4,NULL,1,NULL)

SELECT 
    Customer,
    SUM(ISNULL(BorrowedBook,0)) AS BorrowedBook,
    SUM(ISNULL(OrderedDrink,0)) AS OrderedDrink,
    CASE MIN(CAST(HasCompany AS INT)) WHEN 1 THEN 'YES' ELSE '' END AS HasCompany
FROM @tblTest
GROUP BY Customer

answered Sep 16, 2016 at 6:05

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

Comments

Pintu Kawar · Accepted Answer · 2016-09-16 06:31:00Z

Not sure, why you are getting error with group by.

Your coalesce should be correct. Refer below way.

Select customer
, case when [borrowed] = 0 then NULL else [borrowed] end as [borrowed]
, case when [ordered] = 0 then NULL else [ordered] end as [ordered]
, case when [company] = 1 then 'Yes' end as company
from 
(
    Select customer,
    coalesce(
    case when (case when borrowed = '' then null else borrowed end) = 1 then 'borrowed' end,
    case when (case when ordered = '' then null else ordered end) = 1 then 'ordered' end,
    case when (case when company = '' then null else company end) = 'Yes' then 'company' end
    ) val
    from Table
) main
PIVOT
(
    COUNT (val)
    FOR val IN ( [borrowed], [ordered], [company] )
) piv

OUTPUT:

customer | borrowed | ordered | company
---------------------------------------
1           1           1           Yes
2           NULL        1           NULL
3           NULL        1           Yes

Collectives™ on Stack Overflow

Combine multiple rows into 1 row

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related