SQL Help
SQL Help
(OP)
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
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
Talk To Other Members
RE: SQL Help
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: SQL Help
RE: SQL Help
SQL is not the best solution in this case.
You can use .NET or any other language...