Pivot data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot data

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-10-08 : 17:16:39

I have the data on the following layout


Emp_ID|Dependet_ID|LName|FName|DOB|SEX|adr|City|State|Zip|FName_Dep|LName_Dep|SEX_Dep|DOB|relation
123456|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Alan| Smith|M|2013/08/10|Child
123456|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Jen| Smith|F|2013/08/10|Child
123456|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Jantt| Smith|F|2013/08/10|Spouse
678910|678910-01|White|Jeremy|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Tarik| White|M|2013/08/10|Child
678910|678910-01|white|Jeremy|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Alan| White|M|2013/08/10|Child
558910||White|Jeremy|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|||||

I need to unpivot/pivot the data to:


Emp_ID|relations|Dependet_ID|LName|FName|DOB|SEX|adr|City|State|Zip
123456|employee|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120
123456|Child|123456-01|Alan| Smith|2013/08/10|M|happy st| #00|TESTCITY|XX|20120
etc...

Any suggestions please?

Thanks!

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 23:54:01

can you explain your output? How did you get the row with relations value as employee for Emp_ID 123456? I cant see that in your sample data posted above. I can see only 3 rows with relation value child,child and Spouse.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

divya.ce
Starting Member

16 Posts

Posted - 2013-10-09 : 05:02:19

declare @emp table (Emp_ID int ,Dependet_ID varchar(100), LName varchar(20) ,
FName varchar(20) , DOB varchar(20) ,SEX varchar(20) , adr varchar(20) ,City varchar(20) ,State varchar(20) ,
Zip varchar(20) , FName_Dep varchar(20) ,LName_Dep varchar(20) ,SEX_Dep varchar(20) , relation varchar(20) )

insert into @emp
select '123456','123456-01','smith','Rob','2013/08/10','M','happy st','TESTCITY','XX','20120','Alan',' Smith','M', 'Child' union all
select '123456','123456-01','smith','Rob','2013/08/10','M','happy st','TESTCITY','XX','20120','Jen',' Smith','F' ,'Child' union all
select '123456','123456-01','smith','Rob','2013/08/10','M','happy st','TESTCITY','XX','20120','Jantt',' Smith','F' ,'Spouse' union all
select '678910','678910-01','White','Jeremy','2013/08/10','M','happy st','TESTCITY','XX','20120','Tarik',' White','M' ,'Child' union all
select '678910','678910-01','white','Jeremy','2013/08/10','M','happy st','TESTCITY','XX','20120','Alan',' White','M' ,'Child'

--Emp_ID|relations|Dependet_ID|LName|FName|DOB|SEX|adr|City|State|Zip
--123456|employee|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120
--123456|Child|123456-01|Alan| Smith|2013/08/10|M|happy st| #00|TESTCITY|XX|20120
--etc...

select distinct e.Emp_ID,'Employee' as relation,e.Dependet_ID,e.LName,e.FName,e.dob,e.SEX,e.adr,e.city,e.state,e.zip, Level =1
from @emp e
union all
select distinct e.Emp_ID,e.relation,e.Dependet_ID,e.LName_Dep,e.FName_Dep,e.dob,e.SEX_Dep,e.adr,e.city,e.state,e.zip,Level =2
from @emp e
order by 1,level

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-10-09 : 09:22:13

Input:
All the left records are employees.
All the Dependents (child/spouse) are place at the end of the employee record.
The same employee is repeating for every child/spouse he has.

123456|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Alan| Smith|M|2013/08/10|Child
123456|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Jen| Smith|F|2013/08/10|Child
123456|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120|Jantt| Smith|F|2013/08/10|Spouse

Output:
Avoid repeating employees
Align dependents (child/spouses) vertically NOT horizontally.

Emp_ID|relation|Dependet_ID|LName|FName|DOB|SEX|adr|City|State|Zip
123456|employee|123456-01|smith|Rob|2013/08/10|M|happy st| #00|TESTCITY|XX|20120
123456|Child|123456-01|Alan| Smith|2013/08/10|M|happy st| #00|TESTCITY|XX|20120
123456|Child|123456-01|Jen| Smith|2013/08/10|M|happy st| #00|TESTCITY|XX|20120
123456|spouse|123456-01|Jantt| Smith|2013/08/10|M|happy st| #00|TESTCITY|XX|20120

Please let me know if you need more explanation.

Thanks!

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources