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.
Author |
Topic |
archive
Starting Member
2 Posts |
Posted - 2013-01-29 : 13:22:03
|
I am having trouble joining information from a spreadsheet provided to me by another department with an existing table.The spreadsheet lays out work queues an employee is responsible for with the queues broken down by client.The problem is, the client breakdown is done by putting each client in their own field.So the table looks like thisQueue | ACA | BCRNeeds Review | Rob | BenBundling | Jay | JenI have another table that has Queue and Client in their own fields.Queue | ClientNeeds Review | ACANeeds Review | BCRBundling | ACABundling | BCRI would like to join the data to end up with a table like the following.Queue | Client | EmployeeNeeds Review | ACA | Rob Needs Review | BCR | BenBundling | ACA | Jay Bundling | BCR | JenIs it possible to do this without changing the format of the spreadsheet? Thank you! |
|
tm
Posting Yak Master
160 Posts |
Posted - 2013-01-29 : 14:02:36
|
Below is script to Unpivot the first table and it gives the result as you mentioned.. declare @t table ([Queue] varchar(30) , ACA varchar(10), BCR varchar(10))insert into @tselect 'Needs Review' ,'Rob' , 'Ben'unionselect 'Bundling' , 'Jay' , 'Jen'--Unpivot the table.SELECT [Queue], Client, EmployeeFROM (SELECT [Queue], ACA, BCR FROM @t) AS pUNPIVOT (Employee FOR Client IN (ACA, BCR))AS unpvtResult:Queue Client EmployeeNeeds Review ACA RobNeeds Review BCR BenBundling ACA JayBundling BCR Jen |
|
|
archive
Starting Member
2 Posts |
Posted - 2013-01-30 : 15:51:22
|
That worked wonderfully. Thank you very much. |
|
|
|
|
|