Easiest way of combining multiple fields from different records into one record?

[Steve]

I have a table;

CREATE TABLE theLiterals (
theKey varchar (255) NOT NULL ,
theValue varchar (255) NULL
)
INSERT INTO theLiterals VALUES('default Server','\\MySe rver\')
INSERT INTO theLiterals VALUES('default Share','MyShare \')
INSERT INTO theLiterals VALUES('default Folder','MyFold er\')
INSERT INTO theLiterals VALUES('default File','MyFile.d at')
I then try;

SELECT
defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
FROM theLiterals

and I get;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ NULL NULL NULL
NULL MyShare\ NULL NULL
NULL NULL MyFolder\ NULL
NULL NULL NULL MyFile.dat

but I want it COALESCEd like this;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ MyShare\ MyFolder\ MyFile.dat

....but my syntax is incorrect. Is there an efficient way of doing this.

I want to have a script/UDF where I can say...
GetLiteralsFor( 'defaultServer' ,'defaultShare' ,'defaultFolder ','defaultFile' )
and then my one-row recordset will be...

RS(0) will = '\\MyServer\'
RS(1) will = 'MyShare\'
RS(2) will = 'MyFolder\'
RS(3) will = 'MyFile.dat'

Thanks for any help!

[David Portas]

Just add MIN():

SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue END)
FROM theLiterals

--
David Portas
SQL Server MVP
--

[John Bell]

Hi

Maybe:
SELECT A.defaultServer , B.defaultShare, C.defaultFolder , D.defaultFile
FROM
( SELECT theValue AS defaultServer
FROM theLiterals
WHERE theKey = 'defaultServer' ) A,
( SELECT theValue AS defaultShare
FROM theLiterals
WHERE theKey = 'defaultShare' ) B,
( SELECT theValue AS defaultFolder
FROM theLiterals
WHERE theKey = 'defaultFolder' ) C,
( SELECT theValue AS defaultFile
FROM theLiterals
WHERE theKey = 'defaultFile' ) D

OR

SELECT
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultServer' ) AS defaultServer ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultShare' ) AS defaultShare ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFolder' ) AS defaultFolder ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFile' ) AS defaultFile

You should put a unique or primary key on theKey to make sure only one row
is returned.

John

"Steve" <st*******@cogn izantdesign.com > wrote in message
news:27******** *************** ***@posting.goo gle.com...

I have a table;

CREATE TABLE theLiterals (
theKey varchar (255) NOT NULL ,
theValue varchar (255) NULL
)
INSERT INTO theLiterals VALUES('default Server','\\MySe rver\')
INSERT INTO theLiterals VALUES('default Share','MyShare \')
INSERT INTO theLiterals VALUES('default Folder','MyFold er\')
INSERT INTO theLiterals VALUES('default File','MyFile.d at')
I then try;

SELECT
defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
FROM theLiterals

and I get;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ NULL NULL NULL
NULL MyShare\ NULL NULL
NULL NULL MyFolder\ NULL
NULL NULL NULL MyFile.dat

but I want it COALESCEd like this;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ MyShare\ MyFolder\ MyFile.dat

...but my syntax is incorrect. Is there an efficient way of doing this.

I want to have a script/UDF where I can say...
GetLiteralsFor( 'defaultServer' ,'defaultShare' ,'defaultFolder ','defaultFile' ) and then my one-row recordset will be...

RS(0) will = '\\MyServer\'
RS(1) will = 'MyShare\'
RS(2) will = 'MyFolder\'
RS(3) will = 'MyFile.dat'

Thanks for any help!

[Jim Geissman]

How about this:

SELECT TOP 1
defaultServer = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultServer' ),
defaultShare = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultShare') ,
defaultFolder = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFolder' ),
defaultFile = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFile')
FROM #theLiterals

That returns the desired record:

\\MyServer\ MyShare\ MyFolder\ MyFile.dat

Or you could create a function that takes 4 parameters like 'defaultServer'
and returns a one-record table populated with the results from those 4
SELECTs.

Jim Geissman

[Mischa Sandberg]

Missed the beginning of this thread, but if #theLiterals is not trivially
small,
you get an (avg) 2:1 speedup by doing:

SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM #theLiterals

"Jim Geissman" <ji**********@c ountrywide.com> wrote in message
news:b8******** *************** ***@posting.goo gle.com...

How about this:

SELECT TOP 1
defaultServer = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultServer' ),
defaultShare = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultShare') ,
defaultFolder = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFolder' ),
defaultFile = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFile')
FROM #theLiterals

That returns the desired record:

\\MyServer\ MyShare\ MyFolder\ MyFile.dat

Or you could create a function that takes 4 parameters like 'defaultServer' and returns a one-record table populated with the results from those 4
SELECTs.

Jim Geissman

[Steve]

And first place for minimum reads goes to David Portas!

Thanks everyone for the help. I originally thought doing an aggregate
function
to get rid of NULLS would be inefficient, but by looking at the TRACE
it looks
like it has the most efficient execution plan.

FYI, I listed each of your solutions and the number of reads each
took and some additional questions.

NOTE: The 'theLiterals' table would never be big enough to cause more
than a seconds execution but it is always best to strive for
efficiency anyway. I hope you agree.

-- David Portas
-- 6 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX?
SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue
END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue
END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue
END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue
END)
FROM theLiterals

-- Mischa Sandberg
-- 18 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX or is it the way the CASE-WHEN
is
-- formatted?
SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM theLiterals

-- John Bell
-- 24 reads
SELECT
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultServer' ) AS
defaultServer ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultShare' ) AS
defaultShare ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFolder' ) AS
defaultFolder ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFile' ) AS
defaultFile

-- John Bell
-- 24 reads
SELECT A.defaultServer , B.defaultShare, C.defaultFolder ,
D.defaultFile
FROM
( SELECT theValue AS defaultServer FROM theLiterals WHERE theKey =
'defaultServer' ) A,
( SELECT theValue AS defaultShare FROM theLiterals WHERE theKey =
'defaultShare' ) B,
( SELECT theValue AS defaultFolder FROM theLiterals WHERE theKey =
'defaultFolder' ) C,
( SELECT theValue AS defaultFile FROM theLiterals WHERE theKey =
'defaultFile' ) D

-- Jim Geissman
-- 80 reads
-- Taking off the outside 'FROM theLiterals' returns only the one
record rather
-- than four duplicate records. Therefore the TOP function is then
not needed.
-- So the query becomes the same as John Bell's above with 24 reads
SELECT TOP 1
defaultServer = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultServer' ),
defaultShare = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultShare') ,
defaultFolder = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFolder' ),
defaultFile = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFile')
FROM theLiterals