SQL Query combine two rows into one row of data

Question

I have a table (see the image below --red box). It describes the content of my table (A, B, C, and D) are the columns. The data structure will always be like this, if col A is Type_1, only col B has a content while if Col A is Type_2, Col C and D has contents while col B is NULL.

Now, the table which re enclosed with green box is my desired output.

enter image description here

My experience on building a select statement is not very extensive and I'm almost leaning towards creating two separate tables to get my desired result (like 1 table for Type_1 data only and another table for Type_2 data only).

Question is, is it possible to query two rows and combine it to become a single output result using SELECT query? Considering that these two rows are on the same table?

Thanks.

If there were multiple Type_1 and multiple Type_2 rows, how would it know which ones to combine? — Joachim Isaksson
– Joachim Isaksson, Commented May 19, 2014 at 12:54
@JoachimIsaksson, The data structure will always be like this — Hamlet Hakobyan
– Hamlet Hakobyan, Commented May 19, 2014 at 12:58
I apologize, I realized after the first comment that my image was not very clear. Please see the updated topic. — ChiSen
– ChiSen, Commented May 19, 2014 at 12:59
@HamletHakobyan The data structure is the same, but data may not be. — ApplePie
– ApplePie, Commented May 19, 2014 at 13:00

Hamlet Hakobyan · Accepted Answer · 2014-05-19 12:57:40Z

4

Something like this:

SELECT
  Table2Id,
  MAX(B) B,
  MAX(C) C,
  MAX(D) D
FROM tbl
WHERE A != 'Type_3'
GROUP BY Table2Id

answered May 19, 2014 at 12:57

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

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

Comments

Kevin Hogg · Accepted Answer · 2014-05-19 13:16:52Z

1

Assuming that there is only one row of data for type1 and one row of data for type 2, you can use the following:

 SELECT Id, MAX(B) AS B, MAX(C) AS C, MAX(D) AS D
   FROM Table2
  WHERE A IN ('Type_1','Type_2')
  GROUP BY Id

Example in this SQL Fiddle

answered May 19, 2014 at 13:16

Kevin Hogg

1,78125 silver badges34 bronze badges

Comments

marcosdumay · Accepted Answer · 2014-05-19 13:01:28Z

0

You can make subqueries by enclosing them in parenthesis. As in:

SELECT (SELECT TOP 1 B FROM table ORDER BY some_ordering), (SELECT TOP 1 C FROM table WHERE NOT C IS NULL), D FROM table

The queries inside the parenthesis can apply to any table, and can use the data from the main query in calculations of the selected values and in filters.

answered May 19, 2014 at 13:01

marcosdumay

11 bronze badge

Collectives™ on Stack Overflow

SQL Query combine two rows into one row of data

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related