I have two tables with data, let's call them table T and table B. Relevant data in T and B have a many-to-one relationship with each other, like this:

etc. (This is just a rough demonstration and doesn't resemble the actual data I'm using).

I've run a count(*) query and found that the most number of times items from table T appear in table B is 8. What I want is to transpose the above table, which has thousands and thousands of entries in T and several hundred possible entries for B's corresponding value, into a 9-column table, like this:

etc.

The only search info I can find requires either manually joining B each time and excluding previous values of B.code (as I'm currently doing it), or using a PIVOT table (dynamic SQL or otherwise), which would necessarily have one column for each and every possible value of B.code (which, as mentioned, is several hundred values), neither of which are scalable options.

For reference, my code right now looks something like this:

SELECT
T.id,
b1.code as 'CODE 1', b2.code as 'CODE 2', b3.code as 'CODE 3', b4.code as 'CODE 4' (etc)
FROM T
LEFT JOIN B b1 ON b1.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id)
LEFT JOIN B b2 ON b2.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code))
LEFT JOIN B b3 ON b3.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code, b2.code))
LEFT JOIN B b4 ON b4.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code, b2.code, b3.code))

What are my options? Am I doomed? There has to be a better way, right?

CodePudding user response：

The query you want to end up with is:

;WITH x AS 
 (
   SELECT [T.id], Code, rn = ROW_NUMBER() OVER 
     (PARTITION BY [T.id] ORDER BY @@SPID)
   FROM dbo.B
 ) SELECT [T.id], [CODE 1] = p.[1],[CODE 2] = p.[2], ... 
   FROM x
   PIVOT (MAX(Code) FOR rn IN 
   ([1],[2],...)) AS p;

But you need dynamic SQL to get there without having to hardcode ...Code 8 or add Code 9 when another code gets added to the table:

DECLARE @sql     nvarchar(max) = N'',
  @outputColumns nvarchar(max),
  @pivotColumns  nvarchar(max),
  @maxCount      int = (SELECT MAX(c) FROM 
    (SELECT COUNT(*) FROM dbo.B GROUP BY [T.id]) AS c(c));

;WITH x(n) AS (SELECT 1 UNION ALL SELECT n 1
  FROM x WHERE n < @maxCount)
SELECT @outputColumns = STRING_AGG(CONCAT('[CODE ',n,
                        '] = p.',QUOTENAME(n)), ','),
       @pivotColumns  = STRING_AGG(QUOTENAME(n), N',')
 FROM x;
 
 SELECT @sql = N';WITH x AS 
 (
   SELECT [T.id], Code, rn = ROW_NUMBER() OVER 
    (PARTITION BY [T.id] ORDER BY @@SPID)
   FROM dbo.B
 ) SELECT [T.id], '   @outputColumns   N' 
   FROM x
   PIVOT (MAX(Code) FOR rn IN 
   ('   @pivotColumns   N')) AS p;';
 
 EXEC sys.sp_executesql @sql;

Output (from sample data in the question):

T.id	CODE 1	CODE 2	CODE 3	CODE 4
A	1	1.2	null	null
B	1.2	1.5	null	null
C	1	2	3	15
D	296	null	null	null

• Example db<>fiddle

CodePudding user response：

Untested, but perhaps this will help

Select *
 From (
        Select T.id
              ,B.Code
              ,Col = concat('Code ',row_number() over (partition by T.id order by B.code) )
         From T
         Join B on B.ID=T.ID
      ) src
 Pivot (max(Code) for Col in ([Code 1],
                              [Code 2],
                              [Code 3],
                              [Code 4],
                              [Code 5],
                              [Code 6],
                              [Code 7],
                              [Code 8]) ) pvt