SQL merging rows with same time

Question

0.00/5 (No votes)

See more:

, +

I am asking for help with my databse. I need a SQL query doing following.

http://shrani.si/f/2E/ge/2JXHBdq2/untitled.png

As you can see, that the time is equal in 3 rows, but the data is not in one row. What i want, is to make 1 row out of these.

http://shrani.si/f/3W/lZ/4NJJyHxf/untitled.png

Could u help me generate the query, if its possible? Or will i have to loop trough the databse and compare values and paste them together?

Thnx for your help!

Posted 23-Jul-13 2:06am

tokano

Updated 23-Jul-13 2:07am

v2

Add a Solution

Comments

ArunRajendra 23-Jul-13 8:32am

Sum up all the columns grouped by time.

3 solutions

Solution 1

Here is the solution for your problem.

i have worked on a sample table.
input table:

C#

id	val1	val2
1	5	NULL
2	NULL	2
1	NULL	2
2	3	NULL

select * from (select distinct s1.id, s1.val1, s2.val2<br />
from sample as s1<br />
inner join sample as s2<br />
on s1.id = s2.id) t where t.val1 IS NOT NULL and t.val2 IS NOT NULL

table output after query.

C#

id      val1    val2
1	5	2
2	3	2

Posted 23-Jul-13 2:46am

Sarin VT

Updated 23-Jul-13 5:07am

v2

Comments

tokano 23-Jul-13 9:35am

Oh thank you very much! Can i ask you to edit your solution a bit, because so many "&" and quotes are in, i'm getting confused.

Is also there any way to join two sql querys? Because first i order them by date and time, then i want to execute the query u wrote.

Thnx

P.s.: And how can i continue if u have more then 2 columns as shown on the picture?

From sample as s1
inner join sample as s2
inner join sample as s3...etc?

edit : thank for your contribution, but the statement with SUM and group BY is easier for me, and does the same work!

Solution 3

Hi, The query modified for you solution is.

select * from <br />
(select distinct s1.date, s1.time, s1.st3_T1_KV, s1.st3_T2_KV, s2.st4_T1_KV, ... etc<br />
from your_table_name as s1 inner join your_table_name as s2 on s1.id = s2.id) t <br />
where t.st3_T1_KV IS NOT NULL and t.st3_T2_KV IS NOT NULL and t.st4_T1_KV, ans ..... etc

hopefully this will give your result.

Posted 23-Jul-13 5:16am

Sarin VT

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Naz_Firdouse · Accepted Answer · 2013-07-23T02:49:00

Solution 2

modify your select query like this

SQL

SELECT BREAKS,TIME,   
SUM (st3_T1_KV),SUM (st3_T2_KV)  
FROM orders GROUP BY TIME;

Note: add all your columns...for reference,I showed only four columns

for more info,refer this
SQL SUM() with group by [^]

Posted 23-Jul-13 2:49am

Naz_Firdouse

Comments

damodara naidu betha 23-Jul-13 9:06am

Yes. You are right. No need to use joins here. You forgot to include columns DATE, BREAKS in GROUP BY clause.

tokano 23-Jul-13 10:01am

Thanks for your answer. I've run it and it actually done what it should, many thanx! I've been looking into the SUM function, and i dont understand it well. Doesnt sum (summa) cumulate those numbers then?

Edit: i see it actually does, doesnt it? But the other columns joined are empty, therefore no value is changed. Am i right?

tokano 23-Jul-13 15:47pm

I still have a question here. What if some rows already have the value which is the same as in the other row and i would like to overwrite it, not cumulate it (sum). What could i do then?

Here is the picture of what i mean:

http://shrani.si/f/3g/F0/zA1BEAY/untitled.png

See those two rows having the same time, but some values are in the second row also. I would like to merge them.