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 |
|
sqlneve
Starting Member
16 Posts |
 Posted - 2008-12-31 : 12:28:09
|
| I have a table with several distinct pieces of information in column format that I need in rows. What I have now looks as follows...ID Date Permit12 1/1/08 126512 6/7/84 8876What I want to do is reconstruct the table using a sql query so it appears as followsID Date1 Permit1 Date2 Permit212 1/1/08 1265 6/7/84 8876Any hope???Thanks! |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2008-12-31 : 12:53:13
|
| Seems to me the easiest way would be to create a new table with the structure you want.I guess these would be your columns based on your postID DATE1 PERMIT1 DATE2 PERMIT2Then get a count of the number of rows so you can write a stored proc to do the following:if counter is oddbeginadd the ODD number rows to columns DATE1 and PERMIT1 endelsebeginadd the EVEN rows to columns DATE2 and PERMIT2endThen delete the old table.Once done you simply drop the old table and use your new one. I am sure there are more elegant solutions.This is just one way to do it based on your specs..r&r |
 |
|
|
sqlneve
Starting Member
16 Posts |
Posted - 2008-12-31 : 13:02:16
|
| I'm totally with you that's what i was thinking at first but... it isn't always the case that there are 2 instances of permits and dates sometimes there are as many as 5 sometimes as few as 2. I think i'm going to have to do this one by one but i'm fighting it!Thanks for your suggestion |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2008-12-31 : 13:19:22
|
| This is a bad idea for a table structure. Why would you want to do this?Jim |
|
|
|
sqlneve
Starting Member
16 Posts |
Posted - 2008-12-31 : 13:38:40
|
| I want the records all on one row to create details in database reports I run so i'm looking for a way to display the details of the records without displaying information redundantly. |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2008-12-31 : 14:14:38
|
| Ok my next suggestion involves using a 1NF (first normal form) approach..Are these records tied together somehow? What is their relationship such that you can group them together? Use that relationship to create a normalized table where you can reference the records by a candidate key (aka primary key or composite key) relationship.This way in your report you simply reference that key and display the information however you want.If you cannot normalize for some reason then you may wish to consider using PARTITION syntax.Otherwise I agree with the previous post that it is not the best table structure to use. It does work however for a quick and dirty solution (ie.. naive).r&r |
|
|
|
sqlneve
Starting Member
16 Posts |
Posted - 2008-12-31 : 15:58:35
|
well instead of sqlneve just call me sqlnaive instead. thanks for the suggestion! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-01 : 13:10:31
|
quote:
Originally posted by sqlneve
I have a table with several distinct pieces of information in column format that I need in rows. What I have now looks as follows...ID Date Permit12 1/1/08 126512 6/7/84 8876What I want to do is reconstruct the table using a sql query so it appears as followsID Date1 Permit1 Date2 Permit212 1/1/08 1265 6/7/84 8876Any hope???Thanks!
are you certain about number of records you will have at a max for a ID value?if not, you might need to use dynamic sql. |
|
|
|
|
|
|
Table Reconstruction