| Author |
 Topic  |
|
|
rs70
Starting Member
2 Posts |
 Posted - 08/04/2011 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/04/2011 : 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 - 08/04/2011 : 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 |
Edited by - rs70 on 08/04/2011 09:07:03 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/04/2011 : 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/
|
|
|
| |
Topic |
|
Optimizing the query
