How to aggregate similar sets in SQL
How to create a set of similar data and link a backward related key.
The example below shows how the raw data is and the desired output.
DECLARE @Data TABLE (PID INT, CID INT)
INSERT INTO @Data
VALUES (1, 1), (1, 2), (1, 3), (3, 1), (3, 2), (3, 3), (2, 1), (2, 2), (4, 3), (4, 4),
(5, 4), (5, 5), (8, 4), (8, 5), (6, 6), (7, 4), (7, 5), (7, 6)
In the above data, CID sets 1, 2, 3 differ from 1, 2. These are unique sets.
I want to tie
PID 1 and 3 to set 1, 2, 3.
PID 2 for setting 1, 2
PID 4 for setting 3, 4
PID 5 and 8 for setting 4, 5
and etc.
The overall goal is to return XML that looks like this:
<Items>
<Item>
<Hierarchies>
<Hierarchy>
<CID>1</CID>
<CID>2</CID>
<CID>3</CID>
</Hierarchy>
</Hierarchies>
<PIDs>
<PID>1</PID>
<PID>3</PID>
</PIDs>
</Item>
<Item>
<Hierarchies>
<Hierarchy>
<CID>1</CID>
<CID>2</CID>
</Hierarchy>
</Hierarchies>
<PIDs>
<PID>2</PID>
</PIDs>
</Item>
<Item>
<Hierarchies>
<Hierarchy>
<CID>3</CID>
<CID>4</CID>
</Hierarchy>
</Hierarchies>
<PIDs>
<PID>4</PID>
</PIDs>
</Item>
<Item>
... Etc ...
</Item>
<Items>
+3
source to share
1 answer
something like that? or did I get it wrong?
DECLARE @Data TABLE (PID INT, CID INT)
INSERT INTO @Data
VALUES (1, 1), (1, 2), (1, 3), (3, 1), (3, 2), (3, 3), (2, 1), (2, 2), (4, 3), (4, 4),
(5, 4), (5, 5), (8, 4), (8, 5), (6, 6), (7, 4), (7, 5), (7, 6)
with cid_xml as ( select P.PID
,OA.CID
from @Data P
outer apply (select CAST((select C.CID
from @Data C
where C.PID = P.PID
for xml path('')) as varchar(max)) as CID
) OA
)
select cast((select cast(C2.CID as xml) as 'hierarchies/hierarchy'
,cast(OA2.PID as xml) as 'PIDs'
from cid_xml C2
outer apply (select CAST((select C3.PID
from cid_xml C3
where C3.CID = C2.CID
group by C3.PID
for xml path('')) as varchar(max)) as PID
)OA2
group by C2.CID,OA2.PID
order by LEN(C2.CID) desc
for xml path('item')) as xml)
for xml path('Items')
I just followed the order to see the first nodes of the element / hierarchy / hierarchy as it was in your example target result
the result set I got was
<Items>
<item>
<hierarchies>
<hierarchy>
<CID>1</CID>
<CID>2</CID>
<CID>3</CID>
</hierarchy>
</hierarchies>
<PIDs>
<PID>1</PID>
<PID>3</PID>
</PIDs>
</item>
<item>
<hierarchies>
<hierarchy>
<CID>4</CID>
<CID>5</CID>
<CID>6</CID>
</hierarchy>
</hierarchies>
<PIDs>
<PID>7</PID>
</PIDs>
</item>
<item>
<hierarchies>
<hierarchy>
<CID>3</CID>
<CID>4</CID>
</hierarchy>
</hierarchies>
<PIDs>
<PID>4</PID>
</PIDs>
</item>
<item>
<hierarchies>
<hierarchy>
<CID>4</CID>
<CID>5</CID>
</hierarchy>
</hierarchies>
<PIDs>
<PID>5</PID>
<PID>8</PID>
</PIDs>
</item>
<item>
<hierarchies>
<hierarchy>
<CID>1</CID>
<CID>2</CID>
</hierarchy>
</hierarchies>
<PIDs>
<PID>2</PID>
</PIDs>
</item>
<item>
<hierarchies>
<hierarchy>
<CID>6</CID>
</hierarchy>
</hierarchies>
<PIDs>
<PID>6</PID>
</PIDs>
</item>
</Items>
this is normal?
+4
source to share