SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Optimizing the query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rs70
Starting Member

2 Posts

Posted - 08/04/2011 :  03:06:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/04/2011 :  07:33:32  Show Profile  Reply with Quote
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?
Go to Top of Page

rs70
Starting Member

2 Posts

Posted - 08/04/2011 :  09:01:52  Show Profile  Reply with Quote
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

Edited by - rs70 on 08/04/2011 09:07:03
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/04/2011 :  09:04:00  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000