I have two columns and I want to combine both not by concat just combining one row below the other

Question

I got two columns Named 'Next Schedule Date' and 'Expected Scheduled Date' in Sql table. What I want exactly is to combine two columns not as a concatenation just combining them one row below the other row.

I have tried unPivot but its not working. Any suggestions pls

select [Asset / Location],[Department Name], Future_Scheduled_Date from merge2_ernie Unpivot ( Future_Scheduled_Date for [Department Name] in (select Distinct [Department Name] from merge2_ernie) ) as example

My data is now looking something like this

**Department_Name**    **Next_Schedule_Date**   **Expected_Scheduled_Date**

Dept1                    Aug 2019             Sep 2019
Dept1                    Oct 2019             Nov 2019
Dept2                    Jan 2019             Mar 2019
Dept3                    Nov 2019             Jan 2020
Dept4                    Apr 2019             Sep 2019
Dept5                    Jun 2019             Jul 2019

I want the data to be like this below

Department_Name    Future_Schedule_Date   

Dept1               Aug 2019            
Dept1               Sep 2019           
Dept1               Oct 2019          
Dept1               Nov 2019           
Dept2               Jan 2019
Dept2               Mar 2019
Dept3               Nov 2019
Dept3               Jan 2020
Dept4               Apr 2019             
Dept4               Sep 2019
Dept5               Jun 2019
Dept5               Jul 2019

Not yet thank you so much. I'll look into it now. Thanks – balaji rajaram Jul 18 '19 at 15:41 — balaji rajaram, Jul 18 '19 at 15:41
I messed up. This is a duplicate. – Gordon Linoff Jul 18 '19 at 15:47 — Gordon Linoff, Jul 18 '19 at 15:47

score 1 · Answer 1 · answered Jul 18 '19 at 15:51

1

You need to UNPIVOT not PIVOT

CREATE TABLE #dept
(
    dept VARCHAR(5),
    nextdate VARCHAR(10),
    expecteddate VARCHAR(10) 
)

INSERT INTO #dept(dept, nextdate, expecteddate)
VALUES
('Dept1','Aug 2019','Sep 2019'),
('Dept1','Oct 2019','Nov 2019'),
('Dept2','Jan 2019','Mar 2019'),
('Dept3','Nov 2019','Jan 2020'),
('Dept4','Apr 2019','Sep 2019'),
('Dept5','Jun 2019','Jul 2019');

-- unpivot the data into key value pairs
SELECT dept, val
FROM 
(
SELECT *
from #dept
) a
UNPIVOT
(
val
FOR name IN ([nextdate],[expecteddate])
) u

answered Jul 18 '19 at 15:51

James Casey

2,447
1
11
19

Awesome it just wrked. thanks a lotttttttttttttttttt – balaji rajaram Jul 18 '19 at 16:17
Nice. Please upvote and accept the answer so I score hot Internet Points – James Casey Jul 18 '19 at 19:23

score 0 · Accepted Answer · answered Jul 18 '19 at 15:52

0

You can use apply :

select Department_Name, Future_Schedule_Date
from table t cross apply
     ( values (Next_Schedule_Date), (Expected_Scheduled_Date) 
     ) tt(Future_Schedule_Date)
order by Department_Name, Future_Schedule_Date;

answered Jul 18 '19 at 15:52

Yogesh Sharma

49,870
5
26
52

I have two columns and I want to combine both not by concat just combining one row below the other

2 Answers2