Extracting multiple values as comma seperated

Hi all,

This is my table :

WorkstationNo UserID

101 a1

102 a2

103 a3

101 a2

and there are many other fields too. The output I need is something like this

WorsktationNo OccupiedBy

101 a1,a2

102 a2

103 a3

In the similar fashion I would also require to retrieve the values based on the UserID something like this

UserID Workstations

a1 101

a2 101,102

a3 103

Could someone tell me how to write the query for this.

Here ya go

Code Snippet

create table #t (WorkstationNo int, UserID varchar(10) )

insert into #t

select 101, 'a1'

union all select 102, 'a2'

union all select 103, 'a3'

union all select 101, 'a2'

select WorkstationNo,

substring( ( SELECT ', ' + UserID AS [text()]

FROM #t t2

WHERE t1.WorkstationNo = t2.WorkstationNo

FOR XML path(''), elements ), 2, 1000) as OccupiedBy

from #t t1

group by WorkstationNo

select UserID,

substring( ( SELECT ', ' + convert( varchar(10), WorkstationNo ) AS [text()]

FROM #t t2

WHERE t1.UserID = t2.UserID

FOR XML path(''), elements ), 2, 1000) as OccupiedBy

from #t t1

group by UserID

|||

Thanks DaleJ..

worked like a charm. I guess i will take time to understand the code...

extracting csv string and using in NOTIN clause

Hi,
I'm trying to pass a stored proc a list of values in a comma seperated strin
g:
("3125,3126,3129...") The values represent an integer column in a table.
Then I want to use the string to exclude the values from a SELECT as in:
CREATE PROCEDURE myProc
@.transactionIDs varchar(255)
AS
SELECT TransactionID FROM SalesTransactions
WHERE TransactionID NOT IN (@.transactionIDs)
GO
SQL Server complains that it can't cast a varchar to an int
Any suggestions on how to do this?
Can someone provide an example?
ThanksWhile someone might suggest you use dynamic SQL for this, I
wouldn't. Instead, consider either this not-so efficient solution:
select TransactionID
from SalesTransactions
where
charindex(
',' + rtrim(TransactionID) + ',',
',' + @.transactionIDs + ','
) = 0
-- be sure @.transactionIDs has no spaces
or, for greater efficiency, the more involved solution
that uses a table-valued function to convert @.transactionIDs
into a table of id values:
http://groups.google.co.uk/groups?q...wierzbicki+kass
You can then write:
select TransactionID
from SalesTransactions
where TransactionID not in (
select Item from ListTable(@.transactionIDs)
)
Steve Kass
Drew University
Opa wrote:

>Hi,
>I'm trying to pass a stored proc a list of values in a comma seperated stri
ng:
>("3125,3126,3129...") The values represent an integer column in a table.
>Then I want to use the string to exclude the values from a SELECT as in:
>CREATE PROCEDURE myProc
> @.transactionIDs varchar(255)
>AS
>SELECT TransactionID FROM SalesTransactions
>WHERE TransactionID NOT IN (@.transactionIDs)
>GO
>SQL Server complains that it can't cast a varchar to an int
>
>Any suggestions on how to do this?
>Can someone provide an example?
>Thanks
>
>
>

Extracting a specific amount of data from a field

I am working with a particular field in my SQL database that has the addresses of all our customers. Each part of the address is seperated with a Carriage return but is shown in the database as just a single line of text.
I need to show this in my report as a seperate field for each part of the address (A1, A2, A3 etc..) however I am unsure as to how to extract the data up to each carriage return and then from that one to the next one.
I can use the following which obviously just extracts the data up to the first specified character length but this doesnt really help very much.

SELECT LEFT(ADDRESS, X) AS A1
FROM GTW_BUSINESS_PARTNER

Has anybody got any ideas how this can be done.

Many thanks

From a performance point of view it is probably best, to perform the string splitting operation directly in the SQL statement or in a stored procedure.

If you want to do it with RDL expressions in the report, you could use the VB Split function as follows:

To get the first line: =Split(Fields!Address.Value, VbCrLf)(0)
To get the second line: =Split(Fields!Address.Value, VbCrLf)(1)

Notes:
* VbCrLf means Cr+Lf, you can also try VbCr if you only have Cr between the lines.
* The VB Split function will result in bad performance if used very often (e.g. called multiple times for thousands of rows in the report)

-- Robert

|||The SQL character (ascii) for carriage return is char(13) - char converts to ascii character..... does this point you in the right direction?

Extracting a specific amount of data from a field

I am working with a particular field in my SQL database that has the addresses of all our customers. Each part of the address is seperated with a Carriage return but is shown in the database as just a single line of text.
I need to show this in my report as a seperate field for each part of the address (A1, A2, A3 etc..) however I am unsure as to how to extract the data up to each carriage return and then from that one to the next one.
I can use the following which obviously just extracts the data up to the first specified character length but this doesnt really help very much.

SELECT LEFT(ADDRESS, X) AS A1
FROM GTW_BUSINESS_PARTNER

Has anybody got any ideas how this can be done.

Many thanks

From a performance point of view it is probably best, to perform the string splitting operation directly in the SQL statement or in a stored procedure.

If you want to do it with RDL expressions in the report, you could use the VB Split function as follows:

To get the first line: =Split(Fields!Address.Value, VbCrLf)(0)
To get the second line: =Split(Fields!Address.Value, VbCrLf)(1)

Notes:
* VbCrLf means Cr+Lf, you can also try VbCr if you only have Cr between the lines.
* The VB Split function will result in bad performance if used very often (e.g. called multiple times for thousands of rows in the report)

-- Robert

|||The SQL character (ascii) for carriage return is char(13) - char converts to ascii character..... does this point you in the right direction?