Is coalese best for this issue? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is coalese best for this issue?

Author

Topic

kory27
Starting Member

35 Posts

Posted - 2008-03-01 : 14:24:47

Hi, i would like to write a query to take data from fields and essentially concatenate them the data with text. For instance, a field called title, i would like to take that data and say do this;

[Title] posters and prints available here.

What is the most efficient way to do this? I am creating a froogle feed with my data and this would greatly increase my efficiency as I have too many records to export the data to excel and concatenate there.

have a great weekend and thanks again.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 14:41:58

Show some sample data and expected results.

Simple concatonation doesn't require use of Coalesce function,

Poor planning on your part does not constitute an emergency on my part.

kory27
Starting Member

35 Posts

Posted - 2008-03-01 : 15:04:53

for instance, i have a table TblProduct. In it i have things like the field ProductTitle which would have like Orioles 1970 Team photo or something in it. I would like to get out of that in a query something liek;

Buy you Orioles 1970 team photo unframed, framed, or box mounted. Blah, Blah, Blah...

I just would like to know the best practice for simply using data to complete strings that i can then export to a .csv or tab delimited to upload for a feed.

Thanks again.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 15:20:53

You are asking for a specific response to a general question. Best practice would consist of what suits your situation best and the actual environment of your data etc.

So, you have a column (based on your clear example -sic-) that contains the text

"Orioles 1970 Team Photo"

and you want to add string to that...

Select 'Buy you ' + ProductTitle + ' unframed,framed, or box mounted. Blah Blah Blah'
FROM TblProduct

Of course when you upload this into a csv file, it only has 1 column...perhaps that is by design? Would seem you would actually have a ProductTitle, ProductDescrption, FrameType, Price
(or something)

We can't read minds, and there isn't a "black box" that you can just apply.

Be specific so that we can actually help.

Poor planning on your part does not constitute an emergency on my part.

kory27
Starting Member

35 Posts

Posted - 2008-03-01 : 15:36:43

I am sorry. i am trying to understand the underlying practice and principle b/c i honestly just haven't done too many of these queries and i want to be sure i am starting with good habits.

You are right, i will be pulling the following columns from the following tables to make my feed export query;

Link This is in tblproduct.ProductLink
Name This will be pulled and concatenated as follows: tblProduct.ProductName tblProduct.ProductType
Description This will be concatenated as follows; This tblProduct.ProductName tblProduct.ProductType is available as a framed posters, a wood box mounted poster, a laminated poster, or a dry mounted poster.
Price This is in tblProduct.ProductRetailPrice
Image Url This is in tblProduct.ProductIMGUrl2

I hope this is specific enough. Thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 16:10:50

I am not clear on what you want, still.

Show sample data, and sample output.

For example

Data:

ProductLink | Name | Description | Price | Image

ID | 1970 Orioles Team Photo | Photo of entire 1970 Orioles | $20.00 | https\\etc

Desired Output in 1 string:
???????

Poor planning on your part does not constitute an emergency on my part.

kory27
Starting Member

35 Posts

Posted - 2008-03-01 : 16:29:02

Here you go;

Data Fields:

Product Link | ProductName | ProductType | ProductName | ProductRetailPrice | ProductImgURL2

OutPut Results:

Product Link | ProductName + "_" + ProductType | This + ProductName + "_" + ProductType + is available as a framed posters, a wood box mounted poster, a laminated poster, or a dry mounted poster. | ProductRetailPrice | ProductImgUrl2

The fields in between the | above will correspond and be named in my feed as follows;

link name description price image_url

Is that any clearer? Thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 17:22:18

You almost have it just by thinking it through...



Declare @misctext varchar(400)
SET @mistext = ' is available as a framed posters, a wood box mounted poster, a laminate poster, or a dry mounted poster '

Select [Product Link] as link 
        ,ProductName + '_' + ProductType as [Name]
        ,'This ' + ProductName + '_' + ProductType + @mistext +'' as [Description],
        ,ProductRetailPrice as [Price]
        ,ProductImgURL2 as [image_url]
FROM [TblProduct]

You would just use DTS to export that query into a csv or tab delimited file as needed

Poor planning on your part does not constitute an emergency on my part.

kory27
Starting Member

35 Posts

Posted - 2008-03-01 : 17:46:23

Thank you very much. I know, kinda what i am doing, but as i am learning and reading books online and forums, i always try to get a best practice way of doing things b/c without formal training, i fear that i could go down the wrong road and get a bad habit, and even though it may work, there is a much better way of doing things.

Thanks again and have a great rest of your weekend.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 17:47:59

trial and error. And don't be afraid to hit the help button...the books online is a wealth of information and examples.

Poor planning on your part does not constitute an emergency on my part.

kory27
Starting Member

35 Posts

Posted - 2008-03-03 : 16:21:30

this may seem dumb, but is DTS in sql server management studio? i have found a little about Data Tranformation Services online but i haven't used it before and for that matter, i don't know that it is installed.

i ran the query and it works great, but sql is saying something about the declare not being supported, but runs and gives me the data i want anyway. i ran it to give results in txt and then changed my query options to make the text tab delimited, but i have 991K+ records and when i try to save the results it craps out and says it doesn't have the memory. i also can't create a table from this type of query. Any ideas on how, short of copy and pasting the results in a tedious smaller batch manner, i can get this to save so i can make my txt file? is there a good tool specifically for this you could recommend?

thanks again.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-03 : 19:35:10

SSMS is used for SQL 2005, which uses SSIS instead of DTS. DTS was SQL 2000 and has been replaced. I have not used SSMS yet as our server is still 2000 and haven't had the chance to play with it.

You can use this to make a table:


Select [Product Link] as link 
        ,ProductName + '_' + ProductType as [Name]
        ,'This ' + ProductName + '_' + ProductType + ' is available as a framed posters, a wood box mounted poster, a laminate poster, or a dry mounted poster '  as [Description],
        ,ProductRetailPrice as [Price]
        ,ProductImgURL2 as [image_url]
INTO QueryResults ---this will be the resulting table name
FROM [TblProduct]

once it is in a table, you can right-click and choose tasks>export data. You will have the option of saving the "package" if need be for future use (I believe you can save it..)

Otherwise check books on line for exporting data from SSIS (SQL Server integrated Services)

In SSIS you can use a query to determine the data set to export, and you can remove the INTO [TableName] section so that it is just a reqular query.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources