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 |
|
sqlteamForummer
Starting Member
13 Posts |
 Posted - 2007-08-02 : 11:35:50
|
| Can anyone please help me with the following:I have a table which has fields called defid, datavalue, pid etc.I have to create a new table which should have fields: CustID, datavalue[SocSec]{note:this is the value in d.datavalue when defid is 1004), datavalue[FirstName]{note: this is the value in d.datavalue when defid is 1006}, datavalue[LastName]{note: this is the value in d.datavalue when defid is 1007}, [FullName] {note: this is the value I should get by joining the datavalues (lastname and firstname) when defifis 1006 and 1007) ie. ex: FirstNameLastName.COULD ANYONE PLEASE HELP ME ON THIS. DATA QUERY is as follows:Thank you in advance.SELECT DISTINCT c.CustID, d.datavalue, f.nameFROM dbo.Customers cINNER JOIN dbo.Date7 d ON d.pid = c.rootINNER JOIN dbo.fielddisc f ON f.defid = d.defidWHERE c.separatedate BETWEEN @StartDate AND @EndDateAND c.DateTab = 7 AND d.defid IN (1004, 1006, 1007, 1009) AND c.CustID = 10 AND c.root = 8472ORDER BY c.rootSample Data is:Data for the table which has fields called defid, datavalue, pid etc.NOTE: the order for the data may not be the samepid defid datavalue8472 1004 500-324-1444 {note: SocSec} 8472 1006 Andrew {note: FirstName}8472 1007 McCormick {note: LastName}8472 1009 California {note: State}2552 1009 Newyork {note: State}2552 1006 Fred {note: FirstName}2552 1004 255-753-6936 {note: SocSec} 2552 1007 Dawson {note: LastName}-----------------------------------------Data for the new table should look likeCustID SocSec FirstName LastName FirstNameLastName |
|
|
|
|
|
|
|
How to create separate fields from the data?