Optimizing the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Optimizing the query

Author

Topic

rs70
Starting Member

2 Posts

Posted - 2011-08-04 : 03:06:52

Hi,
I am new to sql.
I have two set of values I want to combination of all values.
How to achieve this using sql.

I just tried using CTE.

Code:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =
'<ExecuteQuery>
<Entities>
<Entity Name="Mono_00122" Id="4"/>
<Entity Name="Mono_00122" Id="5"/>
<Entity Name="Mono_00122" Id="6"/>
<Entity Name="Mono_00122" Id="7"/>
</Entities>
<Fields>
<EntityType Value="2" Name="Model">
<Field ColIndex="1" Id="54" Name="Power" />
<Field ColIndex="2" Id="55" Name="Volt"/>
</EntityType>
<EntityType Value="5" Name="Result" >
<Field ColIndex="3" Id="157" Name="Ob head" />
<Field ColIndex="4" Id="158" Name="Ob dis" />
</EntityType>
<EntityType Value="1" Name="Result" >
<Field ColIndex="3" Id="157" Name="Ob head" />
<Field ColIndex="4" Id="158" Name="Ob dis" />
</EntityType>
</Fields>
</ExecuteQuery>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.

declare @maxent int;

create table #EntityTable
(Row int not null
,Id int not null
);

create table #TypeTable
(Type int not null
);

INSERT #EntityTable
Select ROW_NUMBER() OVER (ORDER BY ID ASC) Row, Id as Ind FROM OPENXML (@idoc, '/ExecuteQuery/Entities/Entity',1)
WITH (ID int '@Id')

INSERT #TypeTable
Select Type From OPENXML (@idoc, '/ExecuteQuery/Fields/EntityType',1)
With(Type int '@Value')

select * from #EntityTable;

Select * from #TypeTable

Select @maxent = COUNT(*) From #EntityTable;
;WITH
cte (ID, Row, type)
AS
(
SELECT (select Id from #EntityTable where Row=1),1 row, Type FROM #TypeTable

UNION ALL
SELECT (select Id from #EntityTable where Row = cte.Row+1),cte.Row+1, Type From Cte where cte.row < @maxent
)
SELECT * from Cte

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-04 : 07:33:32

quote:
Originally posted by rs70

Hi,
I am new to sql.
I have two set of values I want to combination of all values.
How to achieve this using sql.

I just tried using CTE.

.....

Your code looks OK to me. Is that not the output you want to get?

rs70
Starting Member

2 Posts

Posted - 2011-08-04 : 09:01:52

Thank you for the assistance.
No. I am getting the output I want. Is there any better method?

Input:
Table1:
ColA
====
1
2

Table2
======
ColB:
====
4
5
6

Result I want is
Table3
======
ColA ColB
==== ====
1 4
1 5
1 6
2 4
2 5
2 6

Thanking you.
rs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 09:04:00

quote:
Originally posted by rs70

No. I am getting the output I want. My question is better method.
Table1: Table2
Id
==

if you're using sql 2005 or above you can use the new XML functions node(),value() etc rather than OPENXML for shredding and getting xml data outside.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources