Rolling up records by referencing values from one column to another

Question

I have a table where I have two columns- col1 & col2. The table need to be rolled up such that if a value is in col1 then the adjacent value to col2 gets inserted next to original col1 value. To illustrate, if I have a table like this:

col1    col2
24      670
25      980
26      24
28      1570
28      26
27      5745
27      4654

Then the output should look like this (in no particular order placement):

col1    col_1   col_2   col_3   col_4
24      670     26      28    1570
25      980     NULL    NULL    NULL
27      4654    5745    NULL    NULL

Here 24 from col2 exists in col1 already, so 26 (and other associations 28 and 1570) gets inserted next to 24. Unfortunately, with my current knowledge in sql I was able to get this:


--refrences
-- https://stackoverflow.com/questions/38233002/how-to-create-add-columns-using-a-variable-in-a-loop
-- https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query


declare @max_columns int
declare @counter int
declare @col_header nvarchar(max)= ''
declare @query nvarchar(max)= ''

drop table if exists  #tmp_ini
drop table if exists  #tmp
drop table if exists  #tmp_allcols

create table #tmp_ini (col1  int, col2 int )

create table #tmp_allcols (col1  int, col nvarchar(max), [val] int, [row_id] int)


insert into #tmp_ini values (24, 670),
                            (25, 980),
                            (26, 24),
                            (28, 1570),
                            (28, 26),
                            (27, 5745),
                            (27, 4654)


select * into #tmp from #tmp_ini

insert into #tmp_allcols
    select col1,
            'col_' + cast(t.row_id as nvarchar(max)) as col ,
            col2 as val,
            t.row_id 
    from (SELECT * , ROW_NUMBER() OVER ( PARTITION  by col1  Order by col2  ) AS row_id FROM #tmp ) t


select @max_columns= max(m.tot) from(
        select COUNT(*) as tot from #tmp_allcols group by col1
     ) m


set @counter=1
while @counter <= @max_columns
    begin
        set @col_header += 'col_' + cast(@counter as nvarchar(50)) +  ', '
        set @counter = @counter + 1
    end


set @col_header = SUBSTRING(@col_header,1,LEN(@col_header)-1)


set @query += ' select * from ('
set @query += '     select col1, col, val from #tmp_allcols '
set @query += '  ) tmp'
set @query += ' PIVOT ( max(val) for Col in ('
set @query += @col_header
set @query += '     )) AS pvt'

print @query

exec sp_executesql @query

Current output:

col1    col_1   col_2
24      670     NULL
25      980     NULL
26      24      NULL
27      4654    5745
28      26      1570

Any tips or help is appreciated. I have looked at these posts, but couldn't get far SQL Pivot IF EXISTS ; `PIVOT` with `EXISTS` ; `PIVOT` with `EXISTS`

I couldn't understand the pivot, the 25 - 980 i got it because 980 is the only with 25. Your pivot was 24 - 670 - 26 (why it's not 25 instead of 26) — Paulo Fernando, Aug 24 '22 at 02:22
Hello @PauloFernando! Thank you for your input. You understood it right. 25 is not connected to 24 in any ways. However, 24 is connected to 26 and similarly, 28 & 1570 are connected to 26 so they all get rolled up next to 24. In no particular order, 24 - 670 - 26 (b/c 26 -> 24) - 28 - 1570 (b/c 1570- > 26 & 28 -> 26). Apologies if I confuse you. Essentially, I am trying to create all the child nodes of a parent and laying them out in one row — AOE_player, Aug 24 '22 at 02:36

score 4 · Accepted Answer · answered Aug 25 '22 at 02:53

I don't think there is an easy way to do what you need, that's why I skipped the easy way, and did some crazy SQL logic:

--#####################
--#### SCHEMA #########
--#####################
CREATE TABLE Entities(
    entity1 int NULL,
    entity2 int NULL
)

INSERT INTO Entities VALUES
(24, 670),
(25, 980),
(26, 24),
(28, 1570),
(28, 26),
(27, 5745),
(27, 4654)

--###############################
--########### QUERY #############
--###############################

IF OBJECT_ID('tempdb..#TempRelations') IS NOT NULL
DROP TABLE #TempRelations 

IF OBJECT_ID('tempdb..#TempEntities') IS NOT NULL
DROP TABLE #TempEntities

IF OBJECT_ID('tempdb..#FinalRelations') IS NOT NULL
DROP TABLE #FinalRelations

IF OBJECT_ID('tempdb..#FinalTable') IS NOT NULL
DROP TABLE #FinalTable

CREATE TABLE #TempRelations (entity INT NULL)
CREATE TABLE #FinalRelations (id INT, entity INT NULL)

SELECT ROW_NUMBER() OVER (ORDER BY entity1) Id, *
INTO #TempEntities
FROM Entities

DECLARE @entity1 INT, @entity2 INT
DECLARE @count INT = (SELECT COUNT(*) FROM #TempEntities)
DECLARE @relationsCount INT = 1;
DECLARE @i INT = 1
                    
--WHILE THERE ARE STILL ENTITIES
WHILE EXISTS(SELECT 1 
             FROM #TempEntities 
             WHERE entity1 IS NOT NULL 
                OR entity2 IS NOT NULL)
BEGIN
    SELECT @entity1 = entity1, @entity2 = entity2
    FROM #TempEntities
    WHERE Id = @i

    DELETE #TempRelations

    IF @entity1 IS NOT NULL
    BEGIN
        INSERT INTO #TempRelations VALUES (@entity1)
        INSERT INTO #TempRelations VALUES (@entity2)
    END

    UPDATE #TempEntities 
        SET entity1 = NULL, 
            entity2 = NULL
        WHERE Id = @i

    --WHILE THERE ARE STILL RELATIONS TO BE TAKEN FROM ENTITIES
    WHILE 1=1
    BEGIN       
        SET @i = 1

        WHILE @i <= @count
        BEGIN       
            SELECT @entity1 = entity1, @entity2 = entity2
            FROM #TempEntities
            WHERE Id = @i

            --IF entity1 OR entity2 HAS RELATION TO THE CURRENT TEMP RELATIONS, INSERT THE RELATION THEN REMOVE THE ENTITIES
            IF @entity1 IS NOT NULL AND ( EXISTS (SELECT 1
                                                  FROM #TempRelations
                                                  WHERE entity = @entity1
                                                  OR entity = @entity2) OR (SELECT COUNT(*) FROM #TempRelations) = 0 )
            BEGIN               
                INSERT INTO #TempRelations VALUES (@entity1)
                INSERT INTO #TempRelations VALUES (@entity2)
                UPDATE #TempEntities 
                SET entity1 = NULL, 
                    entity2 = NULL
                WHERE Id = @i
            END

            SET @i = @i + 1         
        END

        IF NOT EXISTS (SELECT 1 
            FROM #TempEntities
            WHERE EXISTS (SELECT 1
                          FROM #TempRelations
                          WHERE entity = entity1
                          OR entity = entity2))
        BREAK

    END

    INSERT INTO #FinalRelations
    SELECT DISTINCT @relationsCount,        
                    entity
    FROM #TempRelations 

    SET @relationsCount = @relationsCount + 1
    DELETE #TempRelations
END

SELECT 'col' + (CAST(ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS VARCHAR)) Col,
       id,
       entity
INTO #FinalTable
FROM #FinalRelations

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + col 
                      FROM #FinalTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' FROM 
             (
                SELECT Col, id, entity
                FROM #FinalTable
            ) x
            PIVOT 
            (
                MAX(entity)
                FOR Col IN (' + @cols + N')
            ) p '

PRINT (@query)

exec sp_executesql @query;

Appreciate it Paulo. This is amazing!! Thanks a lot. – AOE_player Aug 25 '22 at 03:21 — AOE_player, Aug 25 '22 at 03:21

Rolling up records by referencing values from one column to another

1 Answers1