Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

concatenate problem

Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

ThanksOriginally posted by Devinder Gera
Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

Thanks
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint|||If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint [/SIZE][/QUOTE]

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks|||Originally posted by Devinder Gera
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks [/SIZE][/QUOTE]

No worries guys it started working. I was just updating at wrong time. I changed the location of update now its working fantastic

Thanks a million Saint

Concatenate Lname and Fname Columns

I'm using Access 2002 and Sql 2000.
I have a Lname and Fname columns that I'm try to concatenate. I'm trying to
use Lname + ', ' + Fname in a View. But Lname is the things that displays.
What am I doing wrong?
Thanks for the help,
Paulpjscott wrote:
> I'm using Access 2002 and Sql 2000.
> I have a Lname and Fname columns that I'm try to concatenate. I'm
> trying to use Lname + ', ' + Fname in a View. But Lname is the things
> that displays.
> What am I doing wrong?
> Thanks for the help,
> Paul
Are you using fixed-length character columns? If so, you'll need to trim
the data. For example:
create table #Names (
LName1 CHAR(20),
LName2 VARCHAR(20),
FName1 CHAR(20),
FName2 VARCHAR(20) )
go
Insert Into #Names Values (
'Gugick', 'Gugick','David','David')
Insert Into #Names Values (
'Smith', 'Smith','Dan','Dan')
go
Select
LName1 + ', ' + FName1 as "Fixed-Length Name",
LName2 + ', ' + FName2 as "Variable-Length Name",
RTRIM(LName1) + ', ' + RTRIM(FName1) as "Fixed-Length Name - Trimmed"
from #Names
Go
Drop Table #Names
go
David Gugick - SQL Server MVP
Quest Software

Concatenate Input Columns

Hi,

In my data flow taks, The Source data is coming from AS400 has 4 columns,

I need to achieve the followings and require your help.

1. Generate a new column which will be combination of concating these 4 columns.

2. Need to add an extra row for Header & Footer.

Please Help.

Concatenation is achieved using the Derived Column component.

Adding your own header and footer rows is a bit more difficult because they need to have the same metadata as the data row. For this reason, concatenate all columns together so as to make a single, very wide, column. You can then use the UNION ALL component to put your header, data and footer together. The header and footer will probably be created using a source script component - though I'll leave that up to you.

-Jamie

|||

Re. Regarding Derived Column component

Source Columns

Col1 smallint, Col2 smallint, Col3 Decimal(12,4), Col4 Decimal(16,4)

Data will be the new derived column

Data = @.[User::TimeStamp] + "D" + Col1 + Col2 + Col3 + Col4

I need to know how to use Cast Operators as Data is DT_STR and Col1 & Col2 are smallint.

Thanks

|||

Look in the top right hand corner of the Derived Column UI. All the type cast operators are in there.

They are also all in BOL which you obviously haven't looked at:

http://msdn2.microsoft.com/en-us/library/ms141260.aspx

http://msdn2.microsoft.com/en-us/library/ms141704.aspx

BOL should ALWAYS be your first port of call. Not this forum!

-Jamie

|||

I have created two source script component one for Header and one for Footer. I am using OLE DB source for the detail records. While doing the union All, It just put the detail first and then Header and Footer.

Although Union All is setup like that

Union All Input 1 is Header

Union All Input 2 is detail and

Union All Input 3 is Footer

How can I make sure they go by order (Header,Detail,Footer).

Thanks again for your help

|||

Ahhh..I didn't think about that. Sorry. There is no way to guarantee the order.

I've thought of another way actually. Use a single script component transform to add the header and footer. It will have to be an asynchronous component.

Sorry for putting you on the wrong path.

-Jamie

Concatenate columns into a string

I am trying to concatenate 3 columns into 1 string but I need to sort
them alphabetically before I concatenate them.
Example
column1 = ABC
column2 = ABF
column3 = ABE
I need to insert the concatenate value into a column as "ABC ABE ABF"
I know how to concatenate the values but I don't know how the sort them
first.
I would appreciate any advice you can provide.
Thanks,You could try something like this as a brute-force approach:
create table #temp(column1 char(3), column2 char(3), column3 char(3))
insert #temp values('ABC', 'ABF', 'ABE')
insert #temp values('DEF', 'AAF', 'AAF')
select first + ' ' + middle + ' ' + last
from (select
case
when column1 <= column2 and column1 <= column3 then column1
when column2 <= column1 and column2 <= column3 then column2
when column3 <= column1 and column3 <= column2 then column3
end first,
case
when column1 >= column2 and column1 <= column3 then column1
when column1 >= column3 and column1 <= column2 then column1
when column2 >= column1 and column2 <= column3 then column2
when column2 >= column3 and column2 <= column1 then column2
when column3 >= column1 and column3 <= column2 then column3
when column3 >= column2 and column3 <= column1 then column3
end middle,
case
when column1 >= column2 and column1 >= column3 then column1
when column2 >= column1 and column2 >= column3 then column2
when column3 >= column1 and column3 >= column2 then column3
end last
from #temp) x
go
drop table #temp
go|||Have you ever worked in billing at a law firm?
:)
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1147465998.777145.245230@.v46g2000cwv.googlegroups.com...
> You could try something like this as a brute-force approach:
> create table #temp(column1 char(3), column2 char(3), column3 char(3))
> insert #temp values('ABC', 'ABF', 'ABE')
> insert #temp values('DEF', 'AAF', 'AAF')
> select first + ' ' + middle + ' ' + last
> from (select
> case
> when column1 <= column2 and column1 <= column3 then column1
> when column2 <= column1 and column2 <= column3 then column2
> when column3 <= column1 and column3 <= column2 then column3
> end first,
> case
> when column1 >= column2 and column1 <= column3 then column1
> when column1 >= column3 and column1 <= column2 then column1
> when column2 >= column1 and column2 <= column3 then column2
> when column2 >= column3 and column2 <= column1 then column2
> when column3 >= column1 and column3 <= column2 then column3
> when column3 >= column2 and column3 <= column1 then column3
> end middle,
> case
> when column1 >= column2 and column1 >= column3 then column1
> when column2 >= column1 and column2 >= column3 then column2
> when column3 >= column1 and column3 >= column2 then column3
> end last
> from #temp) x
> go
> drop table #temp
> go
>|||>> I know how to concatenate the values but I don't know how the sort them
If ordered display of these is significant to your business, perhaps you
should not be representing these values in a single row to begin with.
Please elaborate on your requirements, including some actual sample data,
and someone here can give some advice.
Anith|||(jdornan@.wideopenwest.com) writes:
> I am trying to concatenate 3 columns into 1 string but I need to sort
> them alphabetically before I concatenate them.
> Example
> column1 = ABC
> column2 = ABF
> column3 = ABE
> I need to insert the concatenate value into a column as "ABC ABE ABF"
> I know how to concatenate the values but I don't know how the sort them
> first.
I don't know you intend to concatenate them, but if you are on SQL 2000
you should run a cursor over the data, and the cursor definition should
include an ORDER BY clause. There are tricks that does not use a cursor,
but problem with these tricks is that they rely on undefined behaviour,
and may not yield the desired result.
On SQL 2005, there is another trick - but this time one that relies
on defined behaviour by using FOR XML PATH and XQuery. I show it here
with a sample from the Northwind database:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your input. Sorry for the vague post, I will
elaborate some more on what exactly I am doing.
I am running SQL Server 2000.
I currently get data from 4 different sources that I need to combine.
Each source has different parts of the information. For example:
Serial number 111222 for a widget.
Each widget has many different options that are listed as a 3 character
strings. AAA, BBB, CCC
The problem is that I get the options from 4 different sources so I
must combine all the options into 1 long string (separated by a space)
to be used by another application. I can join the tables with the
serial number as the primary key but the kicker is that the options
must be placed in 1 string in alphabetic order for the front end
application to function correctly.
I am using a DTS package to import and concatenate the data right now.
The data is dumped from a mainframe into 4 different txt files
(currently this is the only way we can obtain this data). The order in
which I import the 4 data sources works most of the time.
Unfortunately there are instances where we have option codes that are
not in alphabetic order and the front end app errors out.
I know we can change the front end application to do the sorting but
with 500,000 plus records at a time it would add quite a bit of
overhead to the app. My thought was to combine, sort, and store them
in one field on the server so the front end app doesn't have to do
that processing.|||(jdornan@.wideopenwest.com) writes:
> I am running SQL Server 2000.
> I currently get data from 4 different sources that I need to combine.
> Each source has different parts of the information. For example:
> Serial number 111222 for a widget.
> Each widget has many different options that are listed as a 3 character
> strings. AAA, BBB, CCC
> The problem is that I get the options from 4 different sources so I
> must combine all the options into 1 long string (separated by a space)
> to be used by another application. I can join the tables with the
> serial number as the primary key but the kicker is that the options
> must be placed in 1 string in alphabetic order for the front end
> application to function correctly.
> I am using a DTS package to import and concatenate the data right now.
> The data is dumped from a mainframe into 4 different txt files
> (currently this is the only way we can obtain this data). The order in
> which I import the 4 data sources works most of the time.
> Unfortunately there are instances where we have option codes that are
> not in alphabetic order and the front end app errors out.
> I know we can change the front end application to do the sorting but
> with 500,000 plus records at a time it would add quite a bit of
> overhead to the app. My thought was to combine, sort, and store them
> in one field on the server so the front end app doesn't have to do
> that processing.
That does not sound too fun, at least not on SQL 2000. You could
get the options into a SQL Server table, but you would then have
to run a cursor over the table to build the string for each widget.
I hope that the options are not stored in sources as lists as well,
because that would make things even worse.
Which will perform the worst, doing this in the server or in the
application, I don't know.
If you post CREATE TABLE statements for the tables, and INSERT statements
with sample data, it is possible that someone is able to find a shortcut
of some sort.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

concatenate columns in sql 2000

i need to concatenate two columns into one column in a table for sql 2000.Has the teacher covered this in class yet? There are multiple ways to do it, and without knowing what they've covered or what the assignment actually says it is tough to guess which answer they want.

-PatP|||I am simply trying to get some help. I am not a DBA, I am not in class for it, nor do I want to be one. I am a networking guy trying to get as much help as I can from other professionals. I have some basic familiarity with SQL but not much. I have to perform this concatenation for my job, but my company does not have a DBA either.

Can anyone help??|||If all you need is to get the job done, then I'd try using something like:UPDATE myTable
SET first_column = first_column + second_column-PatP

concatenate - maybe?

I'm not sure what this process would be called.
I've got 2 columns...
[A] = FamilyName
[B] = FamilyMembers
I'm trying to make this...
[A] [B]
Doe Alan
Doe Bob
Doe Betty
Doe Joe
...into results like this...
[A] [B]
Doe Alan, Bob, Betty, Joe
i.e. Group column [B] into one field
thanksThis is called violating First Normal Form (1NF). It is a TOTAL
VIOLATION of the *most fundamental* principles of RDBMS.
Newbies without any business writing a database often post this request
in Newsgroups to show that they have never learned BASIC RDBMS
principles.
But even before SQL and RDBMS, the *most fundamental* principle of
*any* tiered architecture is that display is done in the client and
NEVER in the server. If you do not know this, then you should not be
programmng at all.|||Who the hell crapped in your soup?
Listen up a**hole!
There is NOT ONE LAW prohibiting me from joining the data into a query per
my preference!
And if this is the only way you can respond in a newsgroup, you need to move
on!
It is neither helpful, informative or appreciated.
Next time you run across a thread that pisses you off, move on!
You might ask yourself what would compel you to respond in such a manner!
No thanks!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130366077.636225.104700@.f14g2000cwb.googlegroups.com...
> This is called violating First Normal Form (1NF). It is a TOTAL
> VIOLATION of the *most fundamental* principles of RDBMS.
> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
>|||Using the article http://www.aspfaq.com/show.asp?id=2529 as a basis the
solution below should help you out:
CREATE TABLE dbo.family
(
FamilyName VARCHAR(20),
FamilyMembers VARCHAR(20)
)
INSERT family SELECT 'Doe', 'Alan'
INSERT family SELECT 'Doe', 'Bob'
INSERT family SELECT 'Doe', 'Betty'
INSERT family SELECT 'Doe', 'Joe'
CREATE FUNCTION dbo.GetFamily
(
@.familyName VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.r VARCHAR(8000)
SELECT @.r = ISNULL(@.r+',', '') + familymembers
FROM dbo.family
WHERE familyname = @.familyName
RETURN @.r
END
GO
SELECT FamilyName, dbo.GetFamily(FamilyName)
FROM (SELECT familyname
FROM family
GROUP BY familyname) As a
- Peter Ward
WARDY IT Solutions
"shank" wrote:

> I'm not sure what this process would be called.
> I've got 2 columns...
> [A] = FamilyName
> [B] = FamilyMembers
> I'm trying to make this...
> [A] [B]
> Doe Alan
> Doe Bob
> Doe Betty
> Doe Joe
> ...into results like this...
> [A] [B]
> Doe Alan, Bob, Betty, Joe
> i.e. Group column [B] into one field
> thanks
>
>|||> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
What absolute rubbish, you expose your lack of programming and real world
experience.
Formatting (what you term display) is done where it is most efficient to do
it, that may well be on the SELECT statement in the database or through
logic i.e. multiple row to single row conversion, again, within the
database.
Your ideas are 15 years old and way out of date.

> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
You are not as respective nor experienced as you think you are.
You may also want to go back and fix this article where you have done a
fundemental mistake in not testing your design...
http://www.dbazine.com/ofinterest/oi-articles/celko14
From Kurt Sune's post of 26 Oct 2005, 14:21...
Tryed it in SQL server, doesnt work due to the fact that SQL server doesnt
treat count as sum.
This query gives the wrong answer, the usage of count taken from the
Celko-article.
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
This one gives the right answer:
SELECT sum(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,sum(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130366077.636225.104700@.f14g2000cwb.googlegroups.com...
> This is called violating First Normal Form (1NF). It is a TOTAL
> VIOLATION of the *most fundamental* principles of RDBMS.
> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
>|||Hi There,
Formatting (what you term display) is done where it is most efficient
to do
it, that may well be on the SELECT statement in the database or through
logic i.e. multiple row to single row conversion, again, within the
database.
Do you consider it (multiple row to single row conversion) fast
/Efficient ? Formatting should (sorry must ) be done on Client Side
.Send the ordered output to the client and process the rows in
single-level-break report manner.
We are all here to use the newsgroup not to abUSE it or flame someone.
Who the hell crapped in your soup?
Listen up a**hole!
There is NOT ONE LAW prohibiting me from joining the data into a query
per my preference!
The newsgroup is like a street shop where you pick those things which
suits you or you like ,ignore whatever you feel not good/worth.
With Warm regards
Jatinder Singh|||Jatinder,
Your answers seem to have got lost in my post.

> Do you consider it (multiple row to single row conversion) fast
> /Efficient ? Formatting should (sorry must ) be done on Client Side
> .Send the ordered output to the client and process the rows in
> single-level-break report manner.
The point i'm trying to get across is that you need do it where its most
efficient to do it, you just can't make a definitive statement that
formatting must be done in the client.
What if there where a few thousand rows being concatenated?
You need to think things through, there is usually a network in place
between the SQL Server and the client, you need to consider scalability what
ever solution you decide to use.
I prefer to do things on and in the SQL Server because its central, i won't
have that scalability problem and now with SQL Server 2005 having CLR
integration i can do a lot of stuff more efficiently.

> We are all here to use the newsgroup not to abUSE it or flame someone.
Completely agree, you are talking to the wrong person here.
If you check my posts you will see the only person i talk down to is Celko
because of his arrogant attitude and rudeness to people who use this
community. You will also note a lot of other people do the same.
Being rude and arrogant to grow name popularity just to sell a book is not
what these communities are for which is what really gets my goat.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1130396709.846350.275340@.g47g2000cwa.googlegroups.com...
> Hi There,
> Formatting (what you term display) is done where it is most efficient
> to do
> it, that may well be on the SELECT statement in the database or through
> logic i.e. multiple row to single row conversion, again, within the
> database.
>
> Do you consider it (multiple row to single row conversion) fast
> /Efficient ? Formatting should (sorry must ) be done on Client Side
> .Send the ordered output to the client and process the rows in
> single-level-break report manner.
> We are all here to use the newsgroup not to abUSE it or flame someone.
> Who the hell crapped in your soup?
> Listen up a**hole!
> There is NOT ONE LAW prohibiting me from joining the data into a query
> per my preference!
> The newsgroup is like a street shop where you pick those things which
> suits you or you like ,ignore whatever you feel not good/worth.
>
> With Warm regards
> Jatinder Singh
>|||Hi Tony,
Thanks for your input , but using scalar function to produce the
concatenated output even for 100 or 1000 rows is not advisable ( I used
the term advisable ; because basic rules help us to write good and
managable code [sorry queries] )
If someone wish to use scalar function there is no one stopping him
or her , but in case the length of concated string crosses 8000 ,Isnot
it the output is incorrect?
SQL Server give us Inline and Scalar function but I really donot
use any of them . What I feel is scalar function can be replaced by
formula directly and an INline function can be replaced by a view with
proper WHERE Clause .So ,where does a function really fit in?
With Warm regards
Jatinder Singh|||It depends what you are doing, if you want concatenated output then yes you
need to consider the 8000 byte limit for a scalar function, alternatively
use a table variable.
This all becomes significantly better and more performant in SQL Server 2005
with CLR, also the TSQL functions are faster as well.
Say you are concatenating 20 values, having 20 joins starts to become
unmanageable and difficult to maintain.
You might be concatenating them for good reason, for instance a message
board thread just like this one. There are tons of other reasons too.
I think i'll finish with a call back to my original post - you cannot
definitively state not to do stuff in SQL Server, it depends what you are
doing - you must test each method and make sure it scales!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1130416953.071865.8500@.f14g2000cwb.googlegroups.com...
> Hi Tony,
> Thanks for your input , but using scalar function to produce the
> concatenated output even for 100 or 1000 rows is not advisable ( I used
> the term advisable ; because basic rules help us to write good and
> managable code [sorry queries] )
> If someone wish to use scalar function there is no one stopping him
> or her , but in case the length of concated string crosses 8000 ,Isnot
> it the output is incorrect?
> SQL Server give us Inline and Scalar function but I really donot
> use any of them . What I feel is scalar function can be replaced by
> formula directly and an INline function can be replaced by a view with
> proper WHERE Clause .So ,where does a function really fit in?
> With Warm regards
> Jatinder Singh
>|||I am appalled at the arrogance of this. I have been designing and building
databases since 1978 and Hierarchical and Codasyl, I then moved on to Relati
onal and NO relational fits all the rules of Codd.
What a piece of arrant nonsense. What this 'CELKO' person does not realise
is that RDBMS is itself a cludge.
I noticed a complete absence of creative input from 'CELKO'.
What this person is doing here is stifling innovation. As other posts sai
d, what if there are thousands of rows.
The ART of database design is in initially normalizing, and THEN de-normaliz
ing for efficiency. Retaining NF for the sake of NF is mentally pathetic.
I have successfully built many very large databases, and many small ones, I
have designed my own DBMS, and worked closely with the architects of some o
f the world's most important DBMSs, you sir, are talking out of your hat.
As for the original post, good question, but I too think you will be stymied
by the 8k limit.
Jerry

quote:

---

Originally posted by --CELKO--
This is called violating First Normal Form (1NF). It is a TOTAL
VIOLATION of the *most fundamental* principles of RDBMS.
Newbies without any business writing a database often post this request
in Newsgroups to show that they have never learned BASIC RDBMS
principles.
But even before SQL and RDBMS, the *most fundamental* principle of
*any* tiered architecture is that display is done in the client and
NEVER in the server. If you do not know this, then you should not be
programmng at all.

---

Concat columns

I have to do some paging stuff. I am doing the follow in a proc.
Select top 10 * from customer
where @.SearchKey <= LastName+FirstName+CustomerNumber
Question is what index to I build on the database to be able to search
this effectively?
Thank you.
ChrisFirst don't select * - specify the columns you want returned.
Second your condition is probably always going to result in a table scan
because you're not searching columns, you're comparing 2 atomic values for
each record. One value being the variable and the other being the
concatenation of the 3 columns.
Better to:
SELECT col1, col2, colN
FROM dbo.customer
WHERE
LastName >= @.SearchKey
OR FirstName >= @.SearchKey
OR CustomerNumber >= @.SearchKey
You could then build some indexes on any or all of those 3 columns.
"Chris" wrote:

> I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris
>|||Chris,
SELECT TOP 10 <COLUMN LIST>
FROM CUSTOMER
WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
BY THIS KEY
--CREATE INDEX ON CUSTOMERNUMBER
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris|||>> Question is what index to I build on the database to be able to search
Try this and see if it makes a difference. Create a computed column like:
concat AS last_name + first_name + cust_nbr.
Then cluster the computed column like:
CREATE CLUSTERED INDEX Idx ON Customers( concat ASC )
Note that this will bias the table access for queries involving this
specific predicate and could potentially slowdown other data manipulation
operations.
Anith|||Jerry Spivey wrote:
> Chris,
> SELECT TOP 10 <COLUMN LIST>
> FROM CUSTOMER
> WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
> BY THIS KEY
> --CREATE INDEX ON CUSTOMERNUMBER
> HTH
> Jerry
> "Chris" <no@.spam.com> wrote in message
> news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>
>
>
That doesn't solve my problem. I'm trying to search for the person
alphabetically after the person I passed in. you way just scans for the
customer id.
Chris|||OK...perhaps you should say that in your original post next time.
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||Try:
CREATE PROC #APROC
@.LASTNAME VARCHAR(20)
AS
SELECT <COLUMN LIST>
FROM AUTHORS
WHERE LASTNAME > @.LASTNAME
ORDER BY LASTNAME
--CONSIDER CREATING A CLUSTERED INDEX ON LASTNAME
--DROP PROC #APROC
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||KH wrote:
> First don't select * - specify the columns you want returned.
> Second your condition is probably always going to result in a table scan
> because you're not searching columns, you're comparing 2 atomic values for
> each record. One value being the variable and the other being the
> concatenation of the 3 columns.
> Better to:
> SELECT col1, col2, colN
> FROM dbo.customer
> WHERE
> LastName >= @.SearchKey
> OR FirstName >= @.SearchKey
> OR CustomerNumber >= @.SearchKey
> You could then build some indexes on any or all of those 3 columns.
>
> "Chris" wrote:
>
This won't work.. If I have the name 'Smith, Joe 1234' your solution
will give me as long as their name is getter than Smith or have a first
name greater than joe (i.e. "Apple, Zebra" would be allowed)
Chris|||On Fri, 14 Oct 2005 16:16:20 -0400, Chris wrote:

>I have to do some paging stuff. I am doing the follow in a proc.
>Select top 10 * from customer
>where @.SearchKey <= LastName+FirstName+CustomerNumber
>Question is what index to I build on the database to be able to search
>this effectively?
>Thank you.
>Chris
Hi Chris,
Anith's suggestion to add a computed column and index it is good. But if
that's for some reason unwanted, then try the query below. The extra AND
might look redundant, but your version is unable to use an index on the
LastName column, and the redundant extra AND does enable some
preselection using that index (if there is one, that is).
SELECT TOP 10 Column1, column2, ... -- Don't use SELECT *
FROM customer
WHERE @.SearchKey <= LastName+FirstName+CustomerNumber
AND @.SearchKey <= LastName
By the way, are you aware that using TOP without ORDER BY will yield
undefined results?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have 'Smith, Joe 1234' your solution won't work either, as (I'm
assuming your data doesn't have the spaces and commans in it) concating the
fields will yield 'SmithJoe1234' which would probably fail the condition in
most cases.
Why are you using less than operators for string comparison anyways?
You should be dealing with things like that before it gets to the database.
"Chris" wrote:

> KH wrote:
> This won't work.. If I have the name 'Smith, Joe 1234' your solution
> will give me as long as their name is getter than Smith or have a first
> name greater than joe (i.e. "Apple, Zebra" would be allowed)
> Chris
>

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
Imran
Can you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
sqlsql

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> > Hi,
> > I have two columns name indate and intime. Both of these fields are in
> > varchar type. I need to concat two such a way that it looks like a
> datetime
> > type. Or if there is a way to convert this varchar type into datetime
> > datatype then that would work too.
> >
> > Thanks.
> > Imran
> >
>

CONCAT + expression

how can i CONCAT 2 columns & () ?
SELECT CONCAT(A,B) AS C From myTAble
but I want to get A (B)
dog (red)
thank youare they both strings?

if so SELECT COALESCE(A,'') + '(' + COALESCE(B,'') + ')'|||yes string(100)

Computing several columns for each row in source table and joining to get result

I have come across this several times now, and I cannot figure out how to do
it better. Say I have a simple table called SourceTable:
DECLARE @.sourceTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT
)
I need to create a table (view, tv function, etc.) that looks something like
DECLARE @.resultTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT,
date1 SMALLDATETIME,
date2 SMALLDATETIME
)
where date1 and date2 are calculated (with functions) using data1...data4
from the same row plus another parameter supplied by the user. So you see
what I want is so simple: For each row in @.sourceTable, evaluate a
table-valued function getDates() that returns a single row containing date1
and date2, and join the result to produce @.resultTable. However, I can't
figure out any syntax to do this straightforwardly.
In some cases where date2 depends on date1, I can use nested queries, so I
can do something like
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, date1, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) T1
But recently, I have had several problems where it would be more efficient
and maintainable if I could return both date1 and date2 from a table-valued
function as a single row with two columns. This is because the relationship
between date1 and date2 is more complicated and they can't just be computed
sequentially. My first attempt was to write a TV function that basically
was
CREATE FUNCTION getDates (@.userInput INT, @.data1 INT, @.data2 INT, @.data3
INT, @.data4 INT)
RETURNS @.dates TABLE (date1 SMALLDATETIME, date2 SMALLDATETIME) AS
BEGIN
DECLARE @.date1 SMALLDATETIME
SET @.date1 = getDate1(@.userInput, @.data1, @.data2)
DECLARE @.date2 SMALLDATETIME
SET @.date2 = getDate2(@.userInput, @.data3, @.data4)
IF (@.date1 < @.date2)
SET @.date1 = getDate1(@.date2, @.data1, @.data2)
INSERT INTO @.dates
SELECT @.date1, @.date2
RETURN
END
I tried to join the function with the source table to get my result table as
follows:
SELECT
ST.data1,
ST.data2,
ST.data3,
ST.data4,
D.date1,
D.date2
FROM @.sourceTable ST
INNER JOIN getDates(
@.userInput,
ST.data1,
ST.data2,
ST.data3,
ST.data4) D
but SQL Server always complains when it reaches the 'ST' in the second
argument of getDates(), because apparently ST is not available in that
context. I tried using a cursor to evaluate getDates() for each row in
@.sourceTable and join the result to produce @.resultTable, but something was
just wrong and the query batch would never finish executing in query
analyzer. (I debugged and found that the cursor was implemented properly,
it was just extremely slow or was hanging in QA.) For now, I am using a
several-level-deep nested query that performs the logic of of my getDates()
function. Each query level performs one calculation or condition on one of
the two dates, and the rest of the columns just get carried along. For
example:
SELECT
data1,
data2,
data3,
data4,
date1 = CASE WHEN (date1 < date2)
THEN getDate1(date2, data1, data2)
ELSE date1
END,
date2
FROM (
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) RT1
) RT2
The query is actually a few levels deeper because I have to calculate other
things based on date1, and there are many more columns. This is horrible in
terms of readability and maintanability because the logic is distributed
throughout each level of the query, and I have to repeat all the columns at
each level. If I could return more than one column from a correlated
subquery, I would be fine, but I don't believe this is possible. Can
someone please help?Well, at least I know it wasn't just me. Thanks!
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23hvbu$gEGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Dustbort,
> SQL Server 2000 and earlier do not support "correlated joins",
> which is what you are trying to write. In your example, the
> right-hand table is a table-valued function that is a different
> table for each row of the left-hand table.
> In SQL Server 2005, this can be done with the new
> APPLY operator. In 2000, there is no easy way,
> though it's possible that there is an easier way to solve
> your specific problem.
> Steve Kass
> Drew University
>
> dustbort wrote:
>

Computer Columns

Hi all,
I've been attempting to search for some documentation on how computer
columns are computed. Firstly, does anyone have any?
If not, can any tell me at which point to column is computed? Is it
computer at time of viewing, or is it computer as soon as the row is
created?
Thanks in advance.
Jon
Jonathan,
A computed column is not stored on disk unless an index is created on
it. So, it will be calculated at query time if there is no index.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>
|||I have a very basic percentage calculation running, so the overhead shouldn't be too much.
Thanks very much for the speedy help.
Jon
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Jonathan,
> A computed column is not stored on disk unless an index is created on
> it. So, it will be calculated at query time if there is no index.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Jonathan Martin wrote:
|||Jonathan,
Have a look at the query plan for more information on what SQL Server is
doing. Try putting an index on it and see if it improves things. Just
play around if you have the time and see what works best. For large
result sets, an index can really help here.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:[vbcol=seagreen]
> I have a very basic percentage calculation running, so the overhead
> shouldn't be too much.
> Thanks very much for the speedy help.
>
> Jon
> "Mark Allison" <mark@.no.tinned.meat.mvps.org
> <mailto:mark@.no.tinned.meat.mvps.org>> wrote in message
> news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...
|||If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>
|||A computed column would be NO DIFFERENT than a computed value in an Ad-Hoc
query, T-SQL stored procedure, or SQL Server view. The difference is where
the computed definition resides. The performance impact/gains would be
identical. What is different is that when either in a VIEW or a COMPUTED
COLUMN on base table is that you CAN put an index on it and, thus, have it
materialized. However, although this will alleviate the computation at
SELECT time, it will have overhead at INSERT and UPDATE time.
Indexes on Computed columns are a performance trade-off between SELECT and
CRUD times. So, it would depend on the primary purpose of the value. If
only queried sporatically for limited sets of rows, either a computed column
or view definition may make sense. If it covers the entire table and is
queried often, then either an INDEXED COMPUTED COLUMN or an INDEXED VIEW
with an index on the column within the VIEW may make more sense.
Sincerely,
Anthony Thomas

"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eTJqArSCFHA.1404@.TK2MSFTNGP11.phx.gbl...
If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>

Computer Columns

Hi all,
I've been attempting to search for some documentation on how computer
columns are computed. Firstly, does anyone have any?
If not, can any tell me at which point to column is computed? Is it
computer at time of viewing, or is it computer as soon as the row is
created?
Thanks in advance.
JonJonathan,
A computed column is not stored on disk unless an index is created on
it. So, it will be calculated at query time if there is no index.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>|||This is a multi-part message in MIME format.
--=_NextPart_000_001A_01C50917.F5D63080
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have a very basic percentage calculation running, so the overhead =shouldn't be too much.
Thanks very much for the speedy help.
Jon
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message =news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Jonathan,
> > A computed column is not stored on disk unless an index is created on > it. So, it will be calculated at query time if there is no index.
> > -- > Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> > Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
> > > Jonathan Martin wrote:
>> Hi all,
>> >> I've been attempting to search for some documentation on how computer =
>> columns are computed. Firstly, does anyone have any?
>> >> If not, can any tell me at which point to column is computed? Is it >> computer at time of viewing, or is it computer as soon as the row is >> created?
>> >> Thanks in advance.
>> >> >> Jon
>> --=_NextPart_000_001A_01C50917.F5D63080
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I have a very basic percentage =calculation running, so the overhead shouldn't be too much.
Thanks very much for the speedy help. =
Jon
"Mark Allison" wrote in message news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...> =Jonathan,> > A computed column is not stored on disk unless an index is =created on > it. So, it will be calculated at query time if there is no index.> > -- > Mark Allison, SQL Server MVP> => > Looking for a SQL Server replication book?>> > > Jonathan Martin wrote:> Hi all,> > I've been attempting to search for some documentation on how computer > columns are computed. =Firstly, does anyone have any?> > If not, can any tell me =at which point to column is computed? Is it > computer at time =of viewing, or is it computer as soon as the row is > created?> > Thanks in advance.> => > Jon> >

---

--=_NextPart_000_001A_01C50917.F5D63080--|||Jonathan,
Have a look at the query plan for more information on what SQL Server is
doing. Try putting an index on it and see if it improves things. Just
play around if you have the time and see what works best. For large
result sets, an index can really help here.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:
> I have a very basic percentage calculation running, so the overhead
> shouldn't be too much.
> Thanks very much for the speedy help.
>
> Jon
> "Mark Allison" <mark@.no.tinned.meat.mvps.org
> <mailto:mark@.no.tinned.meat.mvps.org>> wrote in message
> news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...
> > Jonathan,
> >
> > A computed column is not stored on disk unless an index is created on
> > it. So, it will be calculated at query time if there is no index.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Jonathan Martin wrote:
> >> Hi all,
> >>
> >> I've been attempting to search for some documentation on how computer
> >> columns are computed. Firstly, does anyone have any?
> >>
> >> If not, can any tell me at which point to column is computed? Is it
> >> computer at time of viewing, or is it computer as soon as the row is
> >> created?
> >>
> >> Thanks in advance.
> >>
> >>
> >> Jon
> >>
> >>|||If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>|||A computed column would be NO DIFFERENT than a computed value in an Ad-Hoc
query, T-SQL stored procedure, or SQL Server view. The difference is where
the computed definition resides. The performance impact/gains would be
identical. What is different is that when either in a VIEW or a COMPUTED
COLUMN on base table is that you CAN put an index on it and, thus, have it
materialized. However, although this will alleviate the computation at
SELECT time, it will have overhead at INSERT and UPDATE time.
Indexes on Computed columns are a performance trade-off between SELECT and
CRUD times. So, it would depend on the primary purpose of the value. If
only queried sporatically for limited sets of rows, either a computed column
or view definition may make sense. If it covers the entire table and is
queried often, then either an INDEXED COMPUTED COLUMN or an INDEXED VIEW
with an index on the column within the VIEW may make more sense.
Sincerely,
Anthony Thomas
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eTJqArSCFHA.1404@.TK2MSFTNGP11.phx.gbl...
If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>

Computer Columns

Hi all,
I've been attempting to search for some documentation on how computer
columns are computed. Firstly, does anyone have any?
If not, can any tell me at which point to column is computed? Is it
computer at time of viewing, or is it computer as soon as the row is
created?
Thanks in advance.
JonJonathan,
A computed column is not stored on disk unless an index is created on
it. So, it will be calculated at query time if there is no index.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>|||I have a very basic percentage calculation running, so the overhead shouldn'
t be too much.
Thanks very much for the speedy help.
Jon
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message news:uXVKWZRCFHA.3728@.TK2MSFT
NGP14.phx.gbl...[vbcol=seagreen]
> Jonathan,
>
> A computed column is not stored on disk unless an index is created on
> it. So, it will be calculated at query time if there is no index.
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> Jonathan Martin wrote:|||Jonathan,
Have a look at the query plan for more information on what SQL Server is
doing. Try putting an index on it and see if it improves things. Just
play around if you have the time and see what works best. For large
result sets, an index can really help here.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:[vbcol=seagreen]
> I have a very basic percentage calculation running, so the overhead
> shouldn't be too much.
> Thanks very much for the speedy help.
>
> Jon
> "Mark Allison" <mark@.no.tinned.meat.mvps.org
> <mailto:mark@.no.tinned.meat.mvps.org>> wrote in message
> news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...|||If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>|||A computed column would be NO DIFFERENT than a computed value in an Ad-Hoc
query, T-SQL stored procedure, or SQL Server view. The difference is where
the computed definition resides. The performance impact/gains would be
identical. What is different is that when either in a VIEW or a COMPUTED
COLUMN on base table is that you CAN put an index on it and, thus, have it
materialized. However, although this will alleviate the computation at
SELECT time, it will have overhead at INSERT and UPDATE time.
Indexes on Computed columns are a performance trade-off between SELECT and
CRUD times. So, it would depend on the primary purpose of the value. If
only queried sporatically for limited sets of rows, either a computed column
or view definition may make sense. If it covers the entire table and is
queried often, then either an INDEXED COMPUTED COLUMN or an INDEXED VIEW
with an index on the column within the VIEW may make more sense.
Sincerely,
Anthony Thomas
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eTJqArSCFHA.1404@.TK2MSFTNGP11.phx.gbl...
If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>

computed columns or UDFs

Hi,

What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?

thanks,
Frankjust look up books online they have a better xplanation than anyone here can give you in 1-2 lines.

hth

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
|||Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should[vbcol=seagreen]
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
=
>
|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>
|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still[vbcol=seagreen]
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
this[vbcol=seagreen]
=
>
|||Hi,
temporary tables are always made in tempdb (and only tempdb for your solution)
change
use master
on
use tempdb
Regards,
Pablo
"Steven Yampolsky" wrote:

> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
>
>
|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>
|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>
|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
in
>

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!|||Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
=[vbcol=seagreen]
>|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
this[vbcol=seagreen]
=[vbcol=seagreen]
>|||Hi,
temporary tables are always made in tempdb (and only tempdb for your solutio
n)
change
use master
on
use tempdb
Regards,
Pablo
"Steven Yampolsky" wrote:

> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and stil
l
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name =
> 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> created
>
>|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
in[vbcol=seagreen]
>|||Won't that function get dropped/cleaned up and will require constant
checking for its existance?
Steve
"Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> Create a permanent function in tempdb, like
> USE tempdb
> GO
>
> CREATE FUNCTION dbo.fnTest
> (@.x int, @.y int)
> RETURNS int
> AS
> BEGIN
> RETURN (@.x+@.y)
> END
> GO
> Now using another database u can call this function during temp table
> creation
> eg.
>
> USE Pubs
>
> CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
>
> INSERT INTO #temp VALUES(1,2)
> INSERT INTO #temp VALUES(3,2)
> SELECT * FROM #temp
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> What
closed?[vbcol=seagreen]
out[vbcol=seagreen]
> wrote
> in
>