CODEWITHSUNDEEP
sql
CHANDRU
CHANDRU

Reputation: 37

How to merge values of a row to two columns in sql

Table 1 

ID  Employee ID
1   200678
2   3000033
3   4454545
4   2323232
5   43345563
6   8473434
7   234234
8   766566

Table 2 

ID  year    Manger id
1   2015    8
1   2014    7
2   2015    5
2   2014    6
3   2015    5
3   2014    4
4   2015    1
4   2014    2

Output 

ID  Current year Manger ID  Prev Manager id
1   766566                  234234
2   43345563                8473434

Upvotes: 0

Views: 47

Answers (1)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

In SQL-Server you can do something like this:

SAMPLE DATA

CREATE TABLE #Table1
(
ID INT,
[Employee ID] INT
)
INSERT INTO #Table1 VALUES
(1,   200678   ),
(2,   3000033  ),
(3,   4454545  ),
(4,   2323232  ),
(5,   43345563 ),
(6,   8473434  ),
(7,   234234   ),
(8,   766566   )

CREATE TABLE #Table2
(
ID  INT,
years NVARCHAR(100),   
[Manger id] INT
)
INSERT INTO #Table2 VALUES
(1,   '2015',    8),
(1,   '2014',    7),
(2,   '2015',    5),
(2,   '2014',    6),
(3,   '2015',    5),
(3,   '2014',    4),
(4,   '2015',    1),
(4,   '2014',    2)

QUERY

SELECT t2.ID, 
       MAX(CASE WHEN years = YEAR(GETDATE()) THEN [Employee ID] END) AS [Current year Manger ID],
       MIN(CASE WHEN years = YEAR(GETDATE())-1 THEN [Employee ID] END) AS [Prev Manager id]
FROM #Table1 t1
JOIN #Table2 t2 ON t1.ID = t2.[Manger id]
GROUP BY t2.ID

OUTPUT 

ID  Current year Manger ID  Prev Manager id
1   766566                  234234
2   43345563                8473434
3   43345563                2323232
4   200678                  3000033

DEMO

You can test It at: SQL FIDDLE

Upvotes: 2

Related Questions