Help with "flattening" a table

Hi All,

I have a database that stores some data from two questionnaires. Table One has been created in a fairly straightforward manner. It has the following fields:

ID, variable1, variable2, variable3, ..., variablen

Table Two is a bit more complicated. I'm guessing whoever created it wanted to do things a bit more "efficiently" and it has the following fields.

ID, variable, response.

If I wanted to, say, extract variables 2, 3 and 5 from Table One I could write:

SELECT ID, variable2, variable3, variable 5 FROM TableOne

I don't know how to do a similar thing with Table 2 using only one statement. ie, I can only think of doing:

SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable = 5

This isn't much good to me as I need the data from Table Two in a single table. Once I've done that, I need to join it to Table One. Ordinarily I'd know how to do that, but I don't know how to do given the format of Table Two.

So... I was wondering if anyone could offer advice on how to "flatten" Table Two?

I would like to be able to do something like:

SELECT t1.ID, t1.variable1, t1.variable2, t2.variable6, t2.variable7

FROM TableOne t1 JOIN TableTwo t2 ON t1.ID = t2.ID

Any help would be much appreciated!

To tell the truth I dont understand the question.

What is the meaning of your tables? What is variable? What is ID? is that a unique key for one of tables? Maybe for both?
As I understood from your question in second table if, say, variable = 3, then the response field womehow relates to the value of variable3 in Table1? Can't you write
select ID, Response from TABLE2 where variable in (2, 3, 5) instead of

"SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable =5"?

And, are you considering any table schema reorganization? Because if the way I understood your table schemas is right, then you should consider some other way to store data-storing variable number in one table as a column name and as a value in the other seems rather illogical to me.

Help Running out of disk space.. filegroup question

Thanks so much Paul.
I'll try adding the second file to the primary filegroup.
Just one question about the proportional fill manner
(relative to the %occupied space. If drive E:/ has 471MBs
free and Drive F:\ (which will hold the new file) has 20
GBs free, will Drive E: still fill up and stop the
database or will the data just flow to the F:/ drive?
of the 2 files). Thanks.
Don

>--Original Message--
>Don,
>yes, you can add the new file to the existing filegroup.
>You won't be able to move objects to this file unless you
>create a new filegroup and add the new file to it,
however
>in your case this is probably not required as when new
>space is required in the database, it is added in
>proportional fill manner (relative to the %occupied space
>of the 2 files).
>HTH,
>Paul Ibison
>.
>
IIRC, data will flow to the new empty file before the old file will expand
beyond its current size. Once they both have the same free space
(relative), I'm not sure how SQL Server will decide which one to grow. To
prolong the wait, just make sure the new file is bigger than the old one.
;-)
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...[vbcol=seagreen]
> Thanks so much Paul.
> I'll try adding the second file to the primary filegroup.
> Just one question about the proportional fill manner
> (relative to the %occupied space. If drive E:/ has 471MBs
> free and Drive F:\ (which will hold the new file) has 20
> GBs free, will Drive E: still fill up and stop the
> database or will the data just flow to the F:/ drive?
> of the 2 files). Thanks.
> Don
> however
|||Thanks Paul and Aaron,
I added the second primary group file to the F:/ Drive and
it looks like the data is going to there much faster than
to the E:/ drive that was filling up. It may be too early
to tell the exact ratio of E to F of data flow. It looks
like this fix saved the day.
Don
>--Original Message--
>IIRC, data will flow to the new empty file before the old
file will expand
>beyond its current size. Once they both have the same
free space
>(relative), I'm not sure how SQL Server will decide which
one to grow. To
>prolong the wait, just make sure the new file is bigger
than the old one.[vbcol=seagreen]
>;-)
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...
filegroup.[vbcol=seagreen]
471MBs[vbcol=seagreen]
filegroup.[vbcol=seagreen]
you[vbcol=seagreen]
space
>
>.
>
|||To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are alread
y using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.
|||Hi there,
One of our drives is completely filled up with a database file and so I would like to move some of the db objects to a newly created data file on another drive. Can anyone give me a hint on how to do this?
Thanks
Andreas
"Sassan Karai" wrote:

> To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
> for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are alre
ady using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.
>
|||Hi
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"Andreas" <Andreas@.discussions.microsoft.com> wrote in message
news:58B7A052-6AAB-4E31-AD6F-A13F29A895CF@.microsoft.com...
> Hi there,
> One of our drives is completely filled up with a database file and so I
would like to move some of the db objects to a newly created data file on
another drive. Can anyone give me a hint on how to do this?[vbcol=seagreen]
> Thanks
> Andreas
>
> "Sassan Karai" wrote:
disable automatic filegrow on the primary datafile[vbcol=seagreen]
created data file on your other partition to grow, and/or add other data
files as need arises. This will prevent SQL from filling up the precious
remaining disk space (maybe you are already using more than 90%) on your E:
drive before the system halts running out of diskspace. I occasionally have
to do this to balance disk load on terabyte size dbs.[vbcol=seagreen]

Help Running out of disk space.. filegroup question

Thanks so much Paul.
I'll try adding the second file to the primary filegroup.
Just one question about the proportional fill manner
(relative to the %occupied space. If drive E:/ has 471MBs
free and Drive F:\ (which will hold the new file) has 20
GBs free, will Drive E: still fill up and stop the
database or will the data just flow to the F:/ drive?
of the 2 files). Thanks.
Don

>--Original Message--
>Don,
>yes, you can add the new file to the existing filegroup.
>You won't be able to move objects to this file unless you
>create a new filegroup and add the new file to it,
however
>in your case this is probably not required as when new
>space is required in the database, it is added in
>proportional fill manner (relative to the %occupied space
>of the 2 files).
>HTH,
>Paul Ibison
>.
>IIRC, data will flow to the new empty file before the old file will expand
beyond its current size. Once they both have the same free space
(relative), I'm not sure how SQL Server will decide which one to grow. To
prolong the wait, just make sure the new file is bigger than the old one.
;-)
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:1ac8701c44f03$2b1996d0$a101280a@.phx
.gbl...[vbcol=seagreen]
> Thanks so much Paul.
> I'll try adding the second file to the primary filegroup.
> Just one question about the proportional fill manner
> (relative to the %occupied space. If drive E:/ has 471MBs
> free and Drive F:\ (which will hold the new file) has 20
> GBs free, will Drive E: still fill up and stop the
> database or will the data just flow to the F:/ drive?
> of the 2 files). Thanks.
> Don
>
> however|||Thanks Paul and Aaron,
I added the second primary group file to the F:/ Drive and
it looks like the data is going to there much faster than
to the E:/ drive that was filling up. It may be too early
to tell the exact ratio of E to F of data flow. It looks
like this fix saved the day.
Don
>--Original Message--
>IIRC, data will flow to the new empty file before the old
file will expand
>beyond its current size. Once they both have the same
free space
>(relative), I'm not sure how SQL Server will decide which
one to grow. To
>prolong the wait, just make sure the new file is bigger
than the old one.
>;-)
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:1ac8701c44f03$2b1996d0$a101280a@.phx
.gbl...
filegroup.[vbcol=seagreen]
471MBs[vbcol=seagreen]
filegroup.[vbcol=seagreen]
you[vbcol=seagreen]
space[vbcol=seagreen]
>
>.
>|||To prevent any further SQL initiated filegrowth on your E: drive you can dis
able automatic filegrow on the primary datafile
for this particular database all together and just allow the newly created d
ata file on your other partition to grow, and/or add other data files as nee
d arises. This will prevent SQL from filling up the precious remaining disk
space (maybe you are alread
y using more than 90%) on your E: drive before the system halts running out
of diskspace. I occasionally have to do this to balance disk load on terabyt
e size dbs.|||Hi there,
One of our drives is completely filled up with a database file and so I woul
d like to move some of the db objects to a newly created data file on anothe
r drive. Can anyone give me a hint on how to do this?
Thanks
Andreas
"Sassan Karai" wrote:

> To prevent any further SQL initiated filegrowth on your E: drive you can d
isable automatic filegrow on the primary datafile
> for this particular database all together and just allow the newly created data fi
le on your other partition to grow, and/or add other data files as need arises. This
will prevent SQL from filling up the precious remaining disk space (maybe you are a
lre
ady using more than 90%) on your E: drive before the system halts running out of diskspace.
I occasionally have to do this to balance disk load on terabyte size dbs.
>|||Hi
[url]http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--[/url
]
Moving SQL Server Databases to a New Location
"Andreas" <Andreas@.discussions.microsoft.com> wrote in message
news:58B7A052-6AAB-4E31-AD6F-A13F29A895CF@.microsoft.com...
> Hi there,
> One of our drives is completely filled up with a database file and so I
would like to move some of the db objects to a newly created data file on
another drive. Can anyone give me a hint on how to do this?[vbcol=seagreen]
> Thanks
> Andreas
>
> "Sassan Karai" wrote:
>
disable automatic filegrow on the primary datafile[vbcol=seagreen]
created data file on your other partition to grow, and/or add other data
files as need arises. This will prevent SQL from filling up the precious
remaining disk space (maybe you are already using more than 90%) on your E:
drive before the system halts running out of diskspace. I occasionally have
to do this to balance disk load on terabyte size dbs.[vbcol=seagreen]