SQL Server 2008 unpivot columns

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

SQL Server 2008 unpivot columns

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-10 : 16:05:47

I am working with a 22 columns table where the first two columns are unique, item and group id, and the remaining 20 columns are department ID's ranged from 1-20.
What I am trying to do here is that to convert the 22 column table into a three column table using the item ID, Group ID and Department (Repeat a new row for any additional department).

Any assistant or direction is greatly appreciated.
[code]
create table #table1
(
ItemID varchar(10),
GroupId varchar(10),
Dep1 varchar (10),
Dep2 varchar (10),
Dep3 varchar (10),
Dep4 varchar (10),
Dep5 varchar (10),
Dep6 varchar (10),
Dep7 varchar (10),
Dep8 varchar (10),
Dep9 varchar (10),
Dep10 varchar (10)
)

insert into #table1 (ItemID,GroupId,Dep1,Dep2,Dep3,Dep4,Dep5,Dep6,Dep7,Dep8,Dep9,Dep10) values ('1000259','111111','83R02-0190','9622A- All','88R02-0191','','','','','','','');
insert into #table1 (ItemID,GroupId,Dep1,Dep2,Dep3,Dep4,Dep5,Dep6,Dep7,Dep8,Dep9,Dep10) values ('1000232','222222','887N7-0202','','','','','','','','','');
insert into #table1 (ItemID,GroupId,Dep1,Dep2,Dep3,Dep4,Dep5,Dep6,Dep7,Dep8,Dep9,Dep10) values ('1000121','333333','55677-1243','55677- All','55677-0191','55677-l01','','','','','','');
insert into #table1 (ItemID,GroupId,Dep1,Dep2,Dep3,Dep4,Dep5,Dep6,Dep7,Dep8,Dep9,Dep10) values ('1000453','444444','71D02-0324','71D02- All','','','','','','','','');

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-10 : 17:08:44

[code]
select ItemID
,GroupID
,left(deps,10) [department]
,dep [val]
from #table1
unpivot( dep for deps in ([Dep1],[Dep2],[Dep3],[Dep4],[Dep5],[Dep6],[Dep7],[Dep8],[Dep9],[Dep10])) u

OUTPUT:
ItemID GroupID department val
---------- ---------- ---------- ----------
1000259 111111 Dep1 83R02-0190
1000259 111111 Dep2 9622A- All
1000259 111111 Dep3 88R02-0191
1000259 111111 Dep4
1000259 111111 Dep5
1000259 111111 Dep6
1000259 111111 Dep7
1000259 111111 Dep8
1000259 111111 Dep9
1000259 111111 Dep10
1000232 222222 Dep1 887N7-0202
1000232 222222 Dep2
1000232 222222 Dep3
1000232 222222 Dep4
1000232 222222 Dep5
1000232 222222 Dep6
1000232 222222 Dep7
1000232 222222 Dep8
1000232 222222 Dep9
1000232 222222 Dep10
1000121 333333 Dep1 55677-1243
1000121 333333 Dep2 55677- All
1000121 333333 Dep3 55677-0191
1000121 333333 Dep4 55677-l01
1000121 333333 Dep5
1000121 333333 Dep6
1000121 333333 Dep7
1000121 333333 Dep8
1000121 333333 Dep9
1000121 333333 Dep10
1000453 444444 Dep1 71D02-0324
1000453 444444 Dep2 71D02- All
1000453 444444 Dep3
1000453 444444 Dep4
1000453 444444 Dep5
1000453 444444 Dep6
1000453 444444 Dep7
1000453 444444 Dep8
1000453 444444 Dep9
1000453 444444 Dep10
[/code]

Be One with the Optimizer
TG

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-10 : 19:43:17

Thank you! TG
if I need to exclude empty values from returning for val (where val <> ''),any suggestion?

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-10 : 19:46:15

Ignore my last question. I figure that out. All working perfect!!
where u.dep <> ''

Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources