Help ordering IN clause using passed order

I am trying to make the IN clause of a stored procedure return the rows in
the order in which the IN clause values were specified. What I have is a
table that can be sorted on a number of columns yet I want to pull back a
subset of rows. I have the set of rows needed but I am unable to return the
rows in the correct order using the IN clause.
For example:
SELECT
T.ID,
T.Name
FROM
MyTable
WHERE
T.ID IN ('1,3,2')
Notice that the ID order is 1,3,2. I want the rows in that order without
having to use an Order By on the correct column. That would require that I
a) use dynamic SQL just to use the correct order by column or b) provide the
same query a bunch of times just changing the sorting. I would prefer to not
do either.
Is this possible in SQL Server?One option is to use a CASE expression like:
ORDER BY CASE id WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
END ;
For a general option, use CHARINDEX or PATINDEX function like:
ORDER BY CHARINDEX( ',' + @.list + ',', ',' + id + ',' ) ;
Anith|||Tim Menninger wrote:
> I am trying to make the IN clause of a stored procedure return the rows in
> the order in which the IN clause values were specified. What I have is a
> table that can be sorted on a number of columns yet I want to pull back a
> subset of rows. I have the set of rows needed but I am unable to return th
e
> rows in the correct order using the IN clause.
> For example:
> SELECT
> T.ID,
> T.Name
> FROM
> MyTable
> WHERE
> T.ID IN ('1,3,2')
> Notice that the ID order is 1,3,2. I want the rows in that order without
> having to use an Order By on the correct column. That would require that I
> a) use dynamic SQL just to use the correct order by column or b) provide t
he
> same query a bunch of times just changing the sorting. I would prefer to n
ot
> do either.
> Is this possible in SQL Server?
You should know that you cannot reliably order any query without using
ORDER BY. Try:
DECLARE @.in VARCHAR(100)
SET @.in = '1,3,2'
SELECT T.id, T.name
FROM MyTable
WHERE CHARINDEX(','+CAST(id AS VARCHAR)+',',','+@.in+',')>0
ORDER BY CHARINDEX(','+CAST(id AS VARCHAR)+',',','+@.in+',');
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I'd try to modify Erland Sommarskog's UDF iter_charlist_to_table that
parses a comma-separated string, found at
http://www.sommarskog.se/arrays-in-sql.html
CREATE FUNCTION iter_charlist_to_int_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
value int,
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos,
@.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (value, nstr) VALUES(cast(@.tmpval as int),
@.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(value, nstr) VALUES (cast(ltrim(rtrim(@.leftover)) as
int), ltrim(rtrim(@.leftover)))
RETURN
END
create table #t(i int)
insert into #t values(1)
insert into #t values(2)
insert into #t values(3)
insert into #t values(4)
insert into #t values(5)
select #t.i from #t, dbo.iter_charlist_to_int_table('1,3,2', ',') t
where #t.i=t.value
order by t.listpos
i
--
1
3
2
(3 row(s) affected)|||Your IN clause has only one member. You are confusing IN (1,3,2) and IN
('1,3,2'). You probably need to use dynamic SQL anyway to get your query
working the way you want it.
For example, with this:
create table fred
(
ID varchar(2),
Name varchar(100)
)
go
insert into fred(ID,Name)
select '1','Jim' union
select '2','Tom' union
select '3','Appleby'
select id,name from fred where id in ('1,2')
The select doesn't return anything. If ID were an int, you would get a
syntax error in the select statement
"Tim Menninger" <tmenninger@.comcast.net> wrote in message
news:e77bacLMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am trying to make the IN clause of a stored procedure return the rows in
>the order in which the IN clause values were specified. What I have is a
>table that can be sorted on a number of columns yet I want to pull back a
>subset of rows. I have the set of rows needed but I am unable to return the
>rows in the correct order using the IN clause.
> For example:
> SELECT
> T.ID,
> T.Name
> FROM
> MyTable
> WHERE
> T.ID IN ('1,3,2')
> Notice that the ID order is 1,3,2. I want the rows in that order without
> having to use an Order By on the correct column. That would require that I
> a) use dynamic SQL just to use the correct order by column or b) provide
> the same query a bunch of times just changing the sorting. I would prefer
> to not do either.
> Is this possible in SQL Server?
>|||Someone was asleep in RDBMS 101 class! What is the definition of a
table? It models a set of rows. By definition a set has no ordering.
This is what you should have learned the first w in class.
Do this in the front end, where all formatting and presentation is done
in a tiered architecture (w #2) or with an ORDER BY clause to
convert from a tale to a cursor.sql

Help optimising a stored proc

Hi I have the following procedure that accepts two CSV lists of values,
the first list contains primary key numbers, and the second values to
update.
When the list gets over about 200 items, I am getting intermittent
Timout errors.
Currently just over 500,000 records in the table.
Is there a way to optimise the performace of the update? Or is theer an
easier way if the input can be provided in a single list
e.g 2245=1,2257=2,3367=2 instead of
2245,2257,3367 and 1,2,2
Thanks!
CREATE Procedure dbo.UpdateResults
@.RegistrationIDs Varchar(8000),
@.Results Varchar(4000)
AS
UPDATE tblRegistrations
SET Result
= (SELECT A.Value FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value )
WHERE EXISTS (SELECT *
FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value)
CREATE FUNCTION dbo.Split
(
@.List varchar(8000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(
Id int identity(1,1),
Value nvarchar(150)
)
AS
BEGIN
While (Charindex(@.SplitOn,@.List)>0)
Begin
Insert Into @.RtnValue (value)
Select
Value =
ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
Set @.List =
Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
End
Insert Into @.RtnValue (Value)
Select Value = ltrim(rtrim(@.List))
Return
ENDI would try using temporary tables instead of table variables. I have seen
some strange stuff happen when trying to join two table variables together,
or joining a table-valued function together with a real base table.
Move the logic of the dbo.split funtion inside the stored procedure, take
the input csv strings and write them into a temporary table, then process
from that temp table.
"hals_left" wrote:

> Hi I have the following procedure that accepts two CSV lists of values,
> the first list contains primary key numbers, and the second values to
> update.
> When the list gets over about 200 items, I am getting intermittent
> Timout errors.
> Currently just over 500,000 records in the table.
> Is there a way to optimise the performace of the update? Or is theer an
> easier way if the input can be provided in a single list
> e.g 2245=1,2257=2,3367=2 instead of
> 2245,2257,3367 and 1,2,2
> Thanks!
>
> CREATE Procedure dbo.UpdateResults
> @.RegistrationIDs Varchar(8000),
> @.Results Varchar(4000)
> AS
> UPDATE tblRegistrations
> SET Result
> = (SELECT A.Value FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value )
> WHERE EXISTS (SELECT *
> FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value)
>
> CREATE FUNCTION dbo.Split
> (
> @.List varchar(8000),
> @.SplitOn nvarchar(5)
> )
> RETURNS @.RtnValue table
> (
> Id int identity(1,1),
> Value nvarchar(150)
> )
> AS
> BEGIN
> While (Charindex(@.SplitOn,@.List)>0)
> Begin
> Insert Into @.RtnValue (value)
> Select
> Value =
> ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
> Set @.List =
> Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
> End
> Insert Into @.RtnValue (Value)
> Select Value = ltrim(rtrim(@.List))
> Return
> END
>|||Hi There,
What Mark suggested is right but you may like to try this .
UPDATE T1
SET Result = A.Value
>From FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
Join tblRegistrations T1 On T1.RegistrationID=B.Value
and remove the split function If possible .
With Warm regards
Jatinder Singh
Mark Williams wrote:
> I would try using temporary tables instead of table variables. I have seen
> some strange stuff happen when trying to join two table variables together
,
> or joining a table-valued function together with a real base table.
> Move the logic of the dbo.split funtion inside the stored procedure, take
> the input csv strings and write them into a temporary table, then process
> from that temp table.
> --
> "hals_left" wrote:
>|||Thanks Jatinder, that works nice on a small test and is much simpler
SQL.
I'l test in on the real database and see how it compares to the
original
I dont really see how can I remove the split function, unless I pass
the data 1 record at a time in a loop ...

help on inserting data to DB

Hi,

I m using Microsoft Visual Studio 2005 and SQL server 2000. I have 2 textboxes and a button, what i wanna do is, when i hit the button, the values in textboxes should be inserted into DB. Would you please help me?

Thanks in advance.

There is a tutorials section on this website (www.asp.net). You will get a comprehensive help (code + concept). Once you understand the concepts and make an effort and if you still have issues please post with code and someone here will definetely help you out.

Help on "splitting up" data in a field

Hi
I'm having a problem finding out how I can split data in one field and
then use the values to match records in another table.
In table 1, I have a field where the values looks like
e.g. "229 231 233 235". What I'd like to do, is to match these 4
numbers with an ID in table 2 to get the values from table2. I.e. I'd
like to split up this one value to 4 values (229, 232, 233,235).
I've tried to use REPLACE to put in a "," between each so I could use it
as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
Has any of you any other suggestions to how it can be done? It's not
always the same number of numbers in the field (e.g. another one could
be "456 29580010" ). The field is a VARCHAR(1000) and there're also
some text strings in it. These seems to be some old crab though and I
don't need these values.
The only "general" thing with the formatting, seems to be that there are
2 spaces between each of the numbers I'd like to get out, so I think I
can use that as a "delimiter".
Anyone who has some hints to this?
Regards
SteenSteen
Take a look at Anith's script
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
drop table Numbers
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> Steen
> Take a look at Anith's script
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5,33,229,1,22'
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
> from numbers where substring(','+@.Ids,n,1)=','
> AND n < LEN(@.Ids) + 1
> drop table Numbers
>
>
>
>
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Thanks Uri
I must admit, that I can't really see the purpose of the script, and
also I can't see how it can be used to solve my problem.
I've tried to see if I could get some ideas from the script, but I can't
really see how I can use it?
Regards
Steen|||In SQL 2000 you have to properly normalize the data - i.e. parse the values
and store them in a new table or redesign the table.
In SQL 2005 you can use Anith's function to parse the values on-the-fly
using CROSS APPLY.
ML
http://milambda.blogspot.com/|||I'm really sorry Steen , by posting Anith's example I did mean to give you
an idea to solve the problem
See if this helps you
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE TABLE #Source (col1 INT NOT NULL)
CREATE TABLE #Target (col1 INT NOT NULL)
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 229 1 22'
--Inserting the values to the source table
INSERT INTO #Source
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Source
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 10 1 22'
--Inserting the values to the Target table
INSERT INTO #Target
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Target
-->>> e.g. "229 231 233 235". What I'd like to do, is to match these 4
SELECT * FROM #Source WHERE NOT EXISTS
(SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
> Uri Dimant wrote:
> Thanks Uri
> I must admit, that I can't really see the purpose of the script, and also
> I can't see how it can be used to solve my problem.
> I've tried to see if I could get some ideas from the script, but I can't
> really see how I can use it?
> Regards
> Steen|||I think this might help you out, but there are problems with the aproach...
For one, the string concatenation leaves you open to SQL injection.
Granted, because you are selecting the values from a table, any malicious
injection code needs to actually be stored in your table, but it is still a
possibility. Second, this assumes you are dealing with numeric values, if
you need character values you will have to add in quotes along with the
commas.
declare @.SelectString varchar(1000)
set @.SelectString = TABLE1.FIELD1
set @.SelectString = replace(@.SelectString,' ',',')
set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
@.SelectString + ')'
EXECUTE sp_executesql @.SelectString
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd
> like to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could
> be "456 29580010" ). The field is a VARCHAR(1000) and there're also
> some text strings in it. These seems to be some old crab though and I
> don't need these values.
> The only "general" thing with the formatting, seems to be that there are
> 2 spaces between each of the numbers I'd like to get out, so I think I
> can use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> I'm really sorry Steen , by posting Anith's example I did mean to give you
> an idea to solve the problem
> See if this helps you
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> CREATE TABLE #Source (col1 INT NOT NULL)
> CREATE TABLE #Target (col1 INT NOT NULL)
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 229 1 22'
> --Inserting the values to the source table
> INSERT INTO #Source
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
> SELECT * FROM #Source
>
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 10 1 22'
> --Inserting the values to the Target table
> INSERT INTO #Target
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
>
> SELECT * FROM #Target
>
> -->>> e.g. "229 231 233 235". What I'd like to do, is to match these
4
> SELECT * FROM #Source WHERE NOT EXISTS
> (SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
>
Thanks for you input. I'll have to look further at the example. Right
now I still can't figure out how I can use it, but I'll check it out
tomorrow with a "fresh" pair of eyes..:-).
REgards
Steen|||Jim Underwood wrote:
> I think this might help you out, but there are problems with the aproach..
.
> For one, the string concatenation leaves you open to SQL injection.
> Granted, because you are selecting the values from a table, any malicious
> injection code needs to actually be stored in your table, but it is still
a
> possibility. Second, this assumes you are dealing with numeric values, if
> you need character values you will have to add in quotes along with the
> commas.
> declare @.SelectString varchar(1000)
> set @.SelectString = TABLE1.FIELD1
> set @.SelectString = replace(@.SelectString,' ',',')
> set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
> @.SelectString + ')'
> EXECUTE sp_executesql @.SelectString
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Hi Jim
The script is only for my own use, so I'm not so worried about
injections. It's just for producing some check lists to a few users.
I'll check out your script to see if it works. I'm having both numeric
and text values in the field, so I'll have to remove the text strings first.
Regards
Steen|||There is a function called charindex() which returns the numeric position of
one string within another string. You can join the two tables using
charindex, so that each row in MyTableA is joined with 0 - many rows in
MyTableB where charindex( .. ) > 0.
select
MyTableA.IDS,
MyTableB.ID
from MyTableA
left join MyTableB
on charindex(' '+MyTableB.ID+' ',' '+MyTableA.IDS+' ') > 0
The issue is that this data model is not properly normalized because it is
storing multiple values in one column:
http://www.agiledata.org/essays/dat...html#Normalize
This presents in at least 3 problems:
1. Accuracy: Can you depend on the format of the delimited values
reliable? The purpose of appending additional spaces before and after the
strings is to insure that:
charindex('999','123 ABC999 456') = 0
2. Performance: A non indexed table scan will probably be used due to
using a function for the join expression
http://www.microsoft.com/technet/pr...s/c0618260.mspx
http://www.sql-server-performance.c...ing_indexes.asp
3. Your queries will be more complex to write.
Let's assume that you have a Customer table and a Discount table.What is
needed is a reference table called CustomerDiscount that associates 0 - many
promotions for each customer.
For example:
CustomerID PromotionID
200 10
200 11
212 10
212 13
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||On Thu, 19 Jan 2006 13:34:07 +0100, Steen Persson (DK) wrote:

>Hi
>I'm having a problem finding out how I can split data in one field and
>then use the values to match records in another table.
>In table 1, I have a field where the values looks like
>e.g. "229 231 233 235". What I'd like to do, is to match these 4
>numbers with an ID in table 2 to get the values from table2. I.e. I'd
>like to split up this one value to 4 values (229, 232, 233,235).
>I've tried to use REPLACE to put in a "," between each so I could use it
>as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
>Has any of you any other suggestions to how it can be done? It's not
>always the same number of numbers in the field (e.g. another one could
>be "456 29580010" ). The field is a VARCHAR(1000) and there're also
>some text strings in it. These seems to be some old crab though and I
>don't need these values.
>The only "general" thing with the formatting, seems to be that there are
>2 spaces between each of the numbers I'd like to get out, so I think I
>can use that as a "delimiter".
>Anyone who has some hints to this?
Hi Steen,
In addition to what others already wrote on this, I'll give you this
link:
http://www.sommarskog.se/arrays-in-sql.html
Also, try to change the design. Arrays really should not be stored in a
single column.
Hugo Kornelis, SQL Server MVP

Help needed with Xquery

Hello,

I'm trying to retreive the values from multiple nodes based on the value of another , without any success. The XML source is stored in an SQL(2005) xml column .

'Sample XML

<!--Combat Flight Sim mission-->

<Mission>

<Params Version="3.0" Directive="nothing" Country="Britain" Aircraft="p_51b" Airbase="brod23" Date="8/10/1940" Time="12:00" Weather="scatteredclouds3.xml" Multiplayer="y" MultiplayerOnly="n" />

.......

<AirFormation ID="6003" Directive="nothing" Country="Britain" Skill="1" FormType="diamond">

<Unit ID="9459" Type="p_51b" IsPlayer="y" Skill="1" />

<Unit ID="9460" Type="p_51b" Skill="2" />

.........

<AirFormation ID="6000" Directive="nothing" Country="Britain" Points="2" DamagePercent="40" Skill="2" Payload="2" FormType="box">

<Unit ID="9467" Type="b_25c" Skill="2" Payload="3" />

<Unit ID="9468" Type="b_25c" Skill="2" Payload="3" />

.........

AirFormation ID="6007" Directive="nothing" Country="Germany" Skill="2" FormType="fingertip">

<Unit ID="9475" Type="bf_109g_6" Skill="2" Payload="6" />

<Unit ID="9476" Type="bf_109g_6" Skill="2"

'This is the SQL code:

SELECT DISTINCT nref.value('@.Type', 'varchar(100)') Aircraft

FROM dbo.MOG_Missions CROSS APPLY xmlData.nodes('//AirFormation/Unit') as T(nref)

WHERE id = @.id 'some additional condition here is needed but I cannot figure it out

Which returns the following values from the ?Type attribute :

b_25c
bf_109g_6
p_51b

What I would like to accomplish is to return only the values from ?Type where the AirFormation-Country attribute matches the ?Country attribute of the ?Params node.

Thank you in advance.

Your XML sample is not clear to me. What is the relationship between the Params element and the AirFormation elements? If that is known then you should simply be able to express the condition in an XPath predicate in your nodes call. For example if the Params element is a sibling of the AirFormation elements then you can check e.g.

Code Snippet

SELECT DISTINCT t.u.value('@.Type', 'nvarchar(10)') AS Type

FROM example1

CROSS APPLY xml.nodes('//AirFormation[@.Country = ../Params/@.Country]/Unit') AS t(u)

WHERE id = 3;

|||I should have asked for help sooner! Thank you so much!

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) = POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) => POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>> Hi All,
>> I need to write a trigger to catch all updates made to a table and insert
>> the old and new values into a new table.
>> The problem is that the table being "Audited" has a lot of fields in
>> (more than 120) and a trigger that was written for it takes too long to
>> execute.
>> Splitting the table up into smaller tables not an option right now
>> unfortunatly.
>> Is it possible to write an update trigger that can be fired and is clever
>> enough to only validate updated fields and still be as fast as possible.
>> Thanks
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:
>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
Thanks
Jason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>
|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>
|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help Needed in Calculating results

Hello everyone,
I have a table which has some values and strings, I want to calculate the
values but whenever I use the following in a textbox in a SSRS it gives me a
# Error
=Sum(iif(Fields!XNB.Value >= 8000,1,0))
Which is obvious as there is a string that the function cannot calculate the
string. IS there an alternative to calculating the results when String is
involved.
RegardsGot the answer Use the following and it worked :)
=Sum(iif(Val(Fields!XNB.Value) >= 8000,1,0))
"claton" wrote:
> Hello everyone,
> I have a table which has some values and strings, I want to calculate the
> values but whenever I use the following in a textbox in a SSRS it gives me a
> # Error
> =Sum(iif(Fields!XNB.Value >= 8000,1,0))
> Which is obvious as there is a string that the function cannot calculate the
> string. IS there an alternative to calculating the results when String is
> involved.
> Regards

help needed for a query.

Hi

MailMethodId Is an integer, and it has 2 possible values 1 which means 'Mail to Participant' or 2 which means 'Mail To client'

select

cp.PlanId,

cp.ClientPlanId,

psi.MailMethodId,

psi.StatementTypeId,

cp.PlanName,

cp.ClientId ,

c.ClientName

from ClientPlan cp

Join PlanStatementInfo psi on cp.PlanId = psi.PlanId

Join Client c on cp.ClientId = c.ClientId

Where cp.ClientId = @.ClientId

Union

Select

cp.PlanId,

cp.ClientPlanId,

2,

1,

cp.PlanName,

cp.ClientId,

c.ClientName

From

ClientPlan cp

innerjoin Client c on cp.ClientId = c.ClientId

where

cp.ClientId = @.ClientId

and

cp.PlanId NotIN

(Select psi.PlanId from PlanStatementinfo psi)

So how can i get the Mail methodId to display 'Mail to Participant' if the data is 1 and 'Mail to Client' if the Data is 2

i tried doing this

case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,

instead of psi.MailmethodId, but i am getting a error message that says

Syntax error converting the varchar value 'Mail to Participant' to a column of data type int.

Any Help will be appreciated

Regards

KAren

Karen:

My knee-jerk reaction to this is that you probably have an incompatibility between how the "MailMethodID" column is used in each separate SELECT that comprises the UNION. Try it like this:

Code Snippet

select
cp.PlanId,
cp.ClientPlanId,
case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,
psi.StatementTypeId,
cp.PlanName,
cp.ClientId ,
c.ClientName
from ClientPlan cp
Join PlanStatementInfo psi on cp.PlanId = psi.PlanId
Join Client c on cp.ClientId = c.ClientId
Where cp.ClientId = @.ClientId
Union
Select
cp.PlanId,
cp.ClientPlanId,
'Mail to Client',
1,
cp.PlanName,
cp.ClientId,
c.ClientName
From
ClientPlan cp
inner join Client c on cp.ClientId = c.ClientId
where
cp.ClientId = @.ClientId
and
cp.PlanId Not IN
(Select psi.PlanId from PlanStatementinfo psi)

|||

Thanks a lot Ken, that worked.

help needed

Hi

I've just started learning SQL today. How do i insert values to a table i've created?

thnx

ice

Quote:

Originally Posted by realredice

Hi

I've just started learning SQL today. How do i insert values to a table i've created?

thnx

ice

hi,
just go through all the commands available in SQL server books on line, which is available with all MS SQL server installation, and try 2 learn urself best.. any way the answer is
INSERT INTO tab_name Values('MSSQL', 134)
if u r inserting varchar values enclose data between two single quotes like 'MSSQL"
if it is Numeric values write as it is like 14, 78.9 etc etc

regards,|||hi

thnx 4 the reply. Another q. Is there much difference between MySQL and SQL* Plus?

regards

ice

Help me write sql script

Hi I need to create query which would calculate weekly change of some values. There is table with values for every day. At the end of week I need to calculate % of change. It is something like this:

SELECT ((LastFridayValue - PreviousFridayValue) / PreviousFridayValue) * 100 from myTable.

or it could be something like this:

(LastValue - FirstValue) / FirstValue * 100 from top 5 values from my table order by ID DESC.

Please help me translate this into real sql query :)

Could you provide your table structure with some data example like last two weeks?

Thanks

|||There are two related tables. First table tblValues has structure like this:

ItemID

DayID

Value

1

261

1086,8986

2

262

1110,3700

3

263

1110,3700

4

264

1167,9900

5

265

1121,2900

6

266

1121,2900

7

267

1100,9600

8

268

1100,9600

9

269

1061,1000

10

270

1061,1000

11

271

985,6700

12

272

918,1300

13

273

908,5200

14

274

908,5200

and tblDate has structure like this

DayID

Date

261

28.1.2007

262

29.1.2007

263

30.1.2007

264

31.1.2007

265

1.2.2007

266

2.2.2007

267

3.2.2007

268

4.2.2007

269

5.2.2007

270

6.2.2007

271

7.2.2007

272

8.2.2007

273

9.2.2007

274

10.2.2007

|||

try this I hope it will point you in good direction:

createtable #test1(ItemIDint,

DayID

int,Valuenumeric(12,4))

insert

into #test1

Values

(1, 261, 1086.8986)

insert

into #test1

Values

(2, 262, 1110.3700)

insert

into #test1

Values

(3, 263, 1110.3700)

insert

into #test1

Values

(4, 264, 1167.9900)

insert

into #test1

Values

(5, 265, 1121.2900)

insert

into #test1

Values

(6, 266, 1121.2900)

insert

into #test1

Values

(7, 267, 1100.9600)

insert

into #test1

Values

(8, 268, 1100.9600)

insert

into #test1

Values

(9, 269, 1061.1000)

insert

into #test1

Values

(10, 270, 1061.1000)

insert

into #test1

Values

(11, 271, 985.6700)

insert

into #test1

Values

(12, 272, 918.1300)

insert

into #test1

Values

(13, 273, 908.5200)

insert

into #test1

Values

(14, 274, 908.5200)

create

table #days(DayIDint,

Date

datetime)

insert

into #days

Values

(261,convert(datetime,'28.1.2007',104))

insert

into #days

Values

(262,convert(datetime,'29.1.2007',104))

insert

into #days

Values

(263,convert(datetime,'30.1.2007',104))

insert

into #days

Values

(264,convert(datetime,'31.1.2007',104))

insert

into #days

Values

(265,convert(datetime,'1.2.2007',104))

insert

into #days

Values

(266,convert(datetime,'2.2.2007',104))

insert

into #days

Values

(267,convert(datetime,'3.2.2007',104))

insert

into #days

Values

(268,convert(datetime,'4.2.2007',104))

insert

into #days

Values

(269,convert(datetime,'5.2.2007',104))

insert

into #days

Values

(270,convert(datetime,'6.2.2007',104))

insert

into #days

Values

(271,convert(datetime,'7.2.2007',104))

insert

into #days

Values

(272,convert(datetime,'8.2.2007',104))

insert

into #days

Values

(273,convert(datetime,'9.2.2007',104))

insert

into #days

Values

(274,convert(datetime,'10.2.2007',104))

select

day1 [WeekDay],(cc.value-dd.value) [difference],*from(select day1,max(dayID) cur,MIN(dayID) prevfrom(selectdatepart(weekday,date) day1, dayIDfrom #days

where

date>dateadd(day,-13,getdate()))aa

group

by day1)aa

left

join #test1 ccON cc.dayid=aa.cur

left

join #test1 ddON dd.dayid=aa.prev

select

*from #test1

drop

table #days

drop

table #test1

help me out for a simple query

I am sending a small scenario
i have a table
CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
INSERT INTO VALUES (1, 20)
INSERT INTO VALUES (1, 10)
INSERT INTO VALUES (1, 30)
INSERT INTO VALUES (1, 50)
INSERT INTO VALUES (2, 50)
INSERT INTO VALUES (2, 70)
INSERT INTO VALUES (2, 20)
INSERT INTO VALUES (2, 40)
INSERT INTO VALUES (2, 90)
i need the output like
examid marksstring
----
1 20,10,30,50
2 50,70,20,40,90
----
thx for ur help
*** Sent via Developersdex http://www.examnotes.net ***Here's one way to achieve this using a function:
CREATE FUNCTION dbo.fn_ConcatMarks(@.id INT) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.marks VARCHAR(8000)
SET @.marks = ''
SELECT @.marks = @.marks + CAST(MARKS AS VARCHAR(10)) + ',' FROM
STUDENT_ANSWERS
WHERE EXAMID = @.id
RETURN LEFT(@.marks, LEN(@.marks) - 1)
END
GO
SELECT EXAMID, dbo.fn_ConcatMarks(EXAMID) AS marks
FROM STUDENT_ANSWERS
GROUP BY EXAMID
EXAMID marks
-- --
1 20,10,30,50
2 50,70,20,40,90
You can find several other solutions in previous threads if you look for the
keywords PIVOT, crosstab.
BG, SQL Server MVP
www.SolidQualityLearning.com
"kamal hussain" <skkamalh@.rediffmail.com> wrote in message
news:OzYlXrmRFHA.1208@.TK2MSFTNGP10.phx.gbl...
>
> I am sending a small scenario
> i have a table
> CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
> INSERT INTO VALUES (1, 20)
> INSERT INTO VALUES (1, 10)
> INSERT INTO VALUES (1, 30)
> INSERT INTO VALUES (1, 50)
> INSERT INTO VALUES (2, 50)
> INSERT INTO VALUES (2, 70)
> INSERT INTO VALUES (2, 20)
> INSERT INTO VALUES (2, 40)
> INSERT INTO VALUES (2, 90)
>
>
> i need the output like
> examid marksstring
> ----
> 1 20,10,30,50
> 2 50,70,20,40,90
> ----
>
> thx for ur help
>
>
> *** Sent via Developersdex http://www.examnotes.net ***