Need to combine string data from multiple columns into one column

When quering a table with given criteria, For ex:

select notes, jobid, caller from contact where status in (6) and jobid = 173

I am getting this:

This job will be posted to Monster for 2 weeks. 173 906
Waiting for full budget approval 173 906
TUrns out we're uppin 173 906

What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?

Please HELP!!!!!

Concatenating row values in Transact-SQL

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

AMB

|||

You can concantenate the results, but you need to ensure that you have converted all the different data types to varchar. E.g.

Code Snippet

SELECT notes + ' , ' + CAST(jobid as varchar(100)) + ' , ' + CAST(caller as varchar(100)) FROM contact WHERE status in (6) and jobid = 173

I assumed that jobid and caller are int fields.

HTH

Ray

|||

Here it is (if you use SQL Server 2005),

Code Snippet

select distinct

(

select

notes + ';' as [text()]

from

contact sub

where

sub.caller=main.caller

and sub.jobid=main.jobid

for xml path('')

) as notes,

jobid,

caller

from

contact main

where

status in (6)

and jobid = 173

|||

Thanks for the response sekaran. I should have mentioned it before, I am using sql server 2000 using tsql language. Also the notes columns is of text type which I will cast as nvarchar(3500). I am having problems running you code. What am i doing wrong? can u help?

Need to change column properties...

I have an MSDE database filled with data. I use Web Data Admin to config
the db. In my short-sightedness I defined a column of vchar as only 12
characters when now I need 15. However since there's data in the db, the
edit option is not available for the columns. What's the best way to go
about making the change I need?
hi Terry,
Terry Olsen wrote:
> I have an MSDE database filled with data. I use Web Data Admin to
> config the db. In my short-sightedness I defined a column of vchar
> as only 12 characters when now I need 15. However since there's data
> in the db, the edit option is not available for the columns. What's
> the best way to go about making the change I need?
http://msdn.microsoft.com/library/de...aa-az_3ied.asp
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.test (
ID int NOT NULL PRIMARY KEY ,
data varchar(12) NOT NULL
)
GO
INSERT INTO dbo.test VALUES ( 1 ,'Andrea789012' )
SELECT * FROM dbo.test
GO
ALTER TABLE dbo.test
ALTER COLUMN data varchar(15)
GO
UPDATE dbo.test
SET data = 'Andrea789012345'
WHERE ID = 1
SELECT * FROM dbo.test
GO
DROP TABLE dbo.test
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Need to audit structure change

How to audit changes made by SQL users on table
structure. Some changes include data type, column name,
and number of columns.
Thanks for suggestion.Look up sp_trace_create in the books online - one of the security audit
event types will allow you to capture all DDL which should get everything
you're looking for here. You can also use sql profiler to build the trace
for you and then script it out if you don't want to use the GUI all the
time...
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Fuller" <anonymous@.discussions.microsoft.com> wrote in message
news:020801c3ba8a$69ee1650$a101280a@.phx.gbl...

quote:

---

> How to audit changes made by SQL users on table
> structure. Some changes include data type, column name,
> and number of columns.
> Thanks for suggestion.

---

Need to add dup rows to sp

I have a sp that is used for membership cards, there is a column Number_of_Cards. We enter the number of cards a member wants. I need to create duplicate rows in my results based on the number entered in this field for each member. Anyone have any Ideas how to generate dup rows based on this column?

Thanks for any thoughts,Well, I don't like the sounds of it ;-) , but this is how you would do it in your sproc:

DECLARE @.LoopCount int
SELECT @.LoopCount = 1
WHILE @.LoopCount <= @.CardCount
BEGIN
INSERT INTO...
SET @.LoopCount = @.LoopCount + 1
END

Note that Transact-SQL does not have a FOR...NEXT loop construct. You need to make do with a WHILE loop.

Terri|||Thank you, I will give it a try. I know this sounds bad, but it is for a merge to membership cards and I need to do it this way to use Microsoft Word for the process.

Thanks again.|||I have been working with the example and have run into an issue. I thought you might have an idea. I think the problem is that the first loop does the INTO temp_Cards then the next loop fails because the temp_Cards table already exists.

CREATE PROCEDURE dbo.eP_CardProcess2 (@.Start datetime,@.End datetime,@.LoopCount int)
AS SELECT @.LoopCount =1
WHILE @.LoopCount <= (Select dbo.tblPledges.Number_Of_Cards From dbo.tblPledges Where (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0))
BEGIN
SELECT dbo.tblPledges.Number_Of_Cards,dbo.tblPledges.Card_Start_Date,dbo.tblPledges.Card_End_Date,dbo.tblPledges.Alternate_Card_Name,dbo.tblGivers.Email,dbo.tblPledges.RenewalDate INTO #temp_Cards
FROM dbo.tblPledges INNER JOIN dbo.tblClient ON dbo.tblPledges.Client_ID = dbo.tblClient.Client_ID WHERE (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0)
SET @.LoopCount = @.LoopCount + 1
END

sql

Need to add a new column to an existing table with 37M rows

I have been trying a couple of methods to add a column to a table. Well adding the column hasnt been that difficult. The difficult part is when I need to update this newly added column with a value returned from a function. Even 1000 rows takes for ever to update in a transaction. Is there any one who has come across this stituation, please help.

Thanks in Advance.

Quote:

Originally Posted by codezilla

I have been trying a couple of methods to add a column to a table. Well adding the column hasnt been that difficult. The difficult part is when I need to update this newly added column with a value returned from a function. Even 1000 rows takes for ever to update in a transaction. Is there any one who has come across this stituation, please help.

Thanks in Advance.

am not sure there are other ways...maybe you could benchmark your UPDATE vs SELECT ...newfield = udf(para) into ... from...

depending on table that your updating (may have triggers, constraint). the cons of SELECT...INTO is also space on your db.

also, try if you can just use a CALCULATED FIELD. another one is to just use a function outside of db, that is if you don't need to keep this field and will be used primarily for display purposes.

Need the logic to get this done without cursors

Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?

for intNumber = 1 what is the logic to choose x rather than y ?

for intNumber = 2 what is the logic to choose z rather than m ?

|||

dilbert1947:

I want to select the rows where the intNumber number appears for the first time

As khtan is asking, what is the logic behind your requirement? SQL Server has no concept of "first" without some kind of data sorting.

Typically what you are asking for could be accomodated by this sort of query, where the non-unique columns(s) are aggregated in some manner:

SELECT intNumber, MIN(strItem) FROM myTable GROUP BY intNumber ORDER BY intNumber

|||

Are you trying to accomplish this from code on a page, or in your SQL data manager? If you are doing this from a page, you could use a function with logic such as:

dataset1 = SELECT DISTINCT id FROM tableName //populates with 1 entry for each different ID</P><P>while(dataset != null){ //loop until out of ID's</P><P>dataset2 = SELECT TOP 1 * FROM tableName WHERE id = dataset1.value; // grab top record for each distinct ID</P><P>//do something with data</P><P>dataset1.movenext // move to next record</P><P>}

|||

Sorry for the mess. That should read:

dataset1 = SELECT DISTINCT id FROM tableName //populates with 1 entry for each different ID

while(dataset != null){ //loop until out of ID's

dataset2 = SELECT TOP 1 * FROM tableName WHERE id = dataset1.value; // grab top record for each distinct ID

do something with data

dataset1.movenext // move to next record

}

Need SQL Help - Trim parentheses in column of data

Hi All, I am sure someone has done this before. Trying to write a SQL statement to UPDATE a column named Prod_Model. The table name is tbl_MASTER. Wanting to trim the parentheses out of the data and update back to the table. Any help on the SQL statement?

Trim or remove? Trim usually means only leading and/or trailing, and remove means to get rid of all.

Here is how to remove:

UPDATE Table

SET field=REPLACE(REPLACE(field,')',''),'(','')

WHERE field LIKE '%)%' OR field LIKE '%(%'

Here is how to trim:

UPDATE Table

SET field=REVERSE(SUBSTRING(REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)),PATINDEX('%[^()]%',REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1))),LEN(REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)))-PATINDEX('%[^()]%',REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)))+1))

WHERE field LIKE '(%' or field LIKE ')%' or field LIKE '%)' or field LIKE '%('

There is a much shorter way using STUFF, REVERSE, and PATINDEX, but I thought of this way first.

|||

The remove method did the ticket. Thank you so much.

sql

need some TSQL help

I have some entries in a column as such
XYZ ( ABC ) ( 1)
XYZ ( ABC ) ( 11)
XYZ ( ABC ) ( 2)
XYZ ( ABC ) ( 3)
XYZ ( ABC ) ( 4)
UVW ( XYZ ) ( 10)
UVW ( XYZ ) ( 12)
UVW ( XYZ ) ( 41)
So i want to group entries like this by the first ')' seen
so I can get distinct values such as
XYZ ( ABC )
UVW ( XYZ )
How can i write the query ? Thankscreate table t (
s varchar(40)
)
go
insert into t values ('XYZ ( ABC ) ( 1)')
insert into t values ('XYZ ( ABC ) ( 11)')
insert into t values ('XYZ ( ABC ) ( 2)')
insert into t values ('XYZ ( ABC ) ( 3)')
insert into t values ('XYZ ( ABC ) ( 4)')
insert into t values ('UVW ( XYZ ) ( 10)')
insert into t values ('UVW ( XYZ ) ( 12)')
insert into t values ('UVW ( XYZ ) ( 41)')
go
select distinct
substring(s,1,charindex(')',s)) as s
from t
where charindex(')',s) > 0
go
drop table t
Steve Kass
Drew University
Hassan wrote:

>I have some entries in a column as such
>XYZ ( ABC ) ( 1)
>XYZ ( ABC ) ( 11)
>XYZ ( ABC ) ( 2)
>XYZ ( ABC ) ( 3)
>XYZ ( ABC ) ( 4)
>UVW ( XYZ ) ( 10)
>UVW ( XYZ ) ( 12)
>UVW ( XYZ ) ( 41)
>So i want to group entries like this by the first ')' seen
>so I can get distinct values such as
>XYZ ( ABC )
>UVW ( XYZ )
>How can i write the query ? Thanks
>
>|||or
select substring(s,1,charindex(')',s)) as s
from #t
where charindex(')',s) > 0
group by substring(s,1,charindex(')',s))
Madhivanan

Need some suggestions about using UNIQUEIDENTIFIER

Hi all,
I have always tried to stay away from UNIQUEIDENTIFIER column types,
but I am working on a new project and I am thinking that maybe would be
the best solution.
In this project we are going to have around 200 tables, some of them may
have about 20 million records. Most of the tables are related each other.
The queries that we will have on the system will involve several tables
at the same time.
And here the most important... we are going to have multiple sites. The
information is going to be transferred between sites. Not all the tables
will be transferred but most of them. That's the reason I am thinking in
using a UNIQUEIDENTIFIER column type for the PKs of my tables.
We will be using SQL Server 2005.
What do you guys think? Do you think the overall performance of the
system can be altered a lot?
Thanks!On Mon, 24 Apr 2006 10:54:08 -0400, LEM wrote:

>What do you guys think?
Hi LEM,
First question is if you need a suurrogate key at all. Maybe the
business key is short enough to be used as the only key in the table?
But if you do need to add a surrogate key to some tables, AND you need
to be able to add values to the tables on various sites without running
into replication problems, UNIQUEIDENTIFIER might be the best choice for
you.
Hugo Kornelis, SQL Server MVP|||Thanks for your reply, Hugo.

> First question is if you need a suurrogate key at all. Maybe the
> business key is short enough to be used as the only key in the table?
Not sure if I understand. A regular int PK should be ok for each table,
but I would run into replication problems when transferring data.
The main reason I was considering using a UNIQUEIDENTIFIER column type
as PK for each table was because of replication.|||On Tue, 25 Apr 2006 10:49:32 -0400, LEM wrote:

>Thanks for your reply, Hugo.
>
>Not sure if I understand. A regular int PK should be ok for each table,
Hi LEM,
No, it's not.
In the design phase, you should already find out how your entities are
identified in the business. In 99.9% of all cases, you'll find that the
business already has a way to make sure that employees are discussing
the same customer / product / task / whatever. The attribute (or set of
attributes) the use for this is the business key fir the entity.
During implementation, you'll have to assess for each idividual entity's
business key if it's a good candidate to be the PRIMARY KEY for the
corresponding table. The default answer to that question should be
"yes". Reasons to answer "no" instead are
1. Business key is apt to frequent change, OR
2. Business key is a column with long character data or spans multiple
columns (or both) AND there are references to the entity in other
tables.
Consider using a surrogate key if either 1 or 2 applies. A surrogate key
is any system-generated key. This is added to the table IN ADDITION TO
the column(s) of the business key. The business key is NOT removed!!
Both surrogate key and business key are constrained to be unique (in
most cases, the surrogate key is made PRIMARY KEY and the business key
gets a UNIQUE constraint, but this may be reversed). Any references to
the entity (FOREIGN KEY references) are made using the surrogate key
instead of the business key. For performance reasons, the surrogate key
should be single-column and short - this is why an integer column with
the IDENTITY attribute is often chosen.
IMPORTANT: The surrogate key values should be kept internal to the
system. The end users only need to see the business key. Use stored
procedures or views to make sure that the surrogate key values are never
exposed to end users.
If you have tables with ONLY a system-generated PRIMARY KEY and no other
UNIQUE constraint, then you *will* get duplicates. Maybe not today, and
if you're lucky not tomorrow either - but one day, you will.

>but I would run into replication problems when transferring data.
>The main reason I was considering using a UNIQUEIDENTIFIER column type
>as PK for each table was because of replication.
As I said in my earlier reply - *if* you need a surrogate key in a
replicated scenarion, then UNIQUEIDENTIFIER might be a good choice. But
using no surrogate key (if possible) is better yet.
Hugo Kornelis, SQL Server MVP|||Hugo, thanks a lot for your detailed explanation.
Following your instructions this is what I have done
(I hope I have understood everything correctly):
CREATE TABLE [dbo].[TestTable](
[MySurrogateKey] [uniqueidentifier] NOT NULL CONSTRAINT
[DF_TestTable_MySurrogateKey] DEFAULT (newid()),
[MyBusinessKey] [int] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY NONCLUSTERED
(
[MySurrogateKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_TestTable] UNIQUE CLUSTERED
(
[MyBusinessKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
My question now is the following. If I transfer a record from one site
to another... don't you think that I may get duplicates because of the
MyBusinessKey column? That column is unique only in the CURRENT SITE.
That's the reason I had in mind having only the surrogate key, instead
of keeping both. Don't you think I would have that problem if I keep both?
Thanks!|||On Wed, 26 Apr 2006 13:50:07 -0400, LEM wrote:

>Hugo, thanks a lot for your detailed explanation.
>Following your instructions this is what I have done
>(I hope I have understood everything correctly):
>CREATE TABLE [dbo].[TestTable](
> [MySurrogateKey] [uniqueidentifier] NOT NULL CONSTRAINT
>[DF_TestTable_MySurrogateKey] DEFAULT (newid()),
> [MyBusinessKey] [int] NOT NULL,
> CONSTRAINT [PK_TestTable] PRIMARY KEY NONCLUSTERED
>(
> [MySurrogateKey] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
> CONSTRAINT [IX_TestTable] UNIQUE CLUSTERED
>(
> [MyBusinessKey] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
Hi LEM,
Looks good - assuming that the real table uses a better name for the
business key and includes some other data columns as well.
I like how you've made the business key clustered, rather than accepting
the default of clustering on the primary key. A uniqueidentifier often
is a bad choice for the clustered index due to frequent page splits.

>My question now is the following. If I transfer a record from one site
>to another... don't you think that I may get duplicates because of the
>MyBusinessKey column? That column is unique only in the CURRENT SITE.
That would indeed cause problems (failed replication due to violations
of the UNIQUE constraint). How to handle this depends on the real cause:
a) Either the business key is a good, unique identifier for the business
objects across the entire business. In that case, there really has been
a violation of a business rule and you want the replication to detect it
and handle it appropriately. As an example, think of tax payers
identified by their SSN - if two tax offices add a tax assessment for
the same SSN and the same fiscal year, you WANT the replication process
to detect that duplicate information has been entered and remove one of
the rows.
b) Or the business key turns out to be incomplete. If, for instance,
sales are numbered 1, 2, ... each day in each store, than the business
key (SaleDate, SaleNumber) is wrong as soon as you start replicating
data between stores - change it to (StoreID, SaleDate, SaleNumber) to
solve the problem and prevent the UNIQUE constraint violation.
Hugo Kornelis, SQL Server MVP|||Hugo,
Thanks a lot for your help.

> Looks good - assuming that the real table uses a better name for the
> business key and includes some other data columns as well.
Yes, that was only an example.

> I like how you've made the business key clustered, rather than accepting
> the default of clustering on the primary key. A uniqueidentifier often
> is a bad choice for the clustered index due to frequent page splits.
Exactly. I was reading a little bit about it and that's why I decided to
do that.

> b) Or the business key turns out to be incomplete. If, for instance,
> sales are numbered 1, 2, ... each day in each store, than the business
> key (SaleDate, SaleNumber) is wrong as soon as you start replicating
> data between stores - change it to (StoreID, SaleDate, SaleNumber) to
> solve the problem and prevent the UNIQUE constraint violation.
Yes, that should resolve my problem!
Thanks again for everything, Hugo.

Need some help writing part of the derived column component

Hey. I need to see if "/" is present in the column11 and if it's then just pass it as is or do the substring part. How do I get this to work? It's giving me an error. This is for a TimeDate column. I can get a 20060813 or 2006/08/13.I'm using the below and it's giving me an error saying that It should be DT_BOOL and I'm trying to return DT_I4.

findstring(Column11,"/",2) ? Column11 : SUBSTRING(TRIM(Column11),1,4) + "-" + SUBSTRING(TRIM(Column11),5,2) + "-" + SUBSTRING(TRIM(Column11),7,2)

Thank you

Tej

Tej,

Your test should be: findstring(Column11,"/",2)!=0 . You're testing for true or false(Boolean). findstring returns the location of the string to be found.

Frank

|||

Hey Frank. I tried (dt_bool)findstring(Column11,"/",2)

Is that correct or an error? Please let me know. I'll use what you asked me to in this case but just wanted to make sure. Thank you.

|||

Yes, that will work also.

Frank

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......|||Thank you|||Excellent solution my friend
Thanks for sharing the code

need quick help with a sql query

this query works, i want to add a 4th column that is the value of the 3rd column subracted from the value of the 2nd column, how can i add this??

SELECT `tagid` AS w1, (

SELECT count( `value` )
FROM tags
WHERE `value` =1
AND `tagid` = w1
) AS w2, (

SELECT count( `value` )
FROM tags
WHERE `value` =0
AND `tagid` = w1
) AS w3
FROM tags
WHERE `value` > -1
GROUP BY `tagid`
ORDER BY w2 DESC

you cant reference those columns can you? like this

well you can do another subquery like you already did and then add all the logic to get columns 2 and 3 then there you go

SELECT `tagid` AS w1, (SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) AS w2,
(SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) AS w3,
((SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) -(SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) ) as w4
FROM tags
WHERE `value` > -1
GROUP BY `tagid`
ORDER BY w2 DESC

NEED Query to find apostrophes in table

I've tried everything I can think of to find all the records in a table column (lastname) that contain an apostrophe. I know they are there (O'Brian, D'Marcus, etc.) However, I keep getting syntax errors.
Could someone PLEASE help?!!
Thanks,
KarenSELECT *
FROM theTableWithTheUnspecifiedName
WHERE lastName LIKE '%''%'-PatP|||Either way

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(80))
CREATE INDEX IX1 ON myTable99(Col1)
GO

INSERT INTO myTable99(Col1)
SELECT 'abc' UNION ALL
SELECT 'a''c' UNION ALL
SELECT 'xyz'
GO

SELECT *
FROM myTable99
WHERE LEN(Col1) <> LEN(REPLACE(Col1,CHAR(39),''))
GO

SELECT *
FROM myTable99
WHERE Col1 LIKE '%''%'
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

...it's a scan. I wonder if one would be more effecient than the other though|||Works fine.

Many thanks!!!!!

Karen

Need Query Help

I'm need a query that takes the number from the identity column, then uses
that for the next routine in a range...something like;
Select IdentityNumber
From TableName
Where LastName = 'Somebody'
(Then it takes that IdentityNumber, say row 100, an uses it to grab the rows
on both sides of 100, say 20 rows in each direction. So the second 1/2
of the operation would look like this.
Select FirstName, Lastname, Address
From TableName
Where identitynumbe(100) minus 20 rows and Idenittynumber(100) plus 20 rows.
Thanks in advance
JeffDefine "direction". Rows are not stored in any particular order. Specify a
criteria to sort the data, pot the DDL and some sample data.
ML|||SELECT b.FirstName, b.LastName, b.Address
FROM TableName a JOIN TableName b
ON b.IdentityNumber
BETWEEN (a.IdentityNumber -20) AND (a.IdentityNumber + 20)
WHERE a.LastName = 'Somebody'|||Try something like the following:
select b.FirstName, b.Lastname, b.Address
from TableName a
join TableName b on b.IdentityNumber between a.IdentityNumber - 20 and
a.IdentityNumber + 20
where a.LastName = 'Somebody'
--Brian
(Please reply to the newsgroups only.)
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F46D2C51-A7CB-45B7-A29B-3A297DF00F98@.microsoft.com...
> I'm need a query that takes the number from the identity column, then uses
> that for the next routine in a range...something like;
> Select IdentityNumber
> From TableName
> Where LastName = 'Somebody'
> (Then it takes that IdentityNumber, say row 100, an uses it to grab the
> rows
> on both sides of 100, say 20 rows in each direction. So the second 1/2
> of the operation would look like this.
> Select FirstName, Lastname, Address
> From TableName
> Where identitynumbe(100) minus 20 rows and Idenittynumber(100) plus 20
> rows.
>
> Thanks in advance
> Jeff|||create table Names (
IdentityNumber int,
LastName varchar(255),
FirstName varchar (255)
LogTime datetime, service varchar( 255), machine varchar( 255)
)
87, Peters, Henry, 08/17/2005 10:14:00.000
88, Smith, John, 08/17/2005 10:15:00.000
89, Johnson, Sally, 08/17/2005 10:16:00.000
90, Harris, Betty, 08/17/2005 10:17:00.000
91, Thomas, Steve, 08/17/2005 10:17:30.00
So the query would search and find Johnson,
but return say 20 rows before sally Johnson
(rows 68-88) and 20 rows after Sally Johnson
(rows 90-110)
Thanks Again!!
"ML" wrote:

> Define "direction". Rows are not stored in any particular order. Specify a
> criteria to sort the data, pot the DDL and some sample data.
>
> ML|||>> So the query would search and find Johnson, but return say 20 rows before
Generate a rank column based on the whichever column you want to use to
sequence your data and then use it in your WHERE clause. There are several
ways you can write this & here is one with a derived table construct using
the datetime column used for sequencing :
SELECT t1.*
FROM tbl t1, ( SELECT rank - 20, rank + 20
FROM ( SELECT t1.fname, COUNT( * )
FROM tbl t1, tbl t2
WHERE t2.dt <= t1.dt
GROUP BY t1.id, t1.fname, t1.lname, t1.dt
) T ( fname, rank )
WHERE fname = 'Johnson' ) D ( r1, r2 )
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.dt <= t1.dt ) BETWEEN r1 AND r2 ;
A view could give you a easier read like:
CREATE VIEW vw ( id, fname, lname, dt, rank ) AS
SELECT t1.id, t1.fname, t1.lname, t1.dt, COUNT( * )
FROM tbl t1, tbl t2
WHERE t2.dt <= t1.dt
GROUP BY t1.id, t1.fname, t1.lname, t1.dt
Now, the query is simpler:
SELECT *
FROM vw v1, vw v2
WHERE v1.rank BETWEEN v2.rank - 2 AND v2.rank + 2
AND v2.fname = 'Johnson' ;
Anith|||On Thu, 18 Aug 2005 08:20:03 -0700, Jeff wrote:

>create table Names (
>IdentityNumber int,
>LastName varchar(255),
>FirstName varchar (255)
>LogTime datetime, service varchar( 255), machine varchar( 255)
> )
>87, Peters, Henry, 08/17/2005 10:14:00.000
>88, Smith, John, 08/17/2005 10:15:00.000
>89, Johnson, Sally, 08/17/2005 10:16:00.000
>90, Harris, Betty, 08/17/2005 10:17:00.000
>91, Thomas, Steve, 08/17/2005 10:17:30.00
>So the query would search and find Johnson,
>but return say 20 rows before sally Johnson
>(rows 68-88) and 20 rows after Sally Johnson
>(rows 90-110)
>Thanks Again!!
Hi Jeff,
Untested (since you didn't post the sample data as INSERT statements):
SELECT n1.IdentityNumber, n1.LastName, n1.FirstName, n1.LogTime
FROM Names AS n1
WHERE EXISTS
(SELECT *
FROM Names AS n2
WHERE n2.LastName = 'Johnson'
AND n1.IdentityNumber BETWEEN n2.IdentityNumber - 20
AND n2.IdentityNumber + 20)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Need Query for update and insert in one go.

Hi All,
Please look at the following tables. I have Two tables
Data and TmpData with the following structure.

Data ( All int columns, ID column is Primary and identity)

Id UserID PrgID RoldID
322 1 1 2
323 1 2 2
324 1 3 2
325 2 1 2
326 2 2 2
327 2 3 2
328 3 1 2
329 3 2 2
330 3 3 2

TmpData
Id UserID PrgID RoldID
82 1 1 3
83 1 2 3
84 1 3 3
85 2 1 3
86 2 2 3
87 2 3 3
91 20 1 2
92 20 2 2
93 20 3 2
94 21 1 2
95 21 2 2
96 21 3 2

Now I need to run a query so that
Part 1: It updates existing RoleId columns ( Based on Userid,PrgID) in 'Data' Table with corresponding values from 'Data' table.
Part2 : It inserts new rows in 'TmpData' to 'Data' table.

I am done with Part1 using the simple update statement.

Update Data
Set Data.programroleid=tmp.ProgramRoleID
from TmpData tmp
where Data.userid=tmp.UserId
and Data.programId=tmp.ProgramId

This works fine for existing userids,programids in 'Data' and 'TmpData' tables. But I am struggling with inserting new rows into Data from 'TmpData' ( That exists only in 'tmpData' table). Based on above table structures how do I insert new data into 'Data' table from 'TmpData'.INSERT INTO Data(ID, UserID, PrgID, RoldID)
SELECT * FROM TmpData
WHERE ID not in (SELECT ID FROM TmpData Inner Join Data on TmpData.UserID=Data.UserID and TmpData.ProgID=Data.PrgID and TmpData.RoldID=Data.RoldID)|||ID is auto-generated identity value, and thus cannot be inserted into the target table (unless you specifically disable this for the transaction...).
I wouldn't expect the identity values to match between Data and TmpData anyway, so the "NOT IN" method is probably not appropriate. It is also not efficient. NOT EXISTS is faster than NOT IN.

INSERT INTO Data
(ID,
UserID,
PrgID,
RoldID)
SELECT ID,
UserID,
PrgID,
RoldID
FROM TmpData
WHERE NOT EXISTS
(SELECT *
FROM Data
WHERE Data.UserID = TmpData.UserID
and Data.PrgID = TmpData.PrgID
and Data.RoldID = TmpData.RoldID)

need output where same column has more than one pirce of data

CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
INSERT INTO test values(111,'a12','a')
INSERT INTO test values(111,'d33','a')
INSERT INTO test values(222,'a25','a')
INSERT INTO test values(222,'g21','e')
INSERT INTO test values(333,'a65','a')
INSERT INTO test values(333,'a64','e')
INSERT INTO test values(444,'w11','f')
INSERT INTO test values(555,'a41','a')
INSERT INTO test values(555,'r99','a')
INSERT INTO test values(555,'a76','e')
I need to output the records where the same material has an 'A' as the first
character in col1 and col2 has both an 'A' and an 'E'. So the output should
look like this:
Material col1 col2
333 a65 a
333 a64 e
555 a41 a
555 a76 e
TIAWithout better specs, my first guess is:
SELECT material, MIN(col1), col2
FROM test
GROUP BY material, col2
ORDER BY material, col2
Now, you say you want the output to be those 4 rows. Are those the ONLY
rows that should be returned? If so, why are you eliminating the rows where
material = 111, for example?
Please see http://www.aspfaq.com/5006 for some information on providing
requirements that make follow-up questions, and a delayed solution, much
less likely...
A
"Chesster" <Chesster@.discussions.microsoft.com> wrote in message
news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@.microsoft.com...
> CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
> INSERT INTO test values(111,'a12','a')
> INSERT INTO test values(111,'d33','a')
> INSERT INTO test values(222,'a25','a')
> INSERT INTO test values(222,'g21','e')
> INSERT INTO test values(333,'a65','a')
> INSERT INTO test values(333,'a64','e')
> INSERT INTO test values(444,'w11','f')
> INSERT INTO test values(555,'a41','a')
> INSERT INTO test values(555,'r99','a')
> INSERT INTO test values(555,'a76','e')
> I need to output the records where the same material has an 'A' as the
> first
> character in col1 and col2 has both an 'A' and an 'E'. So the output
> should
> look like this:
> Material col1 col2
> 333 a65 a
> 333 a64 e
> 555 a41 a
> 555 a76 e
> TIA
>|||Yes, those are the only rows that should be returned.
Rows 111 should not be in the output because col2 does not have both an 'A'
and an 'E'.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Without better specs, my first guess is:
> SELECT material, MIN(col1), col2
> FROM test
> GROUP BY material, col2
> ORDER BY material, col2
> Now, you say you want the output to be those 4 rows. Are those the ONLY
> rows that should be returned? If so, why are you eliminating the rows whe
re
> material = 111, for example?
> Please see http://www.aspfaq.com/5006 for some information on providing
> requirements that make follow-up questions, and a delayed solution, much
> less likely...
> A
>
> "Chesster" <Chesster@.discussions.microsoft.com> wrote in message
> news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@.microsoft.com...
>
>|||SET NOCOUNT ON;
GO
CREATE TABLE test
(
material INT,
col1 VARCHAR(3),
col2 VARCHAR(1)
);
GO
INSERT test SELECT 111,'a12','a';
INSERT test SELECT 111,'d33','a';
INSERT test SELECT 222,'a25','a';
INSERT test SELECT 222,'g21','e';
INSERT test SELECT 333,'a65','a';
INSERT test SELECT 333,'a64','e';
INSERT test SELECT 444,'w11','f';
INSERT test SELECT 555,'a41','a';
INSERT test SELECT 555,'r99','a';
INSERT test SELECT 555,'a76','e';
SELECT t.material, MIN(t.col1), t.col2
FROM test t
WHERE
LEFT(t.col1,1) = 'a'
AND
(
(col2 = 'a' AND EXISTS
(
SELECT 1 FROM test tA
WHERE tA.material = t.material
AND LEFT(tA.col1,1) = 'a'
AND col2 = 'e'
))
OR
(col2 = 'e' AND EXISTS
(
SELECT 1 FROM test tB
WHERE tB.material = t.material
AND LEFT(tB.col1,1) = 'a'
AND col2 = 'a'
))
)
GROUP BY material, col2
ORDER BY material, col2;
GO
DROP TABLE test;
GO
"Chesster" <Chesster@.discussions.microsoft.com> wrote in message
news:854D3331-2645-4F01-8204-86AABF15AD91@.microsoft.com...
> Yes, those are the only rows that should be returned.
> Rows 111 should not be in the output because col2 does not have both an
> 'A'
> and an 'E'.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||select * from #test where [material] in(
select ta.[material] from (
select [material] from #test where col1 like 'a%' and col2='a') ta
join (
select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e')
material col1 col2
-- -- --
333 a65 a
333 a64 e
555 a41 a
555 r99 a
555 a76 e
I see that
555 r99 a
is not present in your sample output. I don't see why it does not
belong in the result set|||> I see that
> 555 r99 a
> is not present in your sample output. I don't see why it does not
> belong in the result set
Because the first letter of col1 != 'a'|||thanks Aaron.
select * from #test t1
where col1 like 'a%'
and col2 in('a','e')
and (select count(*) from #test t2
where t1.[material] = t2.[material]
and t2.col1 like 'a%'
and t2.col2 in('a','e') and t2.col1<>t1.col1)>0
material col1 col2
-- -- --
333 a65 a
333 a64 e
555 a41 a
555 a76 e
(4 row(s) affected)|||select * from #test where [material] in(
select ta.[material] from (
select [material] from #test where col1 like 'a%' and col2='a') ta
join (
select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e') and col1 like 'a%'
just added col1 like 'a%' at the end.
Regards

need matrix report advice

I have to create a report that displays monthly actual and forecasted values by project with a variance column for the current month. Has anyone ever attempted something like this? I am having a difficult time determining how the data should return from the procedure so that it can be display in the following format:

JUL

AUG

SEP

OCT

NOV

DEC

JAN

FEB

Actuals

Var

Forecast

PROJECT A

10

20

15

-15

30

15

41

26

47

64

PROJECT B

15

10

25

5

20

20

10

5

10

10

I need to use a matrix since the number of columns can vary. I have thought about returning the following fields: project, date, label, hours.

I am hoping someone has attempted this before and can offer advice on whether I am approaching the problem correctly.

Thanks for any help.

hey there

not sure if this is what you want but this is how I approached a similar report using a Matrix - with varying columns

Report was for a Weekly user worktime

put this in your layout view

(1)=(Parameters!PersonNameValue)

(2-across)=Fields!WorkDate.Value)

(3)=Fields!CallSubject3.Value

(4-across)=Format(Sum(Fields!WorkTime.Value)

(5)=Format(Sum(Field...hrs total)

(6) blank

this shows like below

person date1 date 2 etc

subject time time etc

total time totaltime totaltime etc

sorry I wasn't sure how to post a graphical view on here so I hope this helps you

just add extra fields as required

any questions please ask

Jewel

Need line feed in ToolTip text - how?

Hello,

I am displaying a complex formula in a column header tool tip. The formula is generated in a stored procedure. (I do not enter the tool tip text directly in the column header expression.)

Within the stored procedure, how do I generate a string that contains a carriage return/line feed?

Thanks,

BCB

In you sproc insert

select 'here is the break' + char(10) + 'here is the next line' as text

In your tool tip insert

=First(Fields!text.Value)

|||

Thanks for the response, but I don't think your solution will work in my case. The expression that I use for the tooltip text is:

=First(Fields!COL_4.Value, "ToolTips")

You can see that the tooltip is coming from a "ToolTips" dataset. The dataset is produced by a stored procedure. I need to format the tooltip string within the stored procedure that will contain the line feed. I don't believe the Chr(10) would work within a sproc.

Can you think of another approach?

Thanks,

BCB

|||Using CHAR(10) works fine, at least in SQL Server. Your database may be different, but I'd guess it still has some way to embed special characters into string data.|||

You will have to use CHAR(10)+CHAR(13) for line feed and carriage return in your stored proc in SQL Server.

Shyam

|||

I misunderstood Harley Rider's correct response. Thanks to everyone for setting me straight.

BCB