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 |
|
royv
Constraint Violating Yak Guru
455 Posts |
 Posted - 2003-03-24 : 14:19:53
|
| I have table tblData, defined like this:DataID,DataName,DataValue-------------------------------1,Name,Dylan1,Position,NetworkAdmin2,Name,Chip2,Position,NetworkEngineer3,Name,John3,Position,DeveloperEssentially there is Name data and Position data that I am interested in. I have created a temp table #tmpData like so:Name,Position------------------What I would like to know is if there is a non looping solution to inserting the records from tblData into #tmpData. The DataID column is used to relate the data together. So I want the temp table to look like this when data is finally inserted:Name,Position------------------Dylan, NetworkAdminChip, NetworkEngineerJohn, DeveloperAny ideas on how to do this in a single SELECT with a non looping solution?***************************************Death must absolutely come to enemies of the code! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-24 : 16:01:17
|
| Try:select MAX(case when dataname ='Name' THEN DataValue else ' ' END) as Name, MAX(case when dataname = 'Position' then datavalue else '' END) as PositionFROMtblDataGROUP BY DataID- Jeff |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-24 : 16:06:02
|
| Another way:Select n.dataValue as Name, p.datavalue as PositionFROMtblData nINNER JOINtbdata pon n.dataID = p.dataID WHERE n.Dataname = 'Name' and p.dataname = 'Position'- Jeff |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2003-03-24 : 16:39:07
|
| Thanks Jeff, that worked!***************************************Death must absolutely come to enemies of the code! |
|
|
|
|
|
|
|
|
rows into columns