How to control visibility of table columns at runtime?

Hi all..

Using ReportViewer in an ASPX page, is it possible to hide certain columns of a table at runtime?

That table is databound to a business object and I'm using local reporting capabilities of Visual Studio 2005.

Thanks

Jaime

I assume you are using a "table" reportitem. In report designer you can select the entire column of a table. In the properties window (F4), you should then see a "Visibility" property. You could set the Visibility.Hidden property to e.g. =Parameters!HideColumns.Value (assuming HideColumns is a boolean report parameter).

-- Robert

|||

Hi Robert..

I meant programmatically. How can I change Visibility property of the table using code. Suppose the table is named tblDatos and report viewer control is named rptBusqueda.

Jaime

How to control visibility of table columns at runtime?

Hi all..

Using ReportViewer in an ASPX page, is it possible to hide certain columns of a table at runtime?

That table is databound to a business object and I'm using local reporting capabilities of Visual Studio 2005.

Thanks

Jaime

I assume you are using a "table" reportitem. In report designer you can select the entire column of a table. In the properties window (F4), you should then see a "Visibility" property. You could set the Visibility.Hidden property to e.g. =Parameters!HideColumns.Value (assuming HideColumns is a boolean report parameter).

-- Robert

|||

Hi Robert..

I meant programmatically. How can I change Visibility property of the table using code. Suppose the table is named tblDatos and report viewer control is named rptBusqueda.

Jaime

how to control the table's column number

Hi,

When I add the table into the report, the default column number is 3. Let's say I need 10 columns on the report, besides right click on the last column and click on "add column right" to add the other 7 columns, is there any easy way?

Thanks.

Sorry, there is no other way of doing this. You have to add additional columns through the popup menu.

-- Robert

sql

how to control the number of row?

i want to contorl the output number of row with a certain number when select data from table that not exceed the number with my setting.

For example,if the result is exceed the 500 rows, then i just require 200 rows, that what is the command of this function?

Thanks for any help...

You can try something like:

SELECT top 500
...
from TableName

another more archaic form is to execute

SET ROWCOUNT 500

However, this will also limit the number of records update or inserted, deleted etc. Turn off the limit by:

SET ROWCOUNT 0

The TOP option is in general the better option. Look the two up in books online.

sql

how to control the distance between the two matrix? or (matirx and table )

hi everyone:

the report show two tables two matrixs

how can i control the distance between them

I want to set the same distance between the table and matrix

or (table and table )

Younger,

You could possible place your tables and Matrix each inside of a rectangle and then maintain the distance between your rectangle should get you to your desired results.

Ham

|||

Ham

Thank you very much

Your idea is good, that is worked well.

Younger

How to control number precision?

Hi all,
There is a table like this:
item quantity
-
A 1
B 3
C 7
D 6
Now I want to get a table like this:
item Percent
-
A 1/17
B 3/17
C 7/17
D 6/17
For script:
select item,
quantity/sum(quantity)
from table
but the number has a very long precision, like 0.0588235456456, I only want to keep two number after the point as like 0.06,what should I do?
Thanks!

Coeus:

Maybe something like:

select convert (numeric (9,2), 0.0588235456456)

|||

Thanks for help. :)

I also can use round(variable, 2).

How to control DB size growth?

Hello,
My database is 1gig big and it has so far one table with 1 melion records.
Once I tried to create the primary key in this table but it was rejected by
MS SQL since it was not unique. That's fine with me but I noticed that my
database doubled in size to just over 2 gigs. Then I created clastered index
and the size of the file was around 3 gigs. When I deleted newly created
index the size of the file stayed the same almost reaching the limites that
I set up before. I would never expect that the my database would grow so
fast since the same data in the Access database is just 450 MB.
Why the data grows when the key or index fails to be created?
How to control the size of the Database?
Any help is greatly appreciated,
Les
> Why the data grows when the key or index fails to be created?
It is growing because sql is creating index to the point of failure. Of
course creation takes some disk space. But after failure it cleans the data
in failure but by default it does not shrink the database file. So the file
stays as big as it was on failure.

> How to control the size of the Database?
the best way to control it is to set a maximm size, but the you should set
an alert to monitor the size and notify you database size is near full.
Best way to see how much space your data is taking is to set a TaskPad view
in Enterprise Manager.
(Select Database, go to menu View -> TaskPad).
Danijel
"Tom" <tom@.killspam.com> wrote in message
news:UjSFd.48178$TN6.1797082@.news20.bellglobal.com ...
> Hello,
> My database is 1gig big and it has so far one table with 1 melion records.
> Once I tried to create the primary key in this table but it was rejected
> by MS SQL since it was not unique. That's fine with me but I noticed that
> my database doubled in size to just over 2 gigs. Then I created clastered
> index and the size of the file was around 3 gigs. When I deleted newly
> created index the size of the file stayed the same almost reaching the
> limites that I set up before. I would never expect that the my database
> would grow so fast since the same data in the Access database is just 450
> MB.
> Why the data grows when the key or index fails to be created?
> How to control the size of the Database?
> Any help is greatly appreciated,
> Les
>

How to construct table with unique PK

Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks

What you describe isn't really applicable to a 'normal' primary key.
In the 'normal' case, a PK doesn't care about the values of other PK's in other tables.

There are cases where one would want to distribute PK values like you describe, though I don't know if that's your reason.
(eg some replication / distribution strategies)

However, if you want to read some about keys, here's a link that mayshed some light.
http://www.datamodel.org/DataModelKeys.html

=;o)

/Kenneth

|||

have a composite primary key

key1 and key2

have key1 to have a default value of "A" for the first table, "b" for the second and "c" for the third

key2 would be an identity

your pk would be a combination of key1 and key2

and that would definitely be unique across the enterprise

|||

hi KeWin,

its implemented in a CRM thats why we're try to do the same. Any ideas how?

joeydj,

How would I get the latest PK, for example A3456 is the most recent. So the next generated PK is A3457. Can you show a sprocs that would do that. Thanks. :)

|||

Well, personally I'm not too keen on keys like 'A3457' - where 'A' has some significant meaning.
This is what's called an 'intelligent key' or 'concatenated key' (because the key itself is made up of several parts that has independent meaning), and is ususally something you want to avoid.

What a certain key should look like - well, it depends.
It's a desginer's choice, and it's part of the datamodel.

There may be a number of reasons behind any PK's design, the most important thing about it (imo) is that the designer really understands what a primary key is, and why he/she want it implemented in a certain way. Be it natural or a surrogate, it should be deliberate with some thought behind it.

This leads to the next question, how to decide?
There's no simple answer to that question, unfortunately, it's one of those 'it depends' things....

Your best tool is understanding, both of the database in question, the business it should support, and the concept of primary keys etc.
(I don't have any good links handy, but google usually manages to come up with something =;o)

/Kenneth

|||

portect wrote:

hi KeWin,

its implemented in a CRM thats why we're try to do the same. Any ideas how?

joeydj,

How would I get the latest PK, for example A3456 is the most recent. So the next generated PK is A3457. Can you show a sprocs that would do that. Thanks. :)

try this. this is called a composite pk solution. the advantage is that you dont need to worry whats

the next number in sequence. the problem is joining with other table.

CREATE TABLE [mytable] (
[key1] [char] (1) NOT NULL CONSTRAINT [DF_mytable_key1] DEFAULT ('A'),
[key2] [int] IDENTITY (1, 1) NOT NULL ,
[data1] [char] (10) NULL ,
[data2] [char] (10) NULL ,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED
(
[key1],
[key2]
) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into mytable(data1,data2)values('mydata1a','mydata2a')
insert into mytable(data1,data2)values('mydata1b','mydata2b')
select * from mytable

|||

an alternative solution here.

remember that an identity has a seed and increment parameter.

seed Is the value that is used for the very first row loaded into the table.

this is called an identity seed and contraint solution.

first table shall be between 1 and 10M

pk type bigint, identity on, seed=1

data type bigint

constraint: pk between 1 and 10,000,000

second table shall be between 10M+1 and 20M

pk type bigint, identity on, seed=10,000,0001

data type bigint

constraint: pk between 10,000,001 and 20,000,000

third table shall be between 20M+1 and

pk type bigint, identity on, seed=20,000,0001

data type bigint

constraint: pk between 20,000,001 and 30,000,000

if your not happy with 10M difference make it 100M.

this solution seems better to me.

|||Thanks all. I think surrogate keys will be appropriate.|||

follow up question:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=857019&siteid=1

thanks.

sql

How to construct SQL SELECT statement

I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value

I believe I need to use the LIKE clause but i'm not sure how.

Thanks

BenSelect * From Table Where Field Like '%' + sSearch.Replace("'","''") + '%'|||Hi Ben,

You're right, you can use the LIKE clause,
E.g.
SELECT * FROM Students WHERE StudentName LIKE '%NEW%'

Then the matched records include: HAPPY NEW YEAR and ASP.NEW :)

Regards,|||Hi Colt, thanks for that

If I wanted to apply the search to more than one fields could I use

SELECT * From Students WHERE StudentName, Field2, Field3, Field4 LIKE '%NEW%'

Is that the correct syntax?

Ben|||Hi,

You may try:

SELECT *
From Students
WHERE StudentName LIKE '%NEW%' OR
 Field2 LIKE '%NEW%' OR
 Field3 LIKE '%NEW%' OR
 Field4 LIKE '%NEW%'

Regards,

How to consolidate multiple rows into a single column

Hello,
I would like some help on developing a SQL query.
I have a Team table and a Person Table. For simplicity sake, lets say
the Team has a key and team name. The Person has a person key, team
key, and person name.
I want to query for all team members, and store the results in a single
column. So, the resulting view would have three columns: team key, team
name, and a list of all people on the team.
Any pointers or tips appreciated,
J Wolfgang GoerlichSELECT T.team_key, T.team_name, P.person_name
FROM Team AS T
JOIN Person AS P
ON T.team_key = P.team_key ;
David Portas
SQL Server MVP
--|||Here is a sample:
=====
CREATE TABLE Team
(
TeamID INT,
TeamName VARCHAR(20)
)
GO
CREATE TABLE Person
(
PersonID INT,
TeamID INT,
PersonName VARCHAR(50)
)
GO
INSERT INTO Team VALUES (1, 'Development')
INSERT INTO Team VALUES (2, 'Release')
INSERT INTO Person VALUES (1, 1, 'Bob')
INSERT INTO Person VALUES (2, 1, 'Mason')
INSERT INTO Person VALUES (3, 2, 'Chris')
INSERT INTO Person VALUES (4, 2, 'Scott')
INSERT INTO Person VALUES (5, 2, 'Bruce')
GO
=====
The above just creates some sample tables and data. Now, we can define a
function that concatenates the list of team members as follows:
=====
IF (OBJECT_ID ('dbo.formTeamList') IS NOT NULL)
DROP FUNCTION dbo.formTeamList
GO
CREATE FUNCTION dbo.formTeamList (@.teamID INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.teamList VARCHAR(8000); SET @.teamList = ''
SELECT
@.teamList = @.teamList + ', ' + ISNULL (PersonName, '')
FROM
Person
WHERE
TeamID = @.teamID
RETURN (STUFF (@.teamList, 1, 2, ''))
END
GO
=====
Once done, we can test this out as follows:
=====
SELECT
TeamID, TeamName, dbo.formTeamList (TeamID)
FROM
Team
=====
Although the method shown above works, it is not recommended as there are
limitations:
(1) The function is called for every row in the team table. This can cause
performance problems for large lists.
(2) The function can only concatenate a list of 8000 characters in length
(4000 if you are using UniCode).
Such logic is usually best handled in the application tier of your program,
sinnce you have good flexibility to rotate rows into columns.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<jwgoerlich@.gmail.com> wrote in message
news:1131967790.112616.168370@.g44g2000cwa.googlegroups.com...
> Hello,
> I would like some help on developing a SQL query.
> I have a Team table and a Person Table. For simplicity sake, lets say
> the Team has a key and team name. The Person has a person key, team
> key, and person name.
> I want to query for all team members, and store the results in a single
> column. So, the resulting view would have three columns: team key, team
> name, and a list of all people on the team.
> Any pointers or tips appreciated,
> J Wolfgang Goerlich
>

How to consolidate duplicate records

Hello,
I'm assigned a task to clean up a table which has the data like below:
col1--col2--col3--col4
103 20 606 $50
103 20 606 $60
I was told to consolidate the sales$ and only keep one record, the primary
key is on col1,col2 and col3.
How can I do this?
Thanks,
SarahWhat about
SELECT col1,col2,col3,sum(col4) as sales
From YourTable
Group by col1,col2,col3
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"SG" <sguo@.coopervision.ca> schrieb im Newsbeitrag
news:%23DMa7GoRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm assigned a task to clean up a table which has the data like below:
> col1--col2--col3--col4
> 103 20 606 $50
> 103 20 606 $60
> I was told to consolidate the sales$ and only keep one record, the primary
> key is on col1,col2 and col3.
> How can I do this?
> Thanks,
> Sarah
>|||You can use GROUP BY like:
SELECT col1, col2, col3, SUM( col4 ) AS "col4"
FROM tbl
GROUP BY col1, col2, col3 ;
Anith|||Thanks Anith and Jens for your quick response. How could I forget this? I
made a mistake to group by sales column which I should use sum()so it gave
me the wrong result.
Thanks again,
Sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eChkdLoRFHA.2252@.TK2MSFTNGP15.phx.gbl...
> You can use GROUP BY like:
> SELECT col1, col2, col3, SUM( col4 ) AS "col4"
> FROM tbl
> GROUP BY col1, col2, col3 ;
> --
> Anith
>

How to connect to a SQL view NOT table

Normally i can use Web Matrix to connect to a SQL table. e.g.
Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"
Is is possible to connect to a SQL View. The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).
I also need to use VB.NET to refer to these alias columns.

hy..
I suppose it's the same as that in a table.
Dim queryString As String = "SELECT * from [MyView]"
...|||

Hey,

I think you can just use 'SELECT [MyView].* FROM [MyView]' as a query.
We use views here, we query them like they were tables, so ...
If you still have problems with it, let me know and i'll look into it a little deeper

How to connect 2 table in one database.

Hi everyone.

Please Help Me.

Asp.net using C#.Net and Backend Sql server 2005.

1. How to conection 2 table at the same time.in c#.net.

2.How to Insert,update and delete for 1 or 2 table connections.

all the code give C#.Net in asp.net.

Thanks.

Hi, don't quite get your questions. What are you trying to do? What do you mean by connect 2 tables at the same time? Once you open a connection, you can access as many tables as you want through the connection; if the two tables are in two different database, you can open one connection for each database and then access yor tables.

Same confusion with the question 2. Don't know what you are asking.

Sorry

|||You should do some research on the SQL Join clause. Here's a starter for you: http://www.google.com/search?hl=en&q=sql+join&btnG=Google+Search&meta=|||

single database. 2 table at a time insert a values.

for example :

viswa is database.

student is a one table.

college fees is a one table.

student id is uni key

at a time 2 table insert a values.

please giveinsert, update and delete in c#.Net

|||

Those tables are not related. There is no reason that you should be performing operations on both tables at the same time. Nothing in college fees should relate to anything in student. If it does, then you need to look at your database design.

Also, C# does not offer any INSERT, UPDATE or DELETE procedures. You use the SQL language to perform operations against a database. You then use C# (or VB.Net, or J# etc) to get ADO.NET to present SQL commands to the database.

|||

I have Only one database and mutiple table.

database name = employee

table name = employee details

table name = employee salary

table name = department

morte...

I connect a database . how to select 2 tables.

my question isbatch process (multiple insert, update and delete ) in 2 tables

How to configure multiple subscriber to same publisher using filte

Hi,
I have a base table in publisher db. I have a column, using which i want to
filter it and replicate it to certain dbs. how do i dynamically do it ?
Like for eg, consider this table :
ID Project name center
1 A NY
2 B LON
3 C PAR
now, i want to filter using the column "center". if center = 'NY', then i
must direct it to a particular subscriber. if it is "LON" then it must be
directed to someother subscriber. also, i must accomplish this using merge
replication
for my requirement, i am not able to use multiple publishers to accomplish
it. i must use one publisher, with a filter which varies dynamically
depending on the subscriber.
please explain how i can accomplish this.
PS : I'm new to databases. sorry if this question is very basic
Ki,
this question is not at all basic
You can use dynamic filtering in merge replication. Set up the filter as
center = HOST_NAME(). In the merge agent, before initializing, edit the
command-line parameters and add -HOSTNAME NY for the NY subscriber and so on
for the others.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
AlphaCreate a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:
> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha|||Sorry I wasn't clear on my question. I know I need to set the unique
constraint but just didn't know where is Enterprise Manager is the setting.
I just found it now in the design and right click on the column. thanks for
your help anyway.
"SQL" wrote:
> Create a unique constraint on the column
> http://sqlservercode.blogspot.com/
>
> "Alpha" wrote:
> > Hi, I want one of the column in a table to not have duplicate values(it's
> > Varchar) but it's not the primary key column which is a auto-generated id
> > column for the table.
> >
> > Thanks,
> > Alpha

How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
Alpha
Create a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:

> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha

How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
AlphaCreate a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:

> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha

How to Concatinate results to display results in a list from Left - Right instead of Top -

Hi,
I am creating a report using SQL Server Reporting Services 2000. My
report displays values in datatable. Say, the table looks like this
EmpName Emp# Address Phone#
..... ... ...... ...
I want all phone # for an employee to be displayed in a single row.
This can be acheived by displaying phone numbers in a group footer
(group by emp#). But I want all phone# to be displayed horizontally
(Left - Right) not vertically (Top - Bottom).
Instead of displaying results like this
EmpName Emp# Address
..... ... ......
Phones
###
###
###
###
I want to display results like below -
EmpName Emp# Address Phone#
..... ... ...... ...
Phones ### , ### , ####, ###, ###, ###
..... ... ...... ...
Phones ### , ###
..... ... ...... ...
Phones ### , ###, ### , ###
How can I concatinate the phone# ? I tried using a sub report in the
footer to retrive all phone# for an employee but how to concatinate
them ?
I tried to query like this
SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
But I'm not sure about the length of the return value (may be more than
8000 characters).
So is there any way in concatinating values in Report ? Or is there any
way in acheiving this in a Listbox or Table ?
Regards,
ChiroI think Chris Hays has something that will work for you.
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
Steve MunLeeuw
"Chiro" <chirangv@.hotmail.com> wrote in message
news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> Hi,
> I am creating a report using SQL Server Reporting Services 2000. My
> report displays values in datatable. Say, the table looks like this
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> I want all phone # for an employee to be displayed in a single row.
> This can be acheived by displaying phone numbers in a group footer
> (group by emp#). But I want all phone# to be displayed horizontally
> (Left - Right) not vertically (Top - Bottom).
> Instead of displaying results like this
> EmpName Emp# Address
> ..... ... ......
> Phones
> ###
> ###
> ###
> ###
> I want to display results like below -
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> Phones ### , ### , ####, ###, ###, ###
> ..... ... ...... ...
> Phones ### , ###
> ..... ... ...... ...
> Phones ### , ###, ### , ###
>
> How can I concatinate the phone# ? I tried using a sub report in the
> footer to retrive all phone# for an employee but how to concatinate
> them ?
> I tried to query like this
> SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> But I'm not sure about the length of the return value (may be more than
> 8000 characters).
> So is there any way in concatinating values in Report ? Or is there any
> way in acheiving this in a Listbox or Table ?
> Regards,
> Chiro
>|||Hi,
Thank You.
I was able to acheive this as follows -
1. Add a Matrix control. Add Phone# in column grouping of matrix.
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression
=RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group
expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2
group expressions. (1 for Phone # and other for controling no. of
columns).
Regards,
Chiro
Steve MunLeeuw wrote:
> I think Chris Hays has something that will work for you.
> http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
> Steve MunLeeuw
> "Chiro" <chirangv@.hotmail.com> wrote in message
> news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> > Hi,
> > I am creating a report using SQL Server Reporting Services 2000. My
> > report displays values in datatable. Say, the table looks like this
> >
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > I want all phone # for an employee to be displayed in a single row.
> > This can be acheived by displaying phone numbers in a group footer
> > (group by emp#). But I want all phone# to be displayed horizontally
> > (Left - Right) not vertically (Top - Bottom).
> > Instead of displaying results like this
> > EmpName Emp# Address
> > ..... ... ......
> > Phones
> > ###
> > ###
> > ###
> > ###
> > I want to display results like below -
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > Phones ### , ### , ####, ###, ###, ###
> > ..... ... ...... ...
> > Phones ### , ###
> > ..... ... ...... ...
> > Phones ### , ###, ### , ###
> >
> >
> > How can I concatinate the phone# ? I tried using a sub report in the
> > footer to retrive all phone# for an employee but how to concatinate
> > them ?
> > I tried to query like this
> > SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> > But I'm not sure about the length of the return value (may be more than
> >
> > 8000 characters).
> > So is there any way in concatinating values in Report ? Or is there any
> >
> > way in acheiving this in a Listbox or Table ?
> > Regards,
> > Chiro
> >

How to concatenate two text columns

Hi all,
I have a two text columns in my table with more than 100,000 rows.
I want to create a third text column with the data from text column 1 + text
column 2.
Is there an easy way to concatenate two text fields?
Thanks
Raju
maybe you can export the 2 columns into excel
eg. first column is in cell A1, second column is in cell B1
at C1 you type this formula =A1&B1, then copy this formula till the end of
the row, then you import it back to your table.
Susanna
"Raju" wrote:

> Hi all,
>
> I have a two text columns in my table with more than 100,000 rows.
> I want to create a third text column with the data from text column 1 + text
> column 2.
>
> Is there an easy way to concatenate two text fields?
>
> Thanks
> Raju
>
>
|||If you created a third column in your table you use an UPDATE statement
to do this like:
UPDATE Sometable
SET col3 = ISNULL(col1,'') + ISNULL(col2,'')
or in a view you could use the almost same syntax like:
SELECT ISNULL(col1,'') + ISNULL(col2,'') as col3
THE ISNULL(col1,'') syntax is related to the issue that in some cases
the attributes could be NULL rather than just an empty or regular
string and this would lead to a NULL result concatenating the two
values together.
HTH, Jens Suessmeyer.

How to concatenate multiple rows into one field?

Hi,

I hope someone here can help me.

We have a product table which has a many-to-many relation
to a category table (joined through a third "ProductCategory" table):

[product] --< [productCategory] >-- [category]
--- ------ ----
productID productCategoryID categoryID
productName productID categoryName
categoryID

We want to get a view where each product occupies just one row, and
any multiple category values are combined into a single value, eg
(concatenating with commas):

Product Category
------
cheese dairy
cheese solid
milk dairy
milk liquid
beer liquid

will become:

Product Category
------
cheese dairy, solid
milk dairy, liquid
beer liquid

What is the best way to do it in SQL?

Thanks and regards,
Dmitri"mitmed" <mitmed@.yahoo.com> wrote in message
news:c2fa9a07.0408182248.684dfd7a@.posting.google.c om...
> Hi,
> I hope someone here can help me.
> We have a product table which has a many-to-many relation
> to a category table (joined through a third "ProductCategory" table):
> [product] --< [productCategory] >-- [category]
> --- ------ ----
> productID productCategoryID categoryID
> productName productID categoryName
> categoryID
> We want to get a view where each product occupies just one row, and
> any multiple category values are combined into a single value, eg
> (concatenating with commas):
> Product Category
> ------
> cheese dairy
> cheese solid
> milk dairy
> milk liquid
> beer liquid
> will become:
> Product Category
> ------
> cheese dairy, solid
> milk dairy, liquid
> beer liquid
> What is the best way to do it in SQL?
> Thanks and regards,
> Dmitri

The usual answer is that you should do this in the client, not in TSQL, but
if you must then the only reliable way is using a cursor.

http://www.aspfaq.com/show.asp?id=2279

Simon|||Thanks for your reply Simon,

I completely agree with you that the best place for this type of code
is on the client side. The issue is that my client side is a Crystal
Report (in VB.NET) and i don't know how to do this kind of processing
there. The report i'm trying to produce is the list of products and
their details including categories a product belongs to. I would
really appreciate if somebody could point me to a good crystal report
resource, where it shows how to do things like that if it's possible.

Regards,
Dmitri

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4124eaa5_2@.news.bluewin.ch>...
> "mitmed" <mitmed@.yahoo.com> wrote in message
> news:c2fa9a07.0408182248.684dfd7a@.posting.google.c om...
> > Hi,
> > I hope someone here can help me.
> > We have a product table which has a many-to-many relation
> > to a category table (joined through a third "ProductCategory" table):
> > [product] --< [productCategory] >-- [category]
> > --- ------ ----
> > productID productCategoryID categoryID
> > productName productID categoryName
> > categoryID
> > We want to get a view where each product occupies just one row, and
> > any multiple category values are combined into a single value, eg
> > (concatenating with commas):
> > Product Category
> > ------
> > cheese dairy
> > cheese solid
> > milk dairy
> > milk liquid
> > beer liquid
> > will become:
> > Product Category
> > ------
> > cheese dairy, solid
> > milk dairy, liquid
> > beer liquid
> > What is the best way to do it in SQL?
> > Thanks and regards,
> > Dmitri
> The usual answer is that you should do this in the client, not in TSQL, but
> if you must then the only reliable way is using a cursor.
> http://www.aspfaq.com/show.asp?id=2279
> Simon|||You can do this very easily with the RAC utility/tool for S2k.
No sql coding required.

For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236

RAC v2.2 and QALite @.
www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||mitmed (mitmed@.yahoo.com) writes:
> I completely agree with you that the best place for this type of code
> is on the client side. The issue is that my client side is a Crystal
> Report (in VB.NET) and i don't know how to do this kind of processing
> there. The report i'm trying to produce is the list of products and
> their details including categories a product belongs to. I would
> really appreciate if somebody could point me to a good crystal report
> resource, where it shows how to do things like that if it's possible.

We use Crystal in our system (and we hate it!), but we never let Crystal
near SQL Server itself. The "database" we tell Crystal about is text files
with all the columns. The actual queries are submitted from VB6, and then
we feed Crystal one of more recordsets, typically augmented with other stuff
that the VB code puts in.

Exactly how that translates to in VB .Net I don't know, although it is
possible to work with ADO Recordset if you use the OleDb .Net data
provider. Then again, who wants to use ADO recordsets if you are in .Net?

I should add that my notion of how we use Crystal is somewhat foggy. I
try to stay away from Crystal as much as I can.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp