Combining the results of a cursor loop

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuileSET NOCOUNT ON
CREATE TABLE #CurrentRates
(
AccountType VARCHAR(50),
EffectiveDate DATETIME,
tier INT,
gross FLOAT,
net FLOAT,
aer FLOAT,
footnotes VARCHAR(2000),
[id] INT
)
GO

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT #CurrentRates
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData

SELECT * FROM #CurrentRates

Works, but is it good? ;)

Regards

Shaun McGuile|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?|||Works, but is it good? ;) It uses cursors :o|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?

The line

...Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s...

Splits the ids into a table of column product id.

Have I understood your question?

Regards|||I have and always will be the one and only Shaun McGuile ;)

Lost my dbforums password/email combination somehow.|||Forgot to add the 'drop table #CurrentRates' at the end of the code

Doh!|||Beg your pardon - I thought you were parsing as a string.

Ok - what's the pk of dbo.GetProductRateView?|||pk ha ha ha ha ha ha ha - er..sorry Pootle you had me there.

The db has no pk's nor integrity of any type its real bad

dbo.GetProductRateView is a View pulling data from three non normalised tables its really evil - your heart and that of other members of the community might not take the shock of seeing them.

Its like 'The Ring' of databases (like the film - you see it then you die) lol.|||Well brace yourself

SELECT TOP 100 PERCENT dbo.saving_product.name AS AccountType, dbo.saving_product_variant.from_date AS EffectiveDate,
dbo.saving_product_variant.tier, dbo.saving_product_variant.gross, dbo.saving_product_variant.net, dbo.saving_product_variant.aer,
dbo.saving_date.footnotes, dbo.saving_product.id
FROM dbo.saving_product INNER JOIN
dbo.saving_product_variant ON dbo.saving_product.id = dbo.saving_product_variant.link_id INNER JOIN
dbo.saving_date ON dbo.saving_product.id = dbo.saving_date.link_id
GROUP BY dbo.saving_product.name, dbo.saving_product_variant.from_date, dbo.saving_product_variant.tier, dbo.saving_product_variant.gross,
dbo.saving_product_variant.net, dbo.saving_product_variant.aer, dbo.saving_date.footnotes, dbo.saving_product.id
ORDER BY MAX(dbo.saving_product_variant.date_id) DESC|||date_id? :S

Are you getting the most recent row based on the value date_id? If so then you should know that order by clauses are not guaranteed to work in views. Better to create a view with no order by clause and order it when required.

From BoL:
The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.|||What version are you running BTW?|||INSERT #CurrentRates
SELECT TOP 1 v.*
FROM ( SELECT * FROM dbo.GetProductRateView Order By date_id desc) as v
WHERE v.[id] = @.Id

and remove the order by clause from the view?

Regards

Shaun McGuile|||You can do - no need for the inner query BTW. I'm thinking more than that though.

What version are you running?|||SQLServer 2000 is the db.|||Heh - turns out I didn't need it - apols.

SELECT v.*
FROM dbo.GetProductRateView as v
INNER JOIN--"Last" date per product.
(SELECT dbo.saving_product.id
, MAX(dbo.saving_product_variant.date_id) AS last_date_id
FROM dbo.saving_product
INNER JOIN
dbo.saving_product_variant
ON dbo.saving_product.id = dbo.saving_product_variant.link_id
INNER JOIN
(SELECT *
FROM dbo.split_function(@.IdsString)) AS ids
ON ids.Value = dbo.saving_product.id
GROUP BY dbo.saving_product.id) AS last_prods
ON last_prods.id = v.id
AND last_prods.last_date_id = v.date_id
How is that for the data?|||I'll give it a go and let you know.

Cheers Pootle.

Haven't looked in on Yak Coral in ages. Might do it today if I get time.|||Yeah that works splen-diddly (its how you say it out loud that gets ya).

Only modifictions I had to make were field name for the productID instead of value, altered the view to return date_id field and a DISTINCT is needed as in
SELECT DISTINCT v.* ...

Bril thats my homework done! On with the next assignment!

lol only joking! I dont do homework!

Cheers Pootle

Shaun McGuile|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 seconds

Amazing!|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 secondsThat's set based programming for you. The other thing to remember is that speed of the cursor will be linear. Each additional iteration will take ~ as long as the last. Set based stuff mitigates against changes in scale much better.

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 one string

I have a table as follows:
SizeID | Description
1 Extra Small
2 Small
3 Medium
4 Large
5 Extra Large
And then a Product_Size relationship table:
ProductID | SizeID
1000 1
1001 3
1001 4
1001 5
1010 2
1010 3
I want to be able to write a query which returns the size descriptions in
one single string, separated by commas.
For example, if I request for Product #1001, I want the return string to be:
'Medium, Large, Extra Large'
Please show me how this query can be written.
Thanks!You can do this by creating function.
CREATE FUNCTION dbo.GetSize(@.ProductId int)
RETURNS varchar(50)
AS
Begin
Declare @.ReturnValue Varchar(500), @.descripton Varchar(50)
Set @.ReturnValue = ''
Declare ProductSize Cursor
For Select Description
From Product_Size, SizeMaster
Where Product_Size.SizeID = SizeMaster .SizeID
AND ProductId = @.ProductId
Open ProductSize
Fetch Next From ProductSize into @.descripton
While @.@.Fetch_status = 0
Begin
SET @.ReturnValue = @.ReturnValue + @.descripton + ','
Fetch Next From ProductSize into @.descripton
End
Set @.ReturnValue = left(@.ReturnValue,Len(@.ReturnValue)-1)
Close ProductSize
Deallocate ProductSize
Return(@.ReturnValue)
End
Select Distinct ProductId, dbo.GetSize(productID) from Product_Size
Thanks
Baiju
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>|||I made a simillar request yesterday. check the post titled "Concating Values
of a Column based on a Group"
Gopi
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
>I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
> be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>

Combining Fields to string

I have a function that takes a field with values separated by commas within the field and splits them to multiple rows.

Example:
Field - Interior
Value - abc,def,efg,ghi

Output:
ID Item
1 abc
2 def
3 efg
etc

This is working great thanks to help that I received on here.

Now I am combining multiple fields to a string.
Example:
Field1: abc, def
Field2: ghi, jkl

using

SELECT (Field1 + ',' + Field2) From ....

This is working great unless there is a field that has a NULL value. Then I get a NULL result.

Is there an easy way to only put the fields with value into my string and leave out the NULL fields? Some have one NULL field, some have multiple. I just need to get the string to work and get only the fields that have values.

Any suggestions are always appreciated.It has been resolved on another post.

THANKS!!|||

Quote:

Originally Posted by rpeacock

It has been resolved on another post.

THANKS!!

I am having the same problem - can you tell me what other post solved the issue?

Thanks in advance

RIP

Combined result...

hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column?
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.well, daimous, it seems like you did not understand why i moved your previous thread to the microsoft SQL Server forum

so here is the SQL answer --select street||City||postal_code as address
from yourtableif you find that this doesn't work in SQL Server, i trust it will bring to your attention that SQL Server questions should be posted in the SQL Server forum and not the SQL forum

:)|||Try this

Select [street]+', '+[city]+' '+[postal_code] as Address
from YourTable

This assumes that you have [postal_code] defined as a varchar, and not an integer or numeric field. I put in some spaces and a comma, so your output would be something like this:

Street, City Postal_Code|||If you have NULL values in your table and are using default SQL Server settings, you may need to use this:

Select coalesce([street]+', ', '')+Coalesce([city]+' ', '')+Coalesce([postal_code], '') as Address
from YourTable

Now, go open up Books Online and read about concatenation and the COALESCE function.

Combine record?

I want a string that consist full account number as follow:
account_num = 300.111000.10
But table only consist of the following:
-gbmcu : 300
-gbobj : 111000
-gbsub : 10
So how to combine gbmcu,gbobj and gbsub into account_num?
I had tried this T-SQL but fail :
update Bank_Account
set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
go
Error Message
--
Server: Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.
Please help.Sam wrote:
> I want a string that consist full account number as follow:
> account_num = 300.111000.10
> But table only consist of the following:
> -gbmcu : 300
> -gbobj : 111000
> -gbsub : 10
> So how to combine gbmcu,gbobj and gbsub into account_num?
> I had tried this T-SQL but fail :
> update Bank_Account
> set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
> go
> Error Message
> --
> Server: Msg 8152, Level 16, State 4, Line 2
> String or binary data would be truncated.
> The statement has been terminated.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You don't indicate the data types for the columns gbmcu, gbobj & gbsub.
If they are numeric you'll have to convert them to varchar:
set account_num = cast(gbmcu as varchar) + '.'
+ cast(gbobj as varchar) + '.'
+ cast(gbsub as varchar)
This assumes that columns gbmcu, gbobj & gbsub are in the table being
updated. It also assumes that column account_num is a varchar data type
column.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhqwS4echKqOuFEgEQJMPACfSUlNvfxcbuSt
iWy8RFVdCqRy43sAnj/Z
vlg9s7fQcepcdpIQ8bu8Bi1o
=LMc6
--END PGP SIGNATURE--|||The problem could be that your account_number definition is too short to
hold the resulting string. Else, yours and MGFoster's solution should work.
-oj
"Sam" <cybersam88@.hotmail.com> wrote in message
news:%23N4UkqIGFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I want a string that consist full account number as follow:
> account_num = 300.111000.10
> But table only consist of the following:
> -gbmcu : 300
> -gbobj : 111000
> -gbsub : 10
> So how to combine gbmcu,gbobj and gbsub into account_num?
> I had tried this T-SQL but fail :
> update Bank_Account
> set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
> go
> Error Message
> --
> Server: Msg 8152, Level 16, State 4, Line 2
> String or binary data would be truncated.
> The statement has been terminated.
> Please help.
>

Comapring date fields

I am trying to compare two date fields; one is a string and one is a getdate() field. I am trying to get them into the same format so I can compare them. What am I doing wrong? :eek:

select convert(integer, substring(loc_86, 1, 2)) as tmonth,
convert(integer, substring(loc_86, 3, 2)) as tday,
convert(integer, '20'+right(loc_86, 2)) as tyear,
datepart(month, (dateadd(day, -1, (getdate())))) as ymonth,
datepart(day, (dateadd(day, -1, (getdate())))) as yesterday,
datepart(year, (dateadd(day, -1, (getdate())))) as yyear
from ub_chg_tbl join ubmast_tbl
on (ubmast_tbl.patient_nbr = ub_chg_tbl.patient_nbr)
where tmonth = ymonth and tday = yesterday and ty= yyeartry this as a template:


declare @.str char(06)
select @.str = '060228' -- Feb 28 2006
select convert(datetime,@.str)|||How about

where convert(varchar(10), dateadd (dd, -1, getdate()), 101) = loc_86

Depending on your delimiter, of course.|||How closely do you want to compare them? Getdate() returns results in milliseconds. Are you just trying to match on the day?|||I'm only trying to match the day.

When I use this script:

Convert(VarChar(12),GetDate(),112) as '112'

I get a date in a format of YYMMDD.

I need the date in the format of MMDDYY. How can I do this?|||Open BOL. Search the index for CONVERT. Read the topic "CAST and CONVERT". All the formats are given there.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:|||You should NOT be storing date values as strings. Possibly the most common noob DBA mistake of all time. I strongly urge you to change your datetype to datetime.

That said, this should work for you:select *
from YourTable
where datediff(day, getdate(), convert(datetime, left(DateString,2) + '-' + substring(DateString, 3, 2) + '-' + right(DateString,2), 10)) = 0|||I wish we could change it. We are in healthcare and this database is federally regulated, so we are not allowed to change the format of the fields.|||Now I am receiving this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.|||Because you have an invalid date in your table, because you are not using datetime datatypes.

Format your datestring as 'YYYY-MM-DD' and run it through the ISDATE() function to find the bad records.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:SELECT Replace(Convert(VARCHAR(10), GetDate(), 1), '/', '')-PatP|||Now I am receiving this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.As Sallah once told Indy: Bad Dates.

The following shows how to weed them out relatively painlessly:CREATE TABLE #patp_date_demo (patp_date CHAR(6))

INSERT INTO #patp_date_demo (patp_date)
SELECT '122505' UNION
SELECT '022900' UNION
SELECT '022901' UNION
SELECT '063104' UNION
SELECT '131211'

SELECT patp_date
FROM #patp_date_demo
WHERE 0 = IsDate(Stuff(Stuff(patp_date, 5, 0, '-'), 3, 0, '-'))

DROP TABLE #ptp_date_demo-PatP|||Thanks so much! The following script worked:

REPLACE(CONVERT(varchar(10), DATEADD(day, - 1, GETDATE()), 1), '/', '') AS Yesterday

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Columns & rowns interchage on string the data

Hi,

Is it possible to interchange column & row data from the t-sql on
storing into a file?

Thanks

*** Sent via Developersdex http://www.developersdex.com ***Simon,

Thanks for your reply. I woule like the following output data

col1 col2 col3 ......

row1 r1c1 r1c2 c1c3 ...

row2 r2c1 r2c2 r2c3 ....

row3 r3c1 r3c2 r3c3 ......

..
..
..

to be output like

row1 row2 row3 ......

col1 r1c1 r2c1 r3c1

col2 r1c2 r2c2 r3c2

col3 r1c3 r2c3 r3c3
..
..
..

Thanks

*** Sent via Developersdex http://www.developersdex.com ***|||I'm not sure I understand your question, but I think you're asking if
you can export a crosstab report to a file? If so, a reporting tool is
probably the best general solution (MSSQL Reporting Services, Business
Objects etc), but you could create your own qrosstab queries:

http://www.aspfaq.com/show.asp?id=2462

To export the results to a file, you could use osql.exe, bcp.exe or
DTS.

Simon|||Here's an example:

CREATE TABLE foo (foo_key INTEGER PRIMARY KEY, x INTEGER NOT NULL, y
INTEGER NOT NULL, z INTEGER NOT NULL) ;

INSERT INTO foo (foo_key, x, y, z)
SELECT 1,10,11,12 UNION ALL
SELECT 2,20,21,22 UNION ALL
SELECT 3,30,31,32 ;

SELECT foo_key, x, y, z
FROM foo ;

SELECT 'x' AS col,
MAX(CASE WHEN foo_key = 1 THEN x END) AS row1,
MAX(CASE WHEN foo_key = 2 THEN x END) AS row2,
MAX(CASE WHEN foo_key = 3 THEN x END) AS row3
FROM foo
UNION ALL
SELECT 'y',
MAX(CASE WHEN foo_key = 1 THEN y END),
MAX(CASE WHEN foo_key = 2 THEN y END),
MAX(CASE WHEN foo_key = 3 THEN y END)
FROM foo
UNION ALL
SELECT 'z',
MAX(CASE WHEN foo_key = 1 THEN z END),
MAX(CASE WHEN foo_key = 2 THEN z END),
MAX(CASE WHEN foo_key = 3 THEN z END)
FROM foo ;

--
David Portas
SQL Server MVP
--

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column max size for String

Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?

Many thanks

GrantVarchar can go up to 8000. You can usetext orntext fields which have no practical upper limit on the number of chars. Withtext orntext fields fields you lose some flexibility such as the ability to use thetext orntext field in a where clause.

text is for variable-length non-Unicode data.

ntext is for variable-length Unicode data.|||text datatypes can hold upto 2GB bytes of data ... And ntext also used the same limitation ... The only difference being text datatypes use 1 bytes for 1 character and ntext types use 2 bytes for storing single unicode character ...