Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Help

Status
Not open for further replies.
bdjb

bdjb

Technical User
Oct 29, 2002
292
US
Hello,
I have data in a table that is identified by a fieldnum, if fieldnum = 1 then the answer column is a zip code. Each "chunk" of data also has an identifying number that shows which group goes together. Is there a way in sql code to "flatten" the data? Move each pice to a column so that each group is in one record?

Example:
Answer EntityNum FieldNum
44070 1112 1
OH 1112 2
Hamilton 1112 3
912 Street 1112 4
Fixed 1112 5
Joe Smith 1112 6
44612 1113 1
MI 1113 2
Wayne 1113 3
555 Street Ave 1113 4
Variable 1113 5
Sue Brown 1113 6

What I want is:
EntityNum Zip Address Name Type County
1112 44070 12 street Fixed Joe Smith
 
Sort by date Sort by votes
Something like this ?
SELECT A.EntityNum, A.Answer AS Zip, B.Answer AS State, C.Answer AS County, D.Answer AS Address, E.Answer AS Type, F.Answer AS Name
FROM yourTable A
INNER JOIN yourTable B ON A.EntityNum = B.EntityNum
INNER JOIN yourTable C ON A.EntityNum = C.EntityNum
INNER JOIN yourTable D ON A.EntityNum = D.EntityNum
INNER JOIN yourTable E ON A.EntityNum = E.EntityNum
INNER JOIN yourTable F ON A.EntityNum = F.EntityNum
WHERE A.FieldNum = 1 AND B.FieldNum = 2 AND C.FieldNum = 3 AND D.FieldNum = 4 AND E.FieldNum = 5 AND F.FieldNum = 6

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Storing data like that will only cause problems. If possible, redesign the database.
 
Upvote 0 Downvote
You can write a simple script to migrate data in a new table.
SQL is not the best solution in this case.
You can use .NET or any other language...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
410
carp
carp
manbob
  • Locked
  • Question
need a help with sub query getting group ereor, please correct
Replies
2
Views
412
carp
carp
jchewsmith
  • Locked
  • Question
SQL Syntax 1
Replies
1
Views
362
JarlH
JarlH
soushi01
  • Locked
  • Question
Nested For Loop update statement (pl/sql) 1
Replies
2
Views
298
soushi01
soushi01
excelcus
  • Locked
  • Question
SQL Query Help
Replies
4
Views
280
excelcus
excelcus

Part and Inventory Search

Sponsor

Back
Top