Data merge in SQL

Question

I tried the below query to display data

select  e.Name,ic.Code,t1.pyear,t1.pmonth,t2.Amount     
from t1 
inner join t2 on t2.id=t1.id
inner join t3  on t3.Code=t2.Code
inner join t4 e on t4.employeeindex=t1.employeeindex 
where t1.pyear=2016 and t1.pmonth=1

union all 

select  e.Name,ic.Code,t1.pyear,t1.pmonth,t2.Amount     
from t1 
inner join t2 on t2.id=t1.id
inner join t3  on t3.Code=t2.Code
inner join t4 e on t4.employeeindex=t1.employeeindex 
where t1.pyear=2016 and t1.pmonth=2

and the data i am getting is like this-

scode amount month year       e_name
  abc     3847    1    2016    john
  ABC     20938   2    2016    john
  XYZ     2838    1    2016    david
  XYZ     29384   2    2016    david

now i want to display data like this

 s_code  amount1 amount2  month1  month2   year  e_name
   abc     3847    20938    1       2       2016    john
   xyz     2838    29384    1       2       2016    david

any solution ?

Both sides of the `UNION` look the same to me, which therefore can't explain your current output. Also, bad idea naming your columns `index`. That's a keyword in most SQL databases. — Tim Biegeleisen, Apr 18 '17 at 09:09
i mention here as t1 actual in my side there is tables name .. — user 123, Apr 18 '17 at 09:11
i think after this line "and the data i am getting is like this-" is data what i get — user 123, Apr 18 '17 at 09:22

score 1 · Accepted Answer · edited May 23 '17 at 12:02

edit

You could try to use cte. The following is just an example, adjust the column names by yourself because it's a bit of a mess

WITH unionall AS (
    select ic.Code, t2.Amount, t1.pmonth, t1.pyear, e.Name
    from t1 
    inner join t2 on t2.id=t1.id
    inner join t3  on t3.Code=t2.Code
    inner join t4 e on t4.employeeindex=t1.employeeindex 
    where t1.pyear=2016 and (t1.pmonth=1 OR t2.pmonth=2)
)

SELECT r1.scode, r1.amount as amount1, r2.amount as amount2
FROM unionall r1 inner join unionall r2 on r1.scode = r2.scode

-----------------------

If you know for sure that for each s_code there are 2 months (or at least a costant number) you could easily make a join on itself and select all the values with an alias, like so:

SELECT r1.scode, r1.amount as amount1, r2.amount as amount2 [...more fields...] FROM result r1 inner join result r2 on r1.scode = r2.scode [...many joins as many months...]

If there can be multiple month that you don't know, i would probably do what on mysql is a group_concat. Basically, you group by scode and then, in the same field, there's a list separated values. Too bad you can't do a group_concat in SQLServer, but there are workaround guides around the web

How to make a query with group_concat in sql server

What you ask can be easily achieved with a simple inner join. If you don't understand it, i suggest you to read a few guides before! — Mattia Nocerino, Apr 18 '17 at 12:17

Data merge in SQL

1 Answers1

edit

-----------------------