sql statement with group by

Question

hi,

after a very long sql query a have a result that is in this form:

---col1---col2---col3---col4

---

---1234---1------aaaa---bbbb
---2378---0------aaaa---bbbb
---9753---1------cccc---uuuu
---1234---0------iiii---yyyy
---2378---1------iiii---yyyy
---9753---1------tttt---mmmm

but i don't need it this way. i have to do another sql-statement on this result where i have to use group by the third and forth column. in other words two rows in one, like this:

---col1---col2---col3---col4---col5---col6

---

---1234---1------2378---0------aaaa---bbbb
---9753---1------null---null---cccc---uuuu
---1234---0------2378---1------iiii---yyyy
---9753---1------null---null---tttt---mmmm

thanks in advance

Answer 1

Hello.

You could solve that problem by making two additional tables (temporary or not, depends on your requirements and SQL engine) which structure will be mapping the columns from query.

For example:

CREATE TABLE TableA (
  Col1 int,
  Col2 bit,
  Col3 varchar(4),
  Col4 varchar(4)
)

CREATE TABLE TableB (
  Col1 int,
  Col2 bit,
  Col3 varchar(4),
  Col4 varchar(4)
)

Note that this is only an example structure based on the output data which you have presented.

After making those two tables, you would have to insert data from query to each of the tables.

Added: You don't have to execute the same query twice. Execute it once, put the data to the TableA then make SELECT on TableA and put data to the TableB. That will save you a lot of time.

The last step would be to perfrom query on TableA and TableB with JOIN on their columns Col3 and Col4. Something like this:

SELECT A.Col1, A.Col2, B.Col1, B.Col2, COALESCE(A.Col3, B.Col3), COALESCE(A.Col4, B.Col4)  
FROM TableA A INNER JOIN TableB B ON A.Col3 = B.Col3 AND A.Col4 = B.Col4

Hope this will help.

Also please keep in mind that this solution has one major drawback:

If you change number of columns or their data type in the oryginal query, you will have to also update the table definitions. Possible solution to this would be using a dynamic sql, but it is generally not adviced.

After edit (you've provided additional NULLS in output data):

If you want to keep NULL values, you should use different join, for example: LEFT OUTER JOIN

Another possible solutions are:

1. Just replace (SELECT * FROM dbo.Test) with your long query. However this will cause that query will be run twice.

   SELECT A. *, B. * FROM (SELECT * FROM dbo.Test) A LEFT OUTER JOIN (SELECT * FROM dbo.Test) B ON A.Col3 = B.Col3 AND A.Col4 = B.Col4 AND A.Col1 <> B.Col1 AND A.Col2 <> B.Col2

2. If you use SQL 2005 you could try to use CROSS APPLY operator.

There might be other solutions but you must be more descriptive about SQL engine that you use and your rights on the database.

Answer 2

Hmm,

I'd toss the result into a temp table. Then do

select 
a.col1, 
a.col2, 
b.col1,
b.col2,
coalesce(a.col3, b.col3) as col5, 
coalesce(a.col4,b.col4) as col6 
from #tmp a 
outer join #tmp b 
on a.col3 = b.col3 and a.col4 = b.col4 
where a.col2 = 0 and b.col2 = 1

This is ofcourse assuming that the col2 is an indicator of which part of the pair you have.

Answer 3

i found the correct solution, based on the first solution of @Wodzu.

firstly, create a temporary view that will contain the result.

with tmp as ( --my very long sql statement ... )

secondly, make the select as there are two of them, in these way the query is called only one time.

select a.*, b.* from tmp a inner join tmp b on conditions.

Thanks Wodzu