Concatenate Query Results into a String most effectively

Hi all,
I want to concatenate the results from a query in a string in the most
performance-friendly manner. E.g. executing
SELECT Code, Price
FROM Inventory
returns
ABC, 120
EFG, 200
HIJ, 245.50
...
Instead of returning the result in the format above, I want it to look like
this
ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
returned as a string, or a column.
The normal way is to use cursor, and loop all the rows and concatenate the
values in each row into the string. BUT I read cursors are not
performance-friendly.
So what is the most performance-friendly SQL statement(s) that I can write
(preferably in one statement) to do the above?
Please kindly advise. TQ very much in advance.Hi
Why not doung such things on the client side'?
CREATE TABLE #Test
(
col CHAR(1)NOT NULL,
col1 DECIMAL(5,2) NOT NULL
)
INSERT INTO #Test VALUES('A',120)
INSERT INTO #Test VALUES('B',155)
INSERT INTO #Test VALUES('C',20.33)
DECLARE @.st VARCHAR(100)
SET @.st=''
SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
SELECT @.st
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> Hi all,
> I want to concatenate the results from a query in a string in the most
> performance-friendly manner. E.g. executing
> SELECT Code, Price
> FROM Inventory
> returns
> ABC, 120
> EFG, 200
> HIJ, 245.50
> ...
> Instead of returning the result in the format above, I want it to look
like
> this
> ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
> returned as a string, or a column.
> The normal way is to use cursor, and loop all the rows and concatenate the
> values in each row into the string. BUT I read cursors are not
> performance-friendly.
> So what is the most performance-friendly SQL statement(s) that I can write
> (preferably in one statement) to do the above?
> Please kindly advise. TQ very much in advance.|||Hi,
Thanks. Cool solution. Never think abt that!
"Uri Dimant" wrote:

> Hi
> Why not doung such things on the client side'?
Well I am customizing an existing program... So try to keep my changes
minimum and yet still get good perforamance. ;)
Furthermore doing such things on client side would mean I have to write
additional VB codes and Crystal Report to reformat the whole thing. SQL
statements still so much more powerful.

>
> CREATE TABLE #Test
> (
> col CHAR(1)NOT NULL,
> col1 DECIMAL(5,2) NOT NULL
> )
> INSERT INTO #Test VALUES('A',120)
> INSERT INTO #Test VALUES('B',155)
> INSERT INTO #Test VALUES('C',20.33)
> DECLARE @.st VARCHAR(100)
> SET @.st=''
> SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
> SELECT @.st
>
>
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> like
>
>

Computer techies/ Engineers. IT people. Question about SQL.

Computer techies/ Engineers. IT people. Question about SQL.

Some background to help to answer my question more effectively.

I am a very analytical person. My best attribute is the way I go about solving a problem ... IMO. Many people try their own ways ... and some do better than others. In any class I have ever been in ... I was either the person who never showed up or didnt pay any atention and fooled around ... or the person who asked so many questions that people in class got perplexed/frusterated etc with me ... but I got the right answers ... and the teacher enjoyed the analytical discussion .. and I performed better than everyone else ... and got the best results.

I have an opportunity to benefit very well form SQL knowledge. I would like to learn as much as I can in a very short period of time.

My father is a computer engineer/business owner ... so I am fairly oriented with the business concepts ... but I am no computer wiz by far ... and I do not have him as an option to consult with at this time. I do learn fairly quickly though ... and if I seem slower than others ... then I will probably provide better results.

I own my own sales and marketing business ... and am usually very ambitious ... so I am business oriented.

I am wondering how technical or how easy learning SQL may be ... and if anyone has any pointers or insight as to weather or not this is feasable to learn enough to put to work ... in a short period of time.

I am not even sure if I want to persue this ... but I would like to see what people may comment.

Thanks. Best regards.Forgot to mention that a quick gogle search yeilds some potentially excellent online SQL courses. Some o the process seems very easy though .. and I am wondering if I am underestimating SQL/getting a false impression about how "easy" it may be to master.

http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=sql

The w3scools and sqlcourse pages seem very helpful and user friendly.

LMK if this is what I would need to master SQL.

In order to benefit form my skills I need to ... quote:

" Have a basic understanding of SQL process."

"Automate information in SQL and create necessary notifications."

"EXPERIENCE IN DATA EXTRACTION,MANIPULATION AND ANALYSIS.INFORMATION SYSTEMS KNOWLEDGE REQUIRED PARTICULARLY SQL AND ENTERPRISE MANAGER. STRONG PROGRAMMING/QUERY SKILLS."

Any helpful tips ... please let me know. Thanks.|||In many ways, SQL is like chess or go. Quick and easy to learn, a lifetime to master.

You can learn enough SQL to be useful in about 8 class hours (at a reasonable pace). That means that you know just enough to be dangerous, not that you are any kind of expert.

After that, most people need about 60-80 days of normal use to get to a "plateau". Another roughly 40 hours of class will then get you past that, and into the "real world" of SQL.

After about a year, you may find that you want to get into the "inner workings" of the beast, and learn what makes it tick. There are many courses, with different focuses and various degrees of success. At this point, you'll have a better guess at what makes sense for you than I would!

-PatP|||Thank you for the reply. Glad to now I could be useful with some brief schooling. Sounds like I may be able to "pull this off".

I am networking with some local schools. So far .. I think I can get what I need with about 7 days or 40-60 hours of training ... for an approximate price of 3k$.

Sound reasonable?

Also, since the job offer particularly mentions SQL and Enterprise Manager .. does this mean that the software they are using must be Microsoft SQL .. either 7.0 or 2000? .. Or is Enterprise Manager used with other applications?

Thanks.|||The $3000 sounds rather steep to me, but you always pay for what you get, and sometimes you get what you pay for... It is quite possible that they offer enough value to justify that price.

I can't imagine Enterprise Manager working with anything other than SQL Server. It is very tightly wired into the Microsoft framework.

-PatP|||Hmm. Is SQL Server what I may be getting at?

^^Vuage question.

I guess I ask because I am hearing things abou Sequel Server/ SQL Server / Transact SQL / GUI SQL / Blah blah blah .. and some seem interchangeable and some seem completely different.

Elaborate?|||SQL is an acronym for Structured Query Language. There are both ANSI and ISO standards that define just what SQL is, and how it is supposed to work. I can't think of any vendor that is 100% compliant with the standard, but they all implement something that they call SQL Server.

While other vendors used their own interesting names, like DB2 (Data Base 2), and Oracle (the all knowing prophets of ancient Greece), Sybase had the chutzpah to name their product "SQL Server". Microsoft inherited the Sybase product, and they continue to use the name even though the product has evolved quite a bit.

Microsoft's SQL Enterprise Manager (aka EM) is tightly woven into Microsoft's SQL Server. I don't think you could use EM with any other database.

-PatP|||Knew the first paragraph.

Knew some of the 2nd paragraph.

3rd paragraph definitely helps to move forward.

I saw some stuff about Sequel though ... differentiated from SQL ... and some other things that could be similar ... but also different.

Somebody mentioned that Sequel is what some people call SQL ... but then I am always talking about basic Structured Query Language ... and they differentiate somehow according to others. I also have seen/heard ... two different phrases ... SQL Server and Sequel server ... possibly even provided as separate courses.

I'm a little lost right now .. but I'll piece it all together.

I got to chat with about half of the people I wanted to today. Tomorrow I will try to catch more people during business hours.

Just trying to move forward.

Thanks for the insight, sincerely.|||Sequel is a lot easier to say than S-Q-L. Most of the folks that market (both products and training) go for easy answers. Some of them get intellectually sidetracked and think that they can coin (or worse yet, redefine) new terms. I won't go in to the amount of confusion and general grief that has caused over the years...

-PatP|||SQL is pronounced as an initialism, not as an acronym

the only people that pronounce it as an acronym are people discussing microsoft's products (i do this too)

when i am talking to an actual microsoft certified person, i always make sure to pronounce it as "squeal"|||Originally posted by r937 when i am talking to an actual microsoft certified person, i always make sure to pronounce it as "squeal" I'm sure that scores enormous "brownie points" with them!

-PatP|||Originally posted by r937
when i am talking to an actual microsoft certified person, i always make sure to pronounce it as "squeal"
That reminds me of when I was working alongside Andersen Consulting consultants on a SAP implementation. As my "a" just implied, I pronounce it "sap", whereas the AC bods were instructed from on high that it must always be called S.A.P., and tried unsuccessfully to persuade the rest of us to do likewise. What a waste of breath!

Complex queries using WHERE and mix of OR and AND

How do you effectively mix OR and AND together? I have the query below for m
y
SEARCH page. I would like the users to have the option of selecting one fiel
d
to search with OR selecting pairs of fields together to search the database
with. The problem is, with the SQL statement below, OR works (select 1 field
to search with) but AND does not (if I use more than 1 field to search with,
the search returns all entries in the database.
Could someone pls point out to me what I'm doing wrong? I'd really
appreciate it.
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE (vNXX LIKE 'varNXX' AND vLN LIKE 'varLINE') OR (vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR
(vMN LIKE 'varMONTH' AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR (vM
N
LIKE 'varMONTH' AND vYR LIKE 'varYEAR') OR (vNXX LIKE 'varNXX') OR (vLN LIKE
'varLINE') OR (vMN LIKE 'varMONTH') OR (vDT LIKE 'varDATE') OR (vYR LIKE
'varYEAR') OR (vAGNT LIKE 'varAGENT') OR (vORD LIKE 'varORD') OR (vSAVE LIKE
'varSAVE')
ORDER BY vMN DESC, vDT DESC, vYR DESC> AND does not (if I use more than 1 field to search with,
> the search returns all entries in the database.
Could you post a working example of this so that we can understand what you
mean. There's no reason why you can't use as many ANDs and ORs as you need
in a WHERE clause. AND takes precedence over OR unless you use brackets to
alter the order of evaluation.
David Portas
SQL Server MVP
--|||Hi David,
First I formatted your SQL using www.sqlinform.com .
Then I have seen that some conditions are not logic, e.g. using
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
together with
(
vNXX LIKE 'varNXX'
)
because this condition is true independent from the value of vLN. You
will need to code your SQL in a different way.
Regards
Guido
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
)
OR
(
vNXX LIKE 'varNXX'
)
OR
(
vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
)
OR
(
vDT LIKE 'varDATE'
)
OR
(
vYR LIKE 'varYEAR'
)
OR
(
vAGNT LIKE 'varAGENT'
)
OR
(
vORD LIKE 'varORD'
)
OR
(
vSAVE LIKE 'varSAVE'
)
ORDER BY vMN DESC, vDT DESC, vYR DESC