SQL Combine multiple rows into one

Question

How do I combine these? I want it into 1 row and the SUM of the item is 10. Combine not the sum of all rows that shows.

ItemID  Name    Discription   ENHET1    ENHET2  ENHET3  SUM 
1       Adel        CD        NULL      NULL    NULL    10
1       Adel        CD        NULL      X       NULL    10
1       Adel        CD        X         NULL    NULL    10

Alternatively:

ItemID  Name    Discription   ENHET1    ENHET2  ENHET3   
1       Adel        CD        NULL      NULL    NULL
1       Adel        CD        NULL      X       NULL    
1       Adel        CD        X         NULL    NULL

And I can make a join with the sum separately?

Desired result:

ItemID  Name    Discription   ENHET1    ENHET2  ENHET3  SUM 
1       Adel        CD        X         X       NULL    10

I am amazed !! how could these tables used in real world ?

Ragul
– Ragul

2015-12-16 10:05:46 +00:00
Commented Dec 16, 2015 at 10:05 — Ragul
– Ragul, Commented Dec 16, 2015 at 10:05

Thorsten Kettner · Accepted Answer · 2015-12-16 09:06:02Z

1

Strange table. However, you can simply aggregate and use MIN or MAX on the columns:

select 
  itemid, 
  min(name), min(discription), min(enhet1), min(enhet2), min(enhet3), min("sum")
from mytable
group by itemid;

answered Dec 16, 2015 at 9:06

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

5 Comments

Jonas Over a year ago

Thanks ALOT! and for extremly fast answer =D

Jonas Over a year ago

Is it possible to make it dynamic? so if we add a new enhet it just apears?

Thorsten Kettner Over a year ago

As a general rule: a data model is static; if you add data, you add rows, so the queries stay the same. Once you add columns to your model and you want your queries to take them into account, you will have to change your queries. Your database design looks bad (holding redundant data), which may be the reason for your intention to have a query automatically reacting to more columns ...

Thorsten Kettner Over a year ago

So the best solution would be to change your database design, e.g. one table Item(ItemID, Name, Discription, Amount) with ItemID being the primary key, and one table Item_Enhet(ItemID, EnhetNo, Value) with ItemID + EnhetNo as primary key. But of course it is always possible to have such query dynamic: Use an app that you write in a programming language (C#, Java, ...). Execute a query to get the column names for the table, then use the result to build the final query und execute this.

Jonas Over a year ago

I cant change the DB design its how the application speciallist have set up AX. But thanks for the input

Collectives™ on Stack Overflow

SQL Combine multiple rows into one

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related