How to group data from two tables ?

Question

Hello,
I have two tables and i want to group data from them but two table not linked.

Table TEXT_IN : ID_IN (primary_key), DATE_IN

Table TEXT_OUT : ID_OUT(primary_key),DATE_OUT

Example :

Result :Group Date and Order by IN,OUT

And It seems a bit confusing because we do not link .You can give me solutions.
Thank you.

Answer 1

SELECT MAX(CASE WHEN Rn = 1 THEN [IN] END) AS [IN1],
MAX(CASE WHEN Rn = 1 THEN [OUT] END) AS [OUT1],
MAX(CASE WHEN Rn = 2 THEN [IN] END) AS [IN2],
MAX(CASE WHEN Rn = 2 THEN [OUT] END) AS [OUT2],
MAX(CASE WHEN Rn = 3 THEN [IN] END) AS [IN3],
MAX(CASE WHEN Rn = 3 THEN [OUT] END) AS [OUT3],
MAX(CASE WHEN Rn = 4 THEN [IN] END) AS [IN4],
MAX(CASE WHEN Rn = 4 THEN [OUT] END) AS [OUT4],
MAX(CASE WHEN Rn = 5 THEN [IN] END) AS [IN5],
MAX(CASE WHEN Rn = 5 THEN [OUT] END) AS [OUT5],
..
FROM
(
SELECT COALESCE(m.DATE_IN,n.DATE_IN) AS DATE_IN,
COALESCE(m.Seq,n.Seq) AS Seq,
ID_IN AS [IN],
ID_OUT AS [OUT],
ROW_NUMBER() OVER (PARTITION BY Seq ORDER BY COALESCE(m.DATE_IN,n.DATE_IN)) AS Rn
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY DATE_IN ORDER BY DATE_IN) AS Seq,*
FROM TEXT_IN
)m
FULL OUTER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY DATE_IN ORDER BY DATE_IN) AS Seq,*
FROM TEXT_OUT
)n
ON n.Seq = m.Seq
AND n.DATE_IN = m.DATE_IN
)t
GROUP BY Seq

to make it dynamic see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Hi Visakh,

Glad you responded. Can you give me more details?

But assuming that there are many days and many ID_IN, ID_OUT, I have listed many CASE is not it (@~@).
You try and run the demo see the results I see

Thank sir.

Answer 3

Hi Visakh,

Glad you responded. Can you give me more details?

But assuming that there are many days and many ID_IN, ID_OUT, I have listed many CASE is not it (@~@).
You try and run the demo see the results I see

Thank sir.

Check the link

It has the logic to make code dynamic based on how many dates you have in your table

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

Hi Visakh,

It seems you are joining the tables.  There is condition that you should not join them.

---

Mani Prabhakar

Answer 5

Hi HideCoding,

You have to Join them together to bring them under one TAB.

You can simple join them on Date (as shown below). and simply form your require report  format

select 
ID_IN,
ID_OUt,
Date_In,
Date_out
from TblIN I
FULL OUTER JOIN  join  TblOut O on I.DateIN=o.Dateout

---

Shiv

Answer 6

Hi HideCoding,

You have to Join them together to bring them under one TAB.

You can simple join them on Date (as shown below). and simply form your require report  format

select 
ID_IN,
ID_OUt,
Date_In,
Date_out
from TblIN I
FULL OUTER JOIN  join  TblOut O on I.DateIN=o.Dateout

---

Shiv

ok , i can JOIN data from two table , and i think UNION (merge DATE_IN and DATE_OUT) , GROUP (ID_IN, ID_OUT)

But i can`t , it`s error ? what happen ? You can give me solutions.Thank

headshot9x

Answer 7

Hi HideCoding,

You have to Join them together to bring them under one TAB.

You can simple join them on Date (as shown below). and simply form your require report  format

select 
ID_IN,
ID_OUt,
Date_In,
Date_out
from TblIN I
FULL OUTER JOIN  join  TblOut O on I.DateIN=o.Dateout

---

Shiv

ok , i can JOIN data from two table , and i think UNION (merge DATE_IN and DATE_OUT) , GROUP (ID_IN, ID_OUT)

But i can`t , it`s error ? what happen ? You can give me solutions.Thank

headshot9x

UNION wont give you the desired output as what you want is ID_IN and ID_OUT values against each other

It will match all based on date which is why you get result above

If you see my suggestion this was the reason i added a sequencenumber and joined using it.

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 8

Hi Visakh,

It seems you are joining the tables.  There is condition that you should not join them.

---

Mani Prabhakar

If you want results like what you showed ie columns from two tables against each other then it has to be a join.

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page