Click here to Skip to main content
15,892,746 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Question: Concatenate values from multiple columns of 3 tables and display in one column. Date field is datetime.now my problem is if date is repeat of one table then amt2,amt3 is also repeat on same date
table1:

date        amt1
-----------------
1-1-2016    111
1-1-2016    222    repeat date
3-4-2016    111


table2:

date        amt2
-----------------
1-1-2016    101 
2-2-2016    333
2-3-2016    444
3-3-2016    456
1-4-2016    101     
3-4-2016    111     


table3:

date        amt3
-----------------
2-2-2016    001
2-3-2016    002 
3-3-2016    003
1-4-2016    555
2-4-2016    666
3-4-2016    777


query
SQL
SELECT 
    COALESCE(t1.date, t2.date, t3.date)
  , t1.amount
  , t2.cashamount
  , t3.grandtotal
FROM tb_amount t1
FULL JOIN tb_cashamt t2 ON t1.date =t2.date 
FULL JOIN tb_grandtotal  t3 ON t2.date = t3.date


output:

date        amt1     amt2    amt3
----------------------------------
1-1-2016    111     101      NULL data repeat on amt2 and amt3
1-1-2016    222     101      null 
2-3-2016    NULL    444      002 
3-3-2016    NULL    456      003

------------------------------------
TOTAL       ---    ---     ----- 


Desired output:


date        amt1     amt2    amt3
----------------------------------
1-1-2016    333     101      NULL 
2-3-2016    NULL    444      002 
3-3-2016    NULL    456      003

------------------------------------
TOTAL       ---    ---     -----


What I have tried:

Concatenate values from multiple columns of 3 tables and display in one column. Date field is smalldatetime.now my problem is if date is repeat of one table then amt2,amt3 is also repeat on same date.i explain desired output in the same date total amt1,amt2,amt3 display according to date.
Posted
Updated 25-May-16 16:51pm
v3

1 solution

One option is to use Common Table Expressions:

SQL
WITH cte1 AS
(
  SELECT [date]
  , SUM([amt1]) [amt1]
  FROM #table1
  GROUP BY [date]
)
, cte2 AS
(
  SELECT [date]
  , SUM([amt2]) [amt2]
  FROM #table2
  GROUP BY [date]
)
, cte3 AS
(
  SELECT [date]
  , SUM([amt3]) [amt3]
  FROM #table3
  GROUP BY [date]
)
SELECT 
    COALESCE(t1.[date], t2.[date], t3.[date]) [date]
  , t1.[amt1]
  , t2.[amt2]
  , t3.[amt3]
FROM cte1 t1
FULL JOIN #table2 t2 ON t1.[date] = t2.[date]
FULL JOIN #table3 t3 ON t2.[date] = t3.[date]


Common Table Expressions(CTE) in SQL SERVER 2008[^]

Using Common Table Expressions[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900