Combining strings

Let's assume we have two tables - Customers and Orders.

I need a query that will return a string value containing a list of order titles from the Orders table for a particular customer.

How can this be done?

Thanks.

Hi vkh,

you have to use a function approach for this, as it can be seen on (sort of, I would vary this one to a temporary table rather than a cursor, but just to show you the iterative approach)

http://www.sqlteam.com/item.asp?ItemID=2368

HTH; jens Suessmeyer.

|||Thank you!

Combining results in Comma delimitered strings

I know this has been addressed before but I can't find it...
I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...
Anyone able to help?DECLARE @.commadelimitedthisisanannoyingstringname VARCHAR(8000)

SELECT @.thatstringupthereyouregoingtonottypethis = ''

SELECT @.String = PersonId + ', ' + @.String
FROM Person

SELECT LEFT(@.String, LEN(@.String)-1)

or something like that|||Thanks, that seems to work,... I didn't think it would but some how it does...|||Thanks, that seems to work,... I didn't think it would but some how it does...

It should have worked. :) Why would you not think it would, just curious.|||I would have thought that for that structure to work there would have had to be some sort of loop or something...

I thought I had seen it done using some other function like coalesce or something but when I read the help it didn't look right...

Hmmm,... actually thinking about it now it makes sense... basically it ignores what if selected in the table select and just selects the final built up string...

Seems a little inefficient, is there a better way??|||Actually, if you do a search here or on www.sqlteam.com, you'll find all kinds of ways to do it. :) You can do it with a function using COALESCE. I think for a single string though, this one is pretty efficient.|||Come on, derrick, NOBODY here thought that would work the first time we saw it months back! It's probably one of the most popular tricks on the forum!|||?? You're joking right? I have scripts going way back using that.|||I know that folks use the living stuff out of this construct, but it shouldn't work. It actually violates the SQL-92 standard, since the column value is derived iteratively. My guess is that once the standards committee realizes that this flaw exists in a widely used SQL dialect, they'll add a test for it to the test suite and shortly thereafter Microsoft will either eliminate the behavior or make it switch dependant.

-PatP|||Yup, it's a nifty little thing that has gotchya's people don't think about, and as always tend to overuse it. Like with everything else that is non-standard, when it comes to an end, a lot of these guys are gonna be screaming bloody hell, but it's only because they were lazy at the beginning, though not lazy enough to prevent it right there, and have a beer ;)|||Yeah, but it is lots of fun to watch them running around, screaming to the four winds when the database engine behavior is fixed and their code breaks. As you are fond of observing, those who ignore history are doomed to repeat it!

-PatP|||Okie then guys,... what is the right way to do it? To be honest I have actually implemented a different solution as I didn't get a reply in time so it isn't going to cause me any problems but I would be interested in knowing if there is a proper way or not...|||The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||Well that answer gains zero points for usefullness...

No offense meant. For the purposes of the project I am doing I need a comma delimited string of all the id's. This is not for displaying but for internal processing. A string may not be the best option but at this point it time is the most flexible and the project at hand. The truth is that there is no front in for the problem I am working on. It's a logic problem that I want to solve. Once it is solved I might decide that it is no use or I might decide that there is a front end requirement but at this point there is not.

Now while I agree that I probably don't want SQL to return a comma delimited string because it is a misrepresentation of the actual data I do want to know what is the best way of getting sql to return a comma delimited string so I know what I am talking about when I rule it out as an option.|||No matter how the string is getting formed, it's still limited to the total length of 8000 character (wonder how many times I tried to hint it?:rolleyes: )

Why don't you tell us what the app is for, and why it needs a comma-delimited listing of PersonIDs? Someone (maybe me) would be able to come up with an alternative, hey?! ;)|||I think the answer to your hint question is once (in this post anyway).

The string is used for determining individual branches in a family tree. The processing is all done using asp/vbscript. An array might be better then a string but because you are shuffling data into and out of other stings/arrays at this point a string is the best contruct to test the theory and determine it's usefulness.

At this point conversion of a recordset to a string is simplier then a recordset to an array.

The actually processing overhead of using a string is probably higher but until I determine the usefulness of the function their is little point moving to an array which would be more complex at this point.

Before you ask, yes, you are stuck with asp and vbscript. It can't go to a vb component and it can't upgrade to asp.Net.

Knock yourself out.|||Hey-hey-hey, with that kind of attitude, YOU knock yourself out, not me, alright?! I was just trying to help you (which is why I choose to post here), but as you MIGHT have noticed, it's NOT my problem. If you think you're better than that, - KNOCK YOURSELF OUT, do yourself a favor.

EDITED: and BTW, I hinted on VARCHAR limitation at least twice. I simply didn't think that such an obvious thing as this needs to be mentioned...Obviously it does, hey?!|||*sigh* why is it that people are easily offended...

Look, I appreciate what you are saying and I appreciate your input, the "knock yourself out" line is a standard line from where I come from to say "go for it and good luck". I'm sorry it offended you.|||Fair dinkum. :cool:|||WHAT WAS WRONG WITH THIS ANSWER? Tel me straight, please, I I promise I will take a serious consideration over how I post my answers...The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||What's wrong with the answer is it doesn't actually answer the question.

The question is, is there a proper way to create a comma delimited string in the manner described within SQL (and if there is, what is it).

It is not, how should I build my application so that I do not have to use a comma delimited string, nor is it what restrictions will I put in place by using a comma delimited string.

I'd be quite happy if some one answered "there is no 'proper' way to generate a comma delimited string from sql". I am aware that there are going to be limitations, but in some cases (not this one) the limitation may not come into play (depending on data and data structures).|||Hey, have it your way. I hope the the limitation pointed out is not gonna be a problem for you ;)|||Eventually it would be, but since this was only to test a theory it's not going to and like I said previously I have actually gone about it a different way, but it would still be interesting to know if there is a "proper" way to do it...

An example of where it might be useful is when you want a comma delimited list of the months that are stored in a particular table. You can be certain that the length of the string will not exceed 8000 characters...

I'm not sure where else it might be useful but there are bound to be others.

Thanks for your input.sqlsql

Combining Fields in a stored procedure

my question is this...I have to fields I want to retreive..One is a nvarchar and the other is an integer...I want to return them in this format
(interger field + '/' + nvarcharfield) as combinedfield
problem is i get errors when I try to get this value
I just need the info I know you cant add theses two together...

Example of output needed...

31/OfficeVisit

my sp

ALTER procedure EncounterCodes_NET
(
@.ClinicID int
)
as
select
CombinedField=(EnCodeID + EnCodeDesc)
from
Clinic_Encodes
Where
ClinicID=@.ClinicID
Order by Sortorderreplace the EncodeID with

Cast(EnCodeID as varchar)

Combine Multiple Results into 1 RecordSet

Hello All
I have the following SPROC Below which I want to return the results of the 3
Querries in a single record Set which I can use in my webapp
/ ****************************************
****************
CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
AS
SELECT Count(*) As Total FROM vSQLInv_VersionString
SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
SELECT Count(*) As Valid FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
GO
****************************************
***************/
-- Desired Results --
Total Vulnerable Valid
80 5 75
Thanks
StuartSELECT Count(*) As Total,
SUM(CASE WHEN Status = 'Vulnerable' OR Status = 'EOF' THEN 1 ELSE 0 END)
As Vulnerable ,
SUM(CASE WHEN Status <> 'Vulnerable' THEN 1 ELSE 0 END) As Valid
FROM vSQLInv_VersionString
Jacco Schalkwijk
SQL Server MVP
"Stuart Shay" <sshay@.j51.com> wrote in message
news:umqEkOZMFHA.2384@.tk2msftngp13.phx.gbl...
> Hello All
> I have the following SPROC Below which I want to return the results of the
> 3 Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>|||SELECT
(
SELECT Count(*) FROM vSQLInv_VersionString
) AS Total ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
) As Vulnerable ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
) As Valid
FROM
vSQLInv_VersionString
Cheers,
Greg Jackson
PDX, Oregon|||SELECT Count(*) As Total,
Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as Vulnerable,
Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
FROM vSQLInv_VersionString
"Stuart Shay" wrote:

> Hello All
> I have the following SPROC Below which I want to return the results of the
3
> Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>
>|||Thanks & Have A GREAT Day !!!!!
Stuart
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:41A33FEB-3805-411F-A03D-459849349C05@.microsoft.com...
> SELECT Count(*) As Total,
> Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as
> Vulnerable,
> Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
> FROM vSQLInv_VersionString
>
> "Stuart Shay" wrote:
>

Combine 2 select statment result in one table

Hi All,
I am new in the Fourm.
I have a one Problem. I have a two select stament that are return 2-2 column that are in the differnent table.
I want that return in one table......

Ex I am writing 2 select statment below..
1. select count(product.ProductID) as productCount,AffiliateHits.affiliateID,product.Name as [Name],product.ProductID
from AffiliateHits,product
where AffiliateID like 'SELF'--@.affiliateID
and product.ProductID=AffiliateHits.ProductID
group by product.ProductID,AffiliateHits.affiliateID,product.Name

2.
select count(*)as OrderCount,orderdetail.productid from [order],OrderDetail
where [order].sourceID like @.affiliateID and
[order].addedon between @.fromDate AND @.toDate AND
[order].orderID=OrderDetail.orderid AND
OrderDetail.ProductID in (select distinct(productid)from AffiliateHits where AffiliateID like @.affiliateID)
group by orderdetail.ProductID

Plz Reply asap.

Thankx

Two ways...UNION ALL, or CROSS JOIN. UNION can be used to take two sets and combine them, column by column. So:

select column1
from table1

UNION ALL

select column2
from table2

This might produce a result like

column1
-
1
2

I don't think this is what you need. Cross join

select <columnNames>
from (select column1
from table1)
CROSS JOIN --or possibly join, if there is some relationship between the sets and multiple rows
( select column2
from table2)
--ON --if these are multi row sets (you might JOIN ON productId)

This might produce a set like (if each set returned one row)

column1 column2
--
1 2

I cant tell from your group by if either of these make sense, because your two sets have different group by clauses. Does this help? Can you simplify your example so one of us can make you a functional (with data) example?

Column size

Hello World,
If Len (Expression) return the length of Expression
What will return the size of column ?
Thank's in advance.
MLHow do you define "size of column"? Storage size? If so, use the DATALENGTH(
) function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michel" <m.landrain@.wanadoo.fr> wrote in message news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl
..
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||The size of a column when created?
use the view INFORMATION_SCHEMA.COLUMNS and look for column
CHARACTER_MAXIMUM_LENGTH
For the length of data stored in a field you can use DATALENGTH
select DATALENGTH( Fielname) ,FieldName from YourTable
http://sqlservercode.blogspot.com/
"Michel" wrote:

> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||A precision,
For exemple,
A column is defined as VARCHAR (10)
The size of column is 10 cars.
What will return 10 (the size of column) ?
"Michel" <m.landrain@.wanadoo.fr> a crit dans le message de
news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl...
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||Maybe this example can be of help:
use pubs
select INFORMATION_SCHEMA.[COLUMNS].CHARACTER_MAXIMUM_LENGTH as DeclaredLength
,INFORMATION_SCHEMA.[COLUMNS].CHARACTER_OCTET_LENGTH as ActualLength
from INFORMATION_SCHEMA.[COLUMNS]
where (INFORMATION_SCHEMA.[COLUMNS].TABLE_NAME = 'authors')
CHARACTER_MAXIMUM_LENGTH is what you are looking for, yet consider also the
CHARACTER_OCTET_LENGTH column for unicode data types.
More on this here:
http://msdn.microsoft.com/library/d...br />
87w3.asp
ML
p.s. not the same ML obviously. :)

column name alias concatenation

I have a web application where I would like to return a dynamic column name using aliasing. below is an example:

select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours

I get an error trying to do concatenation as part of the alais. Any ideas?

Luke
lgraunke AT 4invie.comWhats <BR>

Is this being done in SQL Server?|||Ideally I would like the column name/header to show something like 'Fri<BR>20'. The '<BR>' is just some web formating that is automatically incorporated.|||This should float your boat...

USE Northwind
GO

DECLARE @.cmd varchar(8000)

SELECT @.cmd = 'SELECT Quantity AS ['
+ CASE DATEPART(WeekDay,GetDate())
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
+ '<BR>'
+ cast(Day(getDate()) as varchar(2))
+ '] FROM [Order Details]'

SELECT @.cmd

EXEC(@.cmd)|||Thanks, that was exactly what I was looking for.

Column Count Percentage of Not Null fields

Hello folks,

I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.

For instance, a row from my table:
Row1 : field1 field2 field3

If field3 is empty or null, my query should return 67%.

So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'

I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.

Thanks for your help
Mikelet me get this straight -- you don't really know which columns the table will have?|||Do you want to inspect the content of each column in each row OR
Is your intention instead to find the number of columns in a table that do not allow nulls?

select object_name(id),colcount=count(*)
,notnullablecount=sum(case when isnullable=0 then 1 else 0 end)
,nullablecount=sum(case when isnullable=1 then 1 else 0 end)
,'Notnullable%'=convert(int,sum(case when isnullable=0 then 1 else 0 end)*100./count(*)+.5)
from syscolumns
where id=object_id('myTable')
group by id