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







All Articles