SQL Help

[bdjb]

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

 

[PHV]

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

 

[JarlH]

Storing data like that will only cause problems. If possible, redesign the database.

 

[execretor]

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...