Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping
Pu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>
|||When you create the temporary table you can also specify the collation used in your user database on the textfields.
create table #abc
(description varchar(100) COLLATE latin1_general_cs_as)
-- Pu Gong wrote: --
same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping
|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
In
> a
> nvarchar
> that
> setting?
>

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helpingPu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>|||When you create the temporary table you can also specify the collation used in your user database on the textfields
create table #ab
(description varchar(100) COLLATE latin1_general_cs_as
-- Pu Gong wrote: --
same as the subject
I have a database have a different collation setting from the temp db. In
SP, I use a temp table, and later, I use the temp table to compare nvarcha
data with an physical table in the database, then I meet an error like tha
Msg 446, Level 16 ..
Cannot resolve collation conflict for equal to operatio
I think I should change the collation setting of the database to th
collation of tempdb and master, but how can I change the collation setting
I have read the BOL, but can't find a way to change the collatio
Thanks a lot for helpin|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > same as the subject.
> > I have a database have a different collation setting from the temp db.
In
> a
> > SP, I use a temp table, and later, I use the temp table to compare
> nvarchar
> > data with an physical table in the database, then I meet an error like
> that
> >
> > Msg 446, Level 16 ...
> > Cannot resolve collation conflict for equal to operation
> >
> > I think I should change the collation setting of the database to the
> > collation of tempdb and master, but how can I change the collation
> setting?
> > I have read the BOL, but can't find a way to change the collation
> >
> > Thanks a lot for helping
> >
> >
>

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helpingPu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>|||When you create the temporary table you can also specify the collation used
in your user database on the textfields.
create table #abc
(description varchar(100) COLLATE latin1_general_cs_as)
-- Pu Gong wrote: --
same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
In[vbcol=seagreen]
> a
> nvarchar
> that
> setting?
>

Can this be changed to a CASE construct?

-- First, some DDL:
CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
/*
What I want to achieve is:
If there are no rows in #TEMP
OR
If any of the expiration dates are NULL
OR
If any of the expiration dates are <= TODAY
return the string 'NOT CLEARED'
ELSE return the string 'CLEARED'
The following works fine:
*/
DECLARE @.ClinicClearanceStatus varchar(12)
SET @.ClinicClearanceStatus = 'CLEARED'
IF
((SELECT COUNT(*) FROM #TEMP) = 0)
OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
OR
((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
BEGIN
SET @.ClinicClearanceStatus = 'NOT CLEARED'
END
SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
/*
I'm just curious if the preceding logic could be changed to a CASE
construct; something like
SELECT CASE WHEN < throw the IF statement in here somehow >
THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
As always, thanks in advance for all your help.
Carl
*/CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
SELECT
CASE
WHEN
(
((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
)
THEN
'NOT CLEARED'
ELSE
'CLEARED' END as status|||Thanks for your reply, Johnny. I checked it out, and it looks like it's
not testing for NULLs correctly; i.e., when I change all of the
expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
NULL dates intact, it returns CLEARED. I suspect it's a very minor
change but I don't know where to make it . . .
Thanks --
Carl
Johnny D wrote:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
>
> SELECT
> CASE
> WHEN
> (
> ((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
> ((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
> )
> THEN
> 'NOT CLEARED'
> ELSE
> 'CLEARED' END as status
>|||You can do it, as JohnyD pointed out, but IMO the if then logic is easier to
follow than a case statement in a select.
Of course it depends on where and how you plan on using this return value.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:uMy7B9CgGHA.1792@.TK2MSFTNGP03.phx.gbl...
> -- First, some DDL:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
> /*
> What I want to achieve is:
> If there are no rows in #TEMP
> OR
> If any of the expiration dates are NULL
> OR
> If any of the expiration dates are <= TODAY
> return the string 'NOT CLEARED'
> ELSE return the string 'CLEARED'
> The following works fine:
> */
> DECLARE @.ClinicClearanceStatus varchar(12)
> SET @.ClinicClearanceStatus = 'CLEARED'
> IF
> ((SELECT COUNT(*) FROM #TEMP) = 0)
> OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
> OR
> ((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
> BEGIN
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> END
> SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
> /*
> I'm just curious if the preceding logic could be changed to a CASE
> construct; something like
> SELECT CASE WHEN < throw the IF statement in here somehow >
> THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
> As always, thanks in advance for all your help.
> Carl
> */|||I think it is just a typo.
Change the last "IS NULL" in the case function to " > 0"
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply, Johnny. I checked it out, and it looks like it's
> not testing for NULLs correctly; i.e., when I change all of the
> expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
> NULL dates intact, it returns CLEARED. I suspect it's a very minor
> change but I don't know where to make it . . .
> Thanks --
> Carl
> Johnny D wrote:|||Thanks for the clarification Jim -- that did it.
I appreciate your and Johnny's time.
Carl
Jim Underwood wrote:
> I think it is just a typo.
> Change the last "IS NULL" in the case function to " > 0"
> "Carl Imthurn" <nospam@.all.thanks> wrote in message
> news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
>
>
>|||On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
(snip)
>What I want to achieve is:
>If there are no rows in #TEMP
>OR
>If any of the expiration dates are NULL
>OR
>If any of the expiration dates are <= TODAY
>return the string 'NOT CLEARED'
>ELSE return the string 'CLEARED'
Hi Carl,
This can be greatly simplified:
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
Or, if you really want a CASE:
SET @.ClinicClearanceStatus =
(SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
FROM #TEMP)
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Hugo --
First, thanks for posting back to me.
I tested your statement with COALESCE and it worked for conditions 2 and
3 ( NULL expiration dates and expiration dates < = today ) but failed on
condition 1 (empty table). In other words, if I have an empty table, the
SELECT statement returns CLEARED where it should return NOT CLEARED.
Here's some DDL if you have a chance (and desire) to pursue this further:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.TEST')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.TEST
CREATE TABLE TEST (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ1', '2006-05-31')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ2', '2006-05-29')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ3', '2007-05-01')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ4', '2006-05-25')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ5', '2006-05-26')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ6', '2005-01-01')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ7')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ8')
DECLARE @.ClinicClearanceStatus varchar(20)
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
Thanks for your help Hugo - I appreciate it.
Carl
Hugo Kornelis wrote:
> On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
> (snip)
>
>
> Hi Carl,
> This can be greatly simplified:
> IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> Or, if you really want a CASE:
> SET @.ClinicClearanceStatus =
> (SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
> CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
> FROM #TEMP)
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>|||Carl,
You could change the last section to this...
DECLARE @.ClinicClearanceStatus varchar(20)
IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
'') <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
HTH
Barry|||Barry --
That did it! Thanks much -- I appreciate your time.
Have a great day.
Carl
Barry wrote:
> Carl,
> You could change the last section to this...
>
> DECLARE @.ClinicClearanceStatus varchar(20)
> IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
> '') <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> SELECT @.ClinicClearanceStatus
>
> HTH
> Barry
>

Can somone crack this query?

This is a simplification of a query I'm attempting to write at the
moment. I can do it with a temp table, but I think it can be done in
one query. 15 minutes of fame to the correct answer.

3 tables:
Table Continent
================Columns==================
Columns:
ID (primary key)
Name
================Data=====================
NA,North America
SA,South America

Table Country
================Columns==================
ContinentID (foreign key)
Name
Population
================Data=====================
NA,United States,295734134
NA,Canada,32805041
SA,Brazil,186112794
SA,Peru, 27269482

Can someone come up with the query to return the country with the
lowest population in each continent, ie:
CA,Canada,32805041
SA,Peru,27269482

Need to have the country name in the query (would be very easy if you
didn't!).--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

SELECT ContinentID, Name, Population
FROM Country As C
WHERE Population = (SELECT Min(Population) FROM Country
WHERE ContinentID = C.ContinentID)
ORDER BY ContinentID, Name

Do I get a Gold Star?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtMcJYechKqOuFEgEQK58wCgyTJWEzJIGiiynKX9fRrHOA n2/5AAnjhF
XjBxajYEv1Afl+VAJVYY9DLI
=3lQX
--END PGP SIGNATURE--

russ wrote:
> This is a simplification of a query I'm attempting to write at the
> moment. I can do it with a temp table, but I think it can be done in
> one query. 15 minutes of fame to the correct answer.
> 3 tables:
> Table Continent
> ================Columns==================
> Columns:
> ID (primary key)
> Name
> ================Data=====================
> NA,North America
> SA,South America
> Table Country
> ================Columns==================
> ContinentID (foreign key)
> Name
> Population
> ================Data=====================
> NA,United States,295734134
> NA,Canada,32805041
> SA,Brazil,186112794
> SA,Peru, 27269482
> Can someone come up with the query to return the country with the
> lowest population in each continent, ie:
> CA,Canada,32805041
> SA,Peru,27269482
> Need to have the country name in the query (would be very easy if you
> didn't!).|||you most certainly do! correlated sub-query, ended up doing the same
myself.

thanks for your help

Can someone help me with this?

Hi,
I have two tables one holds data (#temp) and the other one is a reference table(#temp2). I would like to grab date from #temp table with #temp2 table column value as my data column. If you look at my example below, you should see what I mean

--my data table
create table #temp (misc1 varchar(10), misc2 varchar(10) , misc3 varchar(10))
insert into #temp values ('Manager','NA','Texas')
insert into #temp values ('VP','EMA','London')

--my reference table
create table #temp2 (Property varchar(10), Value varchar(10))
insert into #temp2 values ('misc1','Title')
insert into #temp2 values ('misc2','Region')
insert into #temp2 values ('misc3','Location')

--my pseudo query, ofcourse this does not work and this is where I need
--help
select
misc1 as (select value from #temp2 where property='misc1'),
misc2 as (select value from #temp2 where property='misc2'),
misc3 as (select value from #temp2 where property='misc3')
from #temp2

--To get the results like below
Title Region Location
Manager NA Texas
VP EMA London
Thanks to all for helping!! :)You would have to use a technique called a correlated subquery, where the subqueries in your SELECT clause reference the primary key of the outer table in their WHERE clause.
And I have to tell you, this is database design (known as an EAV, or Entity Attribute Value schema) is one of the WORST database designs possible. This is extremely inefficient, so I hope you aren't going to be loading a lot of data into it or expecting good performance.