Sql Sever Convert rows to Columns for multiple tables

You need to pivot 2 columns, so I would try this:

DECLARE @MaxCount INT;
DECLARE @MaxCount1 INT;

SELECT @MaxCount = max(cnt)
FROM (
    SELECT S.Id
        ,count(A.IPAddress) AS cnt
   FROM dbo.D2D S INNER JOIN dbo.D2D_IPAddress A on S.Id = A.D2D_ID
    GROUP BY S.Id
    ) X;
    
    SELECT @MaxCount1 = max(cnt1)
FROM (
    SELECT S.Id
        ,count(B.WWN) AS cnt1
   FROM dbo.D2D S INNER JOIN dbo.D2D_WWN B on S.Id = B.D2D_ID
    GROUP BY S.Id
    ) X1
    


DECLARE @SQL NVARCHAR(max), @Col1 nvarchar(max), @Col2 nvarchar(max),
    ,@i INT;

SET @i = 0;

WHILE @i < @MaxCount 
BEGIN
    SET @i = @i + 1;
    SET @Col1 = COALESCE(@Col1 + ', ', '') + 
'MAX(CASE WHEN Rn1 = ' + cast(@i AS NVARCHAR(10)) + 
' THEN IPAddress END) AS IPAddress' + cast(@i AS NVARCHAR(10)); 
END

SET @i = 0;

WHILE @i < @MaxCount1 
BEGIN
    SET @i = @i + 1;
    SET @Col2 = COALESCE(@Col2 + ', ', '') + 
MAX(CASE WHEN Rn2 = ' + cast(@i AS NVARCHAR(10)) + 
' THEN sno END) AS sno' + cast(@i AS NVARCHAR(10)); 

END

 SET @SQL = N';WITH CTE AS (
   SELECT S.ID, A.IPAddress, row_number() OVER (PARTITION BY S.ID ORDER BY A.IPAddress) AS Rn1

   FROM dbo.D2D S INNER JOIN dbo.D2D_IPAddress A on S.Id = A.D2D_ID
), cte2 AS (
SELECT S.ID, B.WWn as Sno, ROW_NUMBER() over (partition by S.ID order by B.wwn) as Rn2
from dbo.D2D S INNER JOIN dbo.D2D_WWN B on S.Id = B.D2D_ID), cte3 AS (

SELECT COALESCE(A.ID, B.ID) as ID, A.IPAddress, A.Rn1,
B.sno, B.Rn2 from cte A FULL JOIN cte2 B ON A.ID = B.ID
AND A.Rn1 = B.RN2)

select ID, ' + Col1 + ', ' + col2 + ' FROM cte3
GROUP BY ID';

PRINT @SQL;

EXECUTE (@SQL);

The above is from the top of my head (since you didn't provide the test data) so you may need to do minor adjustments.

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles