CODEWITHSUNDEEP
sqlsql-server
Paul
Paul

Reputation: 147

Split into multiple rows in sql

My table looks like - enter image description here

And I want to achieve like this - enter image description here

Please help the SQL

Upvotes: 0

Views: 63

Answers (3)

Alexander Bell
Alexander Bell

Reputation: 7918

Provided the Column Names are static, you probably may use the UNION SELECT query, something like the following:

Select CollegeID, DeptID, EmpID, "2017" As Y, "1" As Mnth, [Act201701] As Act, [Pred201701] As Pred from [SomeTable]
UNION
Select CollegeID, DeptID, EmpID, "2017" As Y, "2" As Mnth, [Act201702] As Act, [Pred201702] As Pred from [SomeTable]
UNION
Select CollegeID, DeptID, EmpID, "2017" As Y, "3" As Mnth, [Act201703] As Act, [Pred201703] As Pred from [SomeTable]

where SomeTable is your Table Name.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Use apply:

select t.collegeid, t.deptid, t.empid, v.yr, v.mnth, v.act, v.pred
from t outer apply
     (values (act201701, pred201701, 2017, 1),
             (act201702, pred201702, 2017, 2),
             (act201703, pred201703, 2017, 3),
     ) v(act, pred, yr, mnth);

You can also do the same thing using unpivot. However, apply implements lateral joins, which are much more powerful than merely unpivoting data.

Upvotes: 2

LNT
LNT

Reputation: 124

replace the table name and try

insert into yourTableName (CollegeID, DeptID, EmpID, Yr, Mnth, Act, Pred) values (234, 34, 4, 2017, 1, 6131.86, 6131.82)

Upvotes: 0

Related Questions