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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2004-05-10 : 16:25:05
|
| Anyone know of a quick way to "de-normalize" the data below:PersonID/Item1/11/21/52/32/83/93/103/13/2into a one-record per row result set like this, where number of columns will "grow" as necessary to the max number of cols needed based on the Person with the most Items:PersonID/Item1/Item2/Item3/Item41/1/2/52/3/83/9/10/1/2I have no unique ID-per-record I can key on, i.e. the situation is as you see it.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-10 : 16:28:11
|
| Why do you want to do this? What is the maximum number of Items that a person can have? If there isn't a maximum, how do you know how many columns you need in the table definition? You wouldn't want to change the table each time someone reaches the limit.Or do you just want the result set denormalized and not the actual table?Tara |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-10 : 16:53:56
|
| This might do it, or point you in the right direction.I'm with Tara on this one, this is a kinda weird result set that could cause problems down the road.http://www.sqlteam.com/item.asp?ItemID=2955Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2004-05-11 : 13:30:32
|
| This sounds like a job for the AWK scripting language. I would post it on the Tek-Tips forum.~ Shaun MerrillSeattle, WA |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-05-11 : 13:53:40
|
| Tara,sorry if I wasn't clear. Yes, I want to de-normalize the result set, not the base table.I mis-spoke earlier: there are 62 possible unique item selections per person, but in reality one wouldn't expect a person to have more than 10 items. What I'm trying to do here is to get this result set de-normalized in a VIEW, then query this VIEW as part of a larger query that will return one record per person.thx |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-05-11 : 14:18:33
|
| You'll have to create a loop, find the MAX User Id and work backwards doing SQL Inserts into a table and setting a varianbleSELECT @x = @x + Item FROM TABLE WHERE Person_Id = @PersonIdINSERT INTO newTable(PersonId, ItemId)SELECT @PersonId, @xsomething like that....Brett8-) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-11 : 14:27:35
|
| [url="http://www.sqlteam.com/item.asp?ItemID=11021"] Converting Multiple Rows into a CSV String (Set Based Method) [/url]Jay White{0} |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-05-11 : 15:33:46
|
| Jay,that is one cool script! thx!! |
|
|
|
|
|
|
|
|
can I "de-normalize" to get one row per Person?