concat_ws() in mssql

hey folks, im busy trying to make my script compatible with different types of databases

so, for mysql i use the function 'concat_ws()' in my sql query
but.. this function is not valid if u use mssql

so my question is, does anyone now a function in mssql that does the same as concat_ws() in mysql?Originally posted by bertwasbeer
hey folks, im busy trying to make my script compatible with different types of databases

so, for mysql i use the function 'concat_ws()' in my sql query
but.. this function is not valid if u use mssql

so my question is, does anyone now a function in mssql that does the same as concat_ws() in mysql?

Good question ;)
My question for you: What does the concat_ws() in mysql? I've never saw mysql.|||CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULL values after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'|||Originally posted by bertwasbeer
CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULL values after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'

I've never heard about function like this in MSSQL, may somebody else has. Anyway you could create User-Defined Functions for sql2000.|||What do you want to do with it?
If you are concatenating a column from selected rows you can

declare @.s varchar(8000)

select @.s = coalesce(@.s + ',') + fld
from tbl
where col2 = 'test'

This will concatenate all the values into @.s separated by a comma.

compressed backups

Is there any way to automate compression of timed database backups from a
T-SQL script? I don't see any compression options for the BACKUP command in
BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
A timed command-line batch file using a file compression program could be
set to execute after the script, but the large data file would have to be
created first.
Thanks,
David P. Lurie
David,
There's nothing inside SQL Server to do compression. You could execute a command after the backup that uses
ZIP or similar to do compression. Or use SQL Lite Speed (probably misspelled), which uses an extended stored
procedure to do backup, and this does compression.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David P. Lurie" <abc@.def.net> wrote in message news:%23IaE$vFKEHA.3628@.TK2MSFTNGP12.phx.gbl...
> Is there any way to automate compression of timed database backups from a
> T-SQL script? I don't see any compression options for the BACKUP command in
> BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
> A timed command-line batch file using a file compression program could be
> set to execute after the script, but the large data file would have to be
> created first.
> Thanks,
> David P. Lurie
>

Complex Query Help.. Please :)

Hello group,

First time post..

I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..

Here are the tables.

evaluations - holds a record for each evauation that is created: evalID, evalName

fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)

fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average

fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID

submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID

eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data

Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.

an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as

with any luck this explanation makes sense and the problem will be easy to solve..

:rolleyes:

any replys are greatly appreciated..

Thanks
Willok.. here is some clarification..

I started thinking about the first post and decided that much of that information was probably not needed..

Here is the sql i'm workin with so far..

SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText

my question boils down to this..
regarding the rows in the data table..

can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.

anybody?

Am i on the wrong track?

Am i totally lost?

have i lost everybody else??

thanks
ws
-|||You are right on track, good job.
:D|||me again...

I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..

here is some example data from the data table..

intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28

intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.

make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to

Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..

any help is greatly appreciated.
If you need more info.. please let me know..

thanks
will

Completely Script SQL 2005 Database

Hi Everyone, what im looking for is a way or a freeware utility that will genereate a single SQL Script, for my entire db...

Just like phpMyAdmin does for MySQL...

it would need to script my tables, the data of those tables, and all my stored proc's.

Can SQL Server management studio do this? or does anyone know of a utility that will.

Thanks, Justinhave you tried the Generate Scripts Wizard in SSMS?

You can get to it off any database node in Object Explorer, under the Tasks flyout menu. Look for "Generate Scripts..."

I don't think it can script the data though. For that you might take a look at this addin to SSMS:

http://www.codeproject.com/useritems/enisey.asp|||If you know a little C# and want to try writing your own, SMO exposes pretty much every object in SQL Server, and each one has a Script() method which returns a StringCollection.

So it would be a pretty simple matter to write your own app to do this using SMO. I have done it actually. When I get a chance I'll post it on my site - link below. I'll try to get it posted this weekend.|||Just saw this post, looks like it may be of use to you:

http://www.dbforums.com/showthread.php?t=1610233|||Here's the app I was talking about above. It doesn't script all the data, just the objects. It generates a separate file for each object. I did it that way so it's easy to put a db under source control if it's not already.

http://www.elsasoft.org/tools.htm

Edit: I couldn't help myself. So I just added some code so that scriptdb.exe that will script out the data as well (optionally) using bcp.exe. :)|||Hi,

Thank you for that script, it worked a charm,

how ever i was looking for something that will script it to a .sql file... as in, INSERT INTO type stuff...

Cheers, Justin

complete newbie

would someone me so good as to help me out with the script for a basic
customer table ? , usualy fields, userid (primary key) name, address,
creditcard number, card type, phone number, email address?

TIAHi

Look at

http://www.databaseanswers.org/data_models/index.htm

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"strawberry" <1@.2.com> wrote in message
news:6iA9e.13718$DU6.5013@.newsfe1-gui.ntli.net...
> would someone me so good as to help me out with the script for a basic
> customer table ? , usualy fields, userid (primary key) name, address,
> creditcard number, card type, phone number, email address?
>
> TIA

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Thats not working in SQL 2000. You will have to create it in compat. mode 2k.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Would you please show me a sample code?

Thanks

|||Hi,

the equivalent would be:

CREATE TABLE [dbo].[City](

[CityID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED

(

[CityID] ASC

) ON [PRIMARY]

)

GO

CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])

WITH IGNORE_DUP_KEY ON [PRIMARY]

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)

) ON [PRIMARY]

Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.

|||

Thanks for the reply.

I generated the script using SMO in the following steps:

1. In SMO, right-click on the table City, choose Modify.

2. Highlight CityID, click the "Set Primary Key" button and save the change

3. Right-click on table City, choose "Script table as", choose "Create to"....

Thanks.

|||

Hi

u can't use ignore_dup_key with constraint. u need to create index key.

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

|||

I upgraded my SQL Server 2005 to SP1.

Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.

Problem solved!!

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Thats not working in SQL 2000. You will have to create it in compat. mode 2k.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Would you please show me a sample code?

Thanks

|||Hi,

the equivalent would be:

CREATE TABLE [dbo].[City](

[CityID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED

(

[CityID] ASC

) ON [PRIMARY]

)

GO

CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])

WITH IGNORE_DUP_KEY ON [PRIMARY]

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)

) ON [PRIMARY]

Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.

|||

Thanks for the reply.

I generated the script using SMO in the following steps:

1. In SMO, right-click on the table City, choose Modify.

2. Highlight CityID, click the "Set Primary Key" button and save the change

3. Right-click on table City, choose "Script table as", choose "Create to"....

Thanks.

|||

Hi

u can't use ignore_dup_key with constraint. u need to create index key.

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

|||

I upgraded my SQL Server 2005 to SP1.

Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.

Problem solved!!

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Drop the With( IGNORE_DUP_KEY = OFF ) clause. That is not supported in the Create statement in 2000. It refers to how the index created to support the constraint should react to duplicate keys. This option can be specified if the index is created separately. OFF is the default setting, so removing it will not change the behavior.