| Author |
Topic |
|
u2p_inst
Yak Posting Veteran
78 Posts |
 Posted - 2003-03-19 : 04:19:24
|
| dear all,i have output of table like this:field 1 | field 2 --------------------fname | joe lname | dimaz address | west fname | andylname | markaddress | indiafname | tenylname | firmanaddress | southis posible to create query and the output like this:fname | lname | address------------------------joe | dimaz | westandy | mark | indiateny | firman | southoh |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-19 : 04:35:10
|
| How do you know the three recordsjoe, dimaz, westgo together?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-03-19 : 05:40:19
|
| post table design. You probably need to join, but we need to know what the structure looks like to be able to look at it. If you have that data, and it relates to itself, and it is in one table, I'm guessing you've got some kind of recursive relationship going, and you will need to traverse it in your query ...*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
u2p_inst
Yak Posting Veteran
78 Posts |
Posted - 2003-03-19 : 06:59:46
|
| if you see the field1, the field have the data always:fname,lname, address continouslyoh |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-19 : 07:31:24
|
| True, but the problem is that there's no relationship between Joe and Dimaz. The order of the rows is not guaranteed in SQL Server or any other database product. If you don't have a value that they share, you cannot associate the first name, last name, or addresses.You're not using a spreadsheet anymore. Row order is not meaningful in a relational database. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-19 : 13:11:33
|
| if you see the field1, the field have the data always: fname,lname, address continously Not it doesn't - that's just how it has happenned to display.Try putting a clustered index on field1 and see what happens.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-19 : 13:54:47
|
| Are you hiding some of the table columns from us and you want to do a CROSS-TAB ???Does each group of 3 records have a common field that groups them together?Something like:PersonID | field 1 | field 2 -------------------- 1 | fname | joe 1 | lname | dimaz 1 | address | west 2 | fname | andy 2 | lname | mark 2 | address | india 3 | fname | teny 3 | lname | firman 3 | address | south ????If so, then you can get what you are looking for ... but you need a common field to relate each group of 3 records together as in my example.makes sense?- Jeff |
|
|
|
u2p_inst
Yak Posting Veteran
78 Posts |
Posted - 2003-03-20 : 04:51:49
|
| how can i create personID?oh |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-20 : 08:26:58
|
| YOu can't create a field like this -- you have it or you don't. Your data really has only 2 columns like you've shown? There's no other columns providing some more info that allows us to group these sets of 3 rows together?All joking aside, if that's the case, fire up MS Excel and stick with it... SQL is a whole different story and you need to do a lot of reading up about databases and such.- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-20 : 11:02:02
|
| Script the table and post the ddl...only way we can see what you're up against...mostly because I'm sure no one can see the value of the table results you posted to an application or user. What would you do with it? It is highly non relational (or heirachal for that matter).Brett8-) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-20 : 20:34:07
|
| Can't vouch for whether this works properly, but it's worth a shot.Import your data into a table (myTable, for example) and then run this statement:SELECT Field1 AS Type, Field2 AS Value, Identity(int, 0,1) AS ID INTO #temp FROM myTableThen run:SELECT Max(CASE Type WHEN 'fname' THEN Value END) AS Fname,Max(CASE Type WHEN 'lname' THEN Value END) AS Lname,Max(CASE Type WHEN 'address' THEN Value END) AS AddressINTO NamesAddressesFROM #tempGROUP BY (ID / 3)When you're done, you should have a table named NamesAddresses which contains the data in the desired structure. Still, there's no guarantee the names will match up...and if they don't, you'll see exactly why the structure you originally had can't be easily converted without a common value to bind each row together. |
|
|
|
|
create transact-sql