Query XML type with values from another XML type

Hi - I'm using SQL Server 2005, and I have a view with an XML data type column. I would like to write a stored proc that takes an XML data type as a parameter and return the rows from the view that match the columns in the parameter.

At this point, I'm just trying to get the syntax correct, so I started with some simple queries. Below are the queries:

declare @.params xml

declare @.view xml

set @.params = '<HierarchyTypes>

<HierarchyType hierarchyTypeId="4" />

<HierarchyType hierarchyTypeId="5" />

</HierarchyTypes>'

set @.view = '<HierarchyTypes>

<HierarchyType hierarchyTypeId="4" otherProp = "1"/>

<HierarchyType hierarchyTypeId="5" otherProp = "2"/>

<HierarchyType hierarchyTypeId="6" otherProp = "3"/>

<HierarchyType hierarchyTypeId="7" otherProp = "4"/>

</HierarchyTypes>'

SELECT T.c.query('.') AS result

FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c)

I would like to get the nodes from @.view where @.view.hierarchyTypeId = @.params.hierarchyTypeId. This should be pretty simple, but I'm missing it...

Any thoughts are appreciated!

Thanks,

Phil

It sounds like you are trying to join view and params to get a results set which has one matching node per row. If that is the case, the below query should be doing what you are looking for. If you want to return a single xml fragment, a different technique would have to be used.

In the query below, we iterate over the HierarchyType nodes of both @.view and @.params, project the values of the hierarchyTypeId attributes as integers using the the value() function, verify that they match, and the return the @.param node that matches.

declare @.params xml
declare @.view xml

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
</HierarchyTypes>'

set @.view = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>'

SELECT T.c.query('.') AS result
FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c),

@.params.nodes('/HierarchyTypes/HierarchyType') P(c)
WHERE T.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

|||

Hi Todd - this is very close to what I need. The only other wrinkle is that I'm trying to select all of the columns from the view (not just the hierarchyTypeId). The view has an XML column called HierarchyTypes, and I want to use that column in my 'join criteria'. And I'd like to do this in a stored proc.

CREATE PROCEDURE [dbo].[CodeHierarchy_SearchHierarchy]
@.codeTypes xml = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT v.nodeId,
v.parentNodeId,
v.HierarchyTypes
FROM VLinkedCodeHierarchies as v
WHERE /* hierarchyTypes in @.codeTypes are also in v.HierarchyTypes */

At this point, I also require that the return is NOT XML.

Thanks,

Phil

|||

So it sounds like you have a table or a view that has a few relational columns, and 1 xml column. And then you want to join it with an XML fragment.

Is that correct?

If so, then you can modify the query to use CROSS APPLY. In the example I create a test table which has an xml column, fill it with data and then join it with the fragment, projecting the relevant relational columns and xml data. You could then wrap it in a stored procedure or user defined function as needed.

(I CROSS APPLY the vTest table with nodes() function so that I get the nodes for the current row.)

declare @.params xml

drop table vTest
Create table vTest(
id int,
val xml
)

insert into vTest (id, val) values (1,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>')

insert into vTest (id, val) values (2,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="8" otherProp = "1"/>
<HierarchyType hierarchyTypeId="9" otherProp = "2"/>
<HierarchyType hierarchyTypeId="10" otherProp = "3"/>
<HierarchyType hierarchyTypeId="11" otherProp = "4"/>
</HierarchyTypes>')

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
<HierarchyType hierarchyTypeId="11" />
</HierarchyTypes>'

SELECT vTable.Id,
xVal.c.value('@.hierarchyTypeId', 'int') as hierarchyTypeId,
xVal.c.value('@.otherProp', 'int') as otherProp,
xVal.c.query('.') as MatchingFragment

FROM @.params.nodes('/HierarchyTypes/HierarchyType') P(c),
vTest as vTable CROSS APPLY vTable.Val.nodes('/HierarchyTypes/HierarchyType') xVal(c)
WHERE xVal.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

If you dont want to Join the parameter fragment and the table, but just want to check that for each row it's xml column has some data in common with the parameter fragment, then you could change the query to use an EXISTS.

|||

Hi Todd - thanks again for the excellent reply. I actually want to make sure that the rows returned have data in common with the XML fragment.

Ideally, I would like to have 3 options:

1) View column has some data from XML fragement

2) View column has all data from XML fragment, but could have more.

3) View column has ONLY data from XML fragement.

I can accomplish #1 above using a SELECT DISTINCT, but I don't think that's the most optimum.

Where are some good resources to educate myself on this?

Thanks!

Phil

|||

Here is where you can find the the basics of XQuery and the T-SQL functions that support it:
http://msdn2.microsoft.com/en-us/library/ms190262.aspx

The current w3c resources:
http://www.w3.org/TR/xquery/

T-SQL Reference:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx

And there are probably a number of tutorials that would discuss the differences of when to use a DISTINCT vs EXISTS vs CROSS APPLY.

query with user-define function

I have a table with 3 fields.
In the first field named a there are values i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+3*b (where a,b
supposed to be the contents of the previous fields).
How can I issue a query to get back 5, 9, 32 (5+3*9)'
Many Thanks
HelenNot sure why you want to do this in SQL. Note that if dbo.foo has more than
one row, you will need to limit both queries using a WHERE clause to
identify that single row (unless the expression in c is always the same, in
which case, it shouldn't be in the table at all).
CREATE TABLE dbo.foo
(
a INT,
b INT,
c VARCHAR(32)
)
GO
SET NOCOUNT ON
GO
INSERT dbo.foo SELECT 5,9,'a+3*b'
GO
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b,'+c+' FROM dbo.foo'
FROM dbo.foo
EXEC(@.sql)
GO
DROP TABLE dbo.foo
GO
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen|||Hi,
You can have 3 solutions
1. Direct TSQL . Select a,b,(a+3*b) as c from table_name
2. Create a view. Create view v1 as Select a,b,(a+3*b) as c from table_name
and later use
select * v1
3. Use compute columns while table creation
create table cc(a int, b int, c AS (a + 3 * b))
WHILE INSERTION INSERT DATA ONLY FORM COLUMN a AND b
Thanks
Hari
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen

Query with user-define function

I have a table with 3 fields. In the first field named a there are values
i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+@.q+3*b where a,b
supposed to be the contents of the previous fields, different in each row
and @.q is a variable I wound like to type each time I run the query.
I have typed:
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
FROM dbo.foo where Index=1
EXEC(@.sql)
How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
Many Thanks
HelenDECLARE @.q INT
SET @.q = 7
SELECT a,b, a + @.q + 3 *b AS c
FROM dbo.foo
where Index=1
Jacco Schalkwijk
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||I'm sorry. I didn't explain myself correctly. I mean I have this table in a
SQL Server with many rows and different function in each row. Inside the
function I would like to have a variable (@.q) which I don't know how to writ
e
so as when I query I can put a different value each time.

> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>
>|||Hi
Maybe
CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
varchar(10) )
INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
where [Index]=1'
FROM dbo.foo where [Index]=1
EXEC(@.sql)
John
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||Helen,
The T-SQL infix expression evaluator here might help:
http://users.drew.edu/skass/SQL/Infix.sql.txt
If you first replace the 'a', 'b', and @.q in your expression
with their values, InFixVal should then evaluate the result.
select
a, b,
dbo. InFixVal(replace(replace(replace(c,'a','
('+str(a,19,4)+')'),'b','('+str(
b,19,4)+')'),'@.q,str(@.q,19,4)),1)
from ...
Also look here, for some examples of its use, and comments
about its limitations. It only evaluates a simple set of possible
arithmetic expressions, but it may be enough for you.
http://groups.google.com/groups?hl=...ver&qt_s=Search
Steve Kass
Drew University
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>sql

Query with CASE and NULL values

Hi, please, take a look to this query:

declare @.IDCliente int
declare @.Cliente varchar(50)
declare @.IDUsuario int
declare @.IDUsuarioAlta int

set @.IDcliente = 0
set @.Cliente = ''
set @.IDUsuario = 0
set @.IDUsuarioAlta = 0

select * from cliente
where
(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)
AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)
AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END
AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

Cliente
IDCliente Cliente IDUsusario IDUsuarioAlta
1 Esteban 1 2
2 Jose 3 1
3 Mario 2 NULL
4 Pedro NULL 2
5 NULL 1 2

Its work fine, except for the NULL values. What can I do to fix it ?

thanks

Here you go:

Code Snippet

select IDCliente, Cliente,

case when IDUsusario is null then '' --or whatever you want in place of null

else IDUsusario

end as IDUsusario,

case when IDUsuarioAlta is null then '' -- same thing here

else IDUsuarioAlta

end as IDUsuarioAlta

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)

AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

I'm sorry, I think I didn't explain my self.

The problem is in the WHERE part, No in the SELECT.

When @.IDUsuario has a value, then the query return the record with the NULL value, and that is not correct. If I take off or idusuario is null

then the record with de NULL value is never return.

thanks and sorry my english !.

|||

AH, gotcha.

How about this then:

Code Snippet

select *

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario

ELSE @.IDUsuario

END

or (idusuario is null and @.IDUsuario = 0) )

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta

ELSE @.IDUsuarioAlta

END

or (idusuarioalta is null and @.IDUsuarioAlta = 0) )

AND (idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

or (idCliente is null and @.idCliente = 0) )

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

This might be a little cleaner:

Code Snippet

select *

from cliente

where

((@.IDUsuario <> 0 and idusuario = @.IDUsuario ) or

(@.IDUsuario = 0))

AND ((@.IDUsuarioAlta <> 0 and IDUsuarioAlta = @.IDUsuarioAlta ) or

(@.IDUsuarioAlta = 0))

AND ((@.idCliente <> 0 and idCliente = @.idCliente ) or

(@.idCliente = 0))

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||*** !, it work perfect, i think you know this !! like we say in Argentina: "Sos Groso !!"..... is like say You are Big !... I think so..sql

Query with another query input parameter

Dear Friends,

I have a long query with an input parameter. I want this input parameter be all teh values returned from another query.

SELECT DIR FROM DIRECCAO

BIG QUERY with DIR input parameter.

How can I do?

Thanks.

SELECT @.DIR = DIR FROM DIRECCAO

EXEC BIG_QUERY @.DIR

HTH,

Babu

|||

IT WORKS AND THE QUERY IS:

ALTER PROCEDURE [dbo].[GD_SP_FACTURA_GLOBAL]

AS

DECLARE @.DIR nvarchar(10)

SELECT @.DIR = DIR_NOME FROM Direccao

EXECUTE dbo.GD_SP_FACTURA_ValorTotal @.DIR

BUT How can I SUM all the values returned by the BIGQuery?

THANKS!!

|||How can I have the sum and it's possible to return a list of all values returned by the bigQuery? THANKS!!!|||

Could anyone help me?

Thanks!

Query using mathematical function of values from 2 tables has a performance prob

When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.

Why is this happening?
Is there a way to bypass this problem?

Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test

Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test

Long query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )

Short query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )Just a quick comment:
I don't actually see a(ny) join(s) - instead I see you using WHERE clauses; which is not advised!
The execution plan is assuming INNER JOINS which might not be what you want either.

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
=====
id name
1 Arun
2 Hari
Table2
=====
id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?
Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegro ups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====
> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====
> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>
|||Thank you very much for sending me such a useful answer
|||If you dont mind can you please explain the last 2 queries

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
===== id name
1 Arun
2 Hari
Table2
===== id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queries

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
=====
id name
1 Arun
2 Hari
Table2
=====
id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====
> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====
> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queriessql

query to parse out values from one column into different columns

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr

What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.Look up CROSSTAB queries in Books Online.select userid,
max(case recordtag when 1 then record end) as firstname,
max(case recordtag when 2 then record end) as middleinitial,
max(case recordtag when 3 then record end) as suffix
from [YourTable]
group by userid|||Thanks for the info. I'll let you know how I do.|||I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!

Query to get values from datetime column into comma separated text

Hi All

I am working on a query to get all the datetime values in a column in a table into a comma separated text.

eg.

ColumnDate
--------
2005-11-09 00:00:00.000
2005-11-13 00:00:00.000
2005-11-14 00:00:00.000
2005-11-16 00:00:00.000

I wanted to get something like

2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16

Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks

DECLARE @.List varchar(8000)
SET @.List = ''
select @.List = @.List + convert(varchar, datefield, 102) + ',' from MyTable

Just be careful, because a varchar can hold only 8000 characters, so if your results are more then they are chopped off. For more info on CONVERT function look in the BOL.

Query to get rows which match with all given values

Hi all,

I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:

SELECT *
FROM TblUser u
WHERE EXISTS

(

SELECT *

FROM TblScore s

WHERE s.FKIDUser = PKIDUser

)

With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15

I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.

So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.

Someboy would have an idea at my problem ?

Thanks in advance
Jerome

Is is possible that a user may have scores that repeat? (for exmaple, 2, 2, 4, 6, 6) ?

|||

Something like this might work in 2005, but not in 2000

Code Snippet

select * from tblUser U

where PKIDUser IN

(

(

select FKIDUser from tblscore where ScoreValue = 2

intersect

select FKIDUser from tblscore where ScoreValue = 4

intersect

select FKIDUser from tblscore where ScoreValue = 6

)

except

select FKIDUser from tblScore where ScoreValue not in (2,4,6)

)

|||No, it's not possible.

And to complicate the problem, this query will be created in a stored procedure.
The specific score to search will be passed in argument to the sp.
For that, no problem, I can do it.
And I will insert these score into a temporary table (data type).

I tried to use the " = all " but I'm not sure it's the right solution.
|||

In that case, you can COUNT the records....if the total count = 3 and you've used IN (2,4,6) and these numbers cannot repeat...well, then you have your list:

Code Snippet

select * from tblUser

where PKIDUser in (

select FKIDUser

from TBLSCore

where ScoreValue in (2,4,6)

group by FKIDUser

having count(*) = 3

)

|||

Hi,

May be you can try something like this:

SELECT

U.*

FROM tblUsers As U

JOIN (

SELECT FKIDUser FROM tblScores WHERE Score = 2

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 4

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 6

) AS T

ON U.UserID = T.FKIDUser

Thanks & Regards,

Kiran.Y

|||

This operation is known as "relational division".

declare @.t table(scorevalue int not null unique)

insert into @.t values(2)

insert into @.t values(4)

insert into @.t values(6)

select

u.userid

from

tblusers as u

inner join

tblscore as s

on s.fkuserid = u.pkuserid

inner join

@.t t

s.scorevalue = t.scorevalue

group by

u.userid

having

count(distinct s.score) = (select count(*) from @.t)

go

I am editing my post, because I realized, while running, that you want to kick it up another notch. So, if we add the following expression to the "having"clause, then we could get the expected result.

and (select coun(distinct s2.scorevalue) from tblscore as s2 where s2.fkuserid = u.pkuserid) = (select count(*) from @.t)

Also, we can give it a try to the use of "for xml" black box, to calculate concatenate aggregation (I think that my English here is far from good).

;with agg

as

(

select

userid,

stuff(

(

select ',' + ltrim(s.scorevalue)

from tblscore as s

where s.fkuserid = u.pkuserid

order by s.scorevalue

for xml path('')

), 1, 1, '') as conc_agg

from

tbluser as u

)

select

*

from

agg

where

conc_agg = '2,4,6';

AMB

|||Hi everybody,

Thank you for your replies and sorry for my late answer but I was on vacation ^^

Finally, I used this solution:

DECLARE @.ScoreWanted TABLE (Score INT) -- 'ScoreWanted' score list

INSERT INTO @.ScoreWanted (Score) SELECT 3
INSERT INTO @.ScoreWanted (Score) SELECT 4
INSERT INTO @.ScoreWanted (Score) SELECT 6

SELECT *
FROM (
SELECT *
FROM users p
WHERE p.zone = @.p_Zone
AND p.region = @.p_Region
AND p.zipcode = @.p_ZipCode
AND p.valid = 1
AND p.called > 0
AND NOT EXISTS
(
SELECT 'x'
FROM score s
WHERE s.fk_user = p.id
AND date > @.p_PivotDate
) -- We don't keep users having a score after the pivot date
) a
WHERE a.id IN
(
SELECT s.fk_user
FROM score s
WHERE date <= @.p_PivotDate
AND s.score IN (SELECT * FROM @.ScoreWanted)
GROUP BY s.fk_user
HAVING COUNT(*) = (SELECT COUNT(*) FROM @.scorewanted)
) -- We keep only prospects having exactly the same scores that the scores coming from the

I perform a subquery for second part of my query for performance reason.
Like that I decrease the number of rows for which I need to do the second join.

Compared with my first explanation, there is here an other constraint: the pivot date for the score.
I want only users having all specified score before the pivot date and I don't want users with scores before the pivot date.
It's for that I have two separtes "where" clauses.

But I think I need to add the "distinct" word like Hunchback said.

For the xml code, Like I use Sql 2000, I think it's not supported ?

If you have anothers remarks, I'm listening you.

Thanks you.
Jerome

Query to get rows which match with all given values

Hi all,

I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:

SELECT *
FROM TblUser u
WHERE EXISTS

(

SELECT *

FROM TblScore s

WHERE s.FKIDUser = PKIDUser

)

With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15

I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.

So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.

Someboy would have an idea at my problem ?

Thanks in advance
Jerome

Is is possible that a user may have scores that repeat? (for exmaple, 2, 2, 4, 6, 6) ?

|||

Something like this might work in 2005, but not in 2000

Code Snippet

select * from tblUser U

where PKIDUser IN

(

(

select FKIDUser from tblscore where ScoreValue = 2

intersect

select FKIDUser from tblscore where ScoreValue = 4

intersect

select FKIDUser from tblscore where ScoreValue = 6

)

except

select FKIDUser from tblScore where ScoreValue not in (2,4,6)

)

|||No, it's not possible.

And to complicate the problem, this query will be created in a stored procedure.
The specific score to search will be passed in argument to the sp.
For that, no problem, I can do it.
And I will insert these score into a temporary table (data type).

I tried to use the " = all " but I'm not sure it's the right solution.
|||

In that case, you can COUNT the records....if the total count = 3 and you've used IN (2,4,6) and these numbers cannot repeat...well, then you have your list:

Code Snippet

select * from tblUser

where PKIDUser in (

select FKIDUser

from TBLSCore

where ScoreValue in (2,4,6)

group by FKIDUser

having count(*) = 3

)

|||

Hi,

May be you can try something like this:

SELECT

U.*

FROM tblUsers As U

JOIN (

SELECT FKIDUser FROM tblScores WHERE Score = 2

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 4

UNION

SELECT FKIDUser FROM tblScores WHERE Score = 6

) AS T

ON U.UserID = T.FKIDUser

Thanks & Regards,

Kiran.Y

|||

This operation is known as "relational division".

declare @.t table(scorevalue int not null unique)

insert into @.t values(2)

insert into @.t values(4)

insert into @.t values(6)

select

u.userid

from

tblusers as u

inner join

tblscore as s

on s.fkuserid = u.pkuserid

inner join

@.t t

s.scorevalue = t.scorevalue

group by

u.userid

having

count(distinct s.score) = (select count(*) from @.t)

go

I am editing my post, because I realized, while running, that you want to kick it up another notch. So, if we add the following expression to the "having"clause, then we could get the expected result.

and (select coun(distinct s2.scorevalue) from tblscore as s2 where s2.fkuserid = u.pkuserid) = (select count(*) from @.t)

Also, we can give it a try to the use of "for xml" black box, to calculate concatenate aggregation (I think that my English here is far from good).

;with agg

as

(

select

userid,

stuff(

(

select ',' + ltrim(s.scorevalue)

from tblscore as s

where s.fkuserid = u.pkuserid

order by s.scorevalue

for xml path('')

), 1, 1, '') as conc_agg

from

tbluser as u

)

select

*

from

agg

where

conc_agg = '2,4,6';

AMB

|||Hi everybody,

Thank you for your replies and sorry for my late answer but I was on vacation ^^

Finally, I used this solution:

DECLARE @.ScoreWanted TABLE (Score INT) -- 'ScoreWanted' score list

INSERT INTO @.ScoreWanted (Score) SELECT 3
INSERT INTO @.ScoreWanted (Score) SELECT 4
INSERT INTO @.ScoreWanted (Score) SELECT 6

SELECT *
FROM (
SELECT *
FROM users p
WHERE p.zone = @.p_Zone
AND p.region = @.p_Region
AND p.zipcode = @.p_ZipCode
AND p.valid = 1
AND p.called > 0
AND NOT EXISTS
(
SELECT 'x'
FROM score s
WHERE s.fk_user = p.id
AND date > @.p_PivotDate
) -- We don't keep users having a score after the pivot date
) a
WHERE a.id IN
(
SELECT s.fk_user
FROM score s
WHERE date <= @.p_PivotDate
AND s.score IN (SELECT * FROM @.ScoreWanted)
GROUP BY s.fk_user
HAVING COUNT(*) = (SELECT COUNT(*) FROM @.scorewanted)
) -- We keep only prospects having exactly the same scores that the scores coming from the

I perform a subquery for second part of my query for performance reason.
Like that I decrease the number of rows for which I need to do the second join.

Compared with my first explanation, there is here an other constraint: the pivot date for the score.
I want only users having all specified score before the pivot date and I don't want users with scores before the pivot date.
It's for that I have two separtes "where" clauses.

But I think I need to add the "distinct" word like Hunchback said.

For the xml code, Like I use Sql 2000, I think it's not supported ?

If you have anothers remarks, I'm listening you.

Thanks you.
Jerome

Query to find default value for a column

Where are the default values for a column stored in SQL Server. I thought
they would be in the syscolumns table, but I cannot find them there, nor
anywhere else for that matter.
Thanks,
Jasonget the cdefault from syscolumns and
select from syscomments for the id
syscomments.id = syscolumns.cdefault.
Let me know if this helps
"JasonDWilson" wrote:

> Where are the default values for a column stored in SQL Server. I thought
> they would be in the syscolumns table, but I cannot find them there, nor
> anywhere else for that matter.
> Thanks,
> --
> Jason|||Try this:
select distinct substring(object_name(c.id), 1, 50) 'Table Name'
, substring(c.name, 1, 40) 'Column Name'
, object_name(c.cdefault)'Default Name'
from syscolumns c,
syscomments m
where m.id = c.cdefault
Perayu
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:E45274CE-E559-40E0-805E-7DD5037D90BD@.microsoft.com...
> Where are the default values for a column stored in SQL Server. I thought
> they would be in the syscolumns table, but I cannot find them there, nor
> anywhere else for that matter.
> Thanks,
> --
> Jason

query to find a sum of char values present in different rows

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

Quote:

Originally Posted by Anu139

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

I got the answer

declare @.name varchar(50)

select @.name = coalesce( @.name,'' )+ col1 from tab_name

select @.name 'Name'

Query to ADD/SELECT values from an SQL

Hello.

I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table.

Either way, I need the row returned at the end of the query.

I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements.

For example:

User runs a query for "Canada".
Canada exists in the database, so the database returns Canada along with its ID.

Next user runs a query for "Chile".
Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned.

Does anyone know how I may accomplish this?something like this:

create proc MyProc
(
 @.countryid int out,
 @.countryname nvarchar(50)
)
set @.countryid = -1
select @.countryid = CountryId
from MyTable
where CountryName = @.countryname
if (@.countryid = -1)
begin
 insert into MyTable (CountryName)
 values (@.countryname)
 select @.countryid = scope_identity()
end

|||or

IF NOT EXISTS ( Select countryId from from MyTable where CountryName = @.countryname)
 INSERT INTO MyTable (CountryName) values (@.countryname)

hth

Query Syntax - Right

I have a column that has dollar amounts with the dollar sign present (i.e. $500), due to bulk insert.
I need to convert the values in that column to Float, but I get error converting varchar to float, due to the $.
I can't do something like right(amount, 3), because the amounts aren't consistent ($500, $5000, $500000) . . .
Is there a way to select right minus one? or left 2 and on?
Help appreciated.
Thanks!
Ysandre,
You need to convert it to MONEY first, then to FLOAT:
SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
That said, why are you using FLOAT? I recommend you use DECIMAL instead;
FLOAT is an inexact type and you could end up with rounding errors. That is
not the case with DECIMAL.
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> I have a column that has dollar amounts with the dollar sign present (i.e.
$500), due to bulk insert.
> I need to convert the values in that column to Float, but I get error
converting varchar to float, due to the $.
> I can't do something like right(amount, 3), because the amounts aren't
consistent ($500, $5000, $500000) . . .
> Is there a way to select right minus one? or left 2 and on?
> Help appreciated.
> Thanks!
>
|||Thank you adam! that worked!
Our developer is the one who made everything float, I don't know why I just work with it
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
"Adam Machanic" wrote:

> Ysandre,
> You need to convert it to MONEY first, then to FLOAT:
> SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
> That said, why are you using FLOAT? I recommend you use DECIMAL instead;
> FLOAT is an inexact type and you could end up with rounding errors. That is
> not the case with DECIMAL.
>
> "Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
> news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> $500), due to bulk insert.
> converting varchar to float, due to the $.
> consistent ($500, $5000, $500000) . . .
>
>