Combine multiple rows in single row

Question

i am new to SQL server and i am trying to combine multiple row in single row but i am not able to do it.Can anyone help me out?

Input :

Id      |RED    |BUY    |BSW
------------------------------------
1328        NULL    NULL    0.05
1328        NULL    0.06    NULL
1328        0.01    NULL    NULL
1328        0.05    NULL    NULL
1329        NULL    NULL    0.05
1329        NULL    0.05    NULL
1329        0.05    NULL    NULL

Output

Id         |RED    |BUY    |BSW
------------------------------------
1328        0.01    0.06    0.05
1328        0.05    NUll    NULL
1329        0.05    0.05    0.05

Editing data so as to remove SUM() conflict.

Is there just (at most) one row with non-NULL data for each of the columns? Because then you could just `SUM(COALESCE(RED, 0)), SUM(COALESCE(BUY, 0)), ...` and `GROUP BY Id`. — CompuChip, Feb 17 '14 at 11:05
what logic you have applied & got the following output ? Show your SQL. — Aditya, Feb 17 '14 at 11:07
i think given input is not the complete scenario.show the original table data. — KumarHarsh, Feb 17 '14 at 11:11
@KumarHarsh Table contains many columns so i can paste complete table.But every columns for each `Id` contains same value except for `RED`,`BUY`,`BSW`. — Flying_Machine, Feb 17 '14 at 11:14
@Flying_Machine why can you not use `SUM`? It works if there is zero or one record for each of the `RED`, `BUY` and `BSW` values. Otherwise please provide a more accurate example or description. — CompuChip, Feb 17 '14 at 11:17
how about `sum(isnull(red,0)),sum(isnull(buy,0)),sum(isnull(bsw,0))` — vasin1987, Feb 17 '14 at 11:23
@CompuChip Because in code it checks if value is `0.05` or `0.00` .if i use `SUM()` then `0.05+0.01` will evaluate to `0.06` which would be wrong. — Flying_Machine, Feb 17 '14 at 11:23
@Flying_Machine can you make clearer example. Maybe with all different value so we know what to accomplish. WIth this example it looks like you need SUM() function that's why we come up with that solution — vasin1987, Feb 17 '14 at 11:30
@Flying_Machine Please explain your logic here. What make first 3 lines combine into one line and fourth one separate line — vasin1987, Feb 17 '14 at 11:40
I agree with people here, explain your logic, show us a piece of your code. — Orlando Herrera, Feb 17 '14 at 15:09

Vignesh Kumar A · Answer 1 · 2014-02-17T14:09:27.527

0

Try like this

SELECT id, 
       Sum(Isnull(red, 0)) 
         OVER ( 
           partition BY id 
           ORDER BY id), 
       Sum(Isnull(buy, 0)) 
         OVER ( 
           partition BY id 
           ORDER BY id), 
       Sum(Isnull(bsw, 0)) 
         OVER ( 
           partition BY id 
           ORDER BY id) 
FROM Table1
GROUP  BY id

edited Feb 17 '14 at 14:09

answered Feb 17 '14 at 11:41

Vignesh Kumar A

27,863
13
63
115

your query is giving error.`The isnull function requires 2 argument(s). ` – Flying_Machine Feb 17 '14 at 12:10

score 0 · Answer 2 · edited Feb 17 '14 at 14:09

first of all change the field default setting to zero instread of null it will increase the processing speed then try this

SELECT id, 
       Sum((red, 0)) 
         OVER ( 
           partition BY id 
           ORDER BY id), 
       Sum((buy, 0)) 
         OVER ( 
           partition BY id 
           ORDER BY id), 
       Sum((bsw, 0)) 
         OVER ( 
           partition BY id 
           ORDER BY id) 
GROUP  BY id

Combine multiple rows in single row

2 Answers2