SQL Server : fastest way to change rows to columns

Question

I've got an issue regarding two tables in my database that have the following columns:

COMMENT table

KEY 
TYPE
NUMBER
TEXT

Composite key made up of (KEY, TYPE, NUMBER)

RESULTS table

KEY
TYPE1
TYPE2
...
TYPE20
TEXT1
TEXT2
...
TEXT20

An example of the COMMENT table would be this:

KEY | TYPE | NUMBER | TEXT|
1      A     0001     SAMPLETEXT
1      A     0002     SAMPLETEXT2
1      B     0001     SAMPLETEXT3
1      B     0002     SAMPLETEXT4
1      B     0003     SAMPLETEXT5
2      C     0001     SAMPLETEXT6
2      C     0002     SAMPLETEXT7
3      A     0001     SAMPLETEXT8

For each KEY, there are only 3 different types A,B,C and one TYPE may have up to 0020 in the NUMBER field. These records are ordered by KEY then by TYPE

I need to accomplish the following: for each KEY in COMMENT table, insert the first 20 TYPE rows into each column inside de RESULTS table (TYPE1 for the first type in the Comment table, TYPE2 for the second type in the comment table and so on) and insert the first 20 TEXT rows into each column inside the RESULTS table (TEXT1 for the first text, TEXT2 for the second text and so on)

The RESULTS table would look like this:

KEY | TYPE1 | TYPE2 | TYPE3 | ... | TYPE20 | TEXT1 | TEXT2 | ... | TEXT20
1      A        A      B             NULL    SAMPTE1 SAMPTE2       NULL
2      C        C      NULL          NULL    SAMPTE6 SAMPTE7       NULL
3      A     NULL      NULL  ...    NULL     SAMPTE7 NULL    ....  NULL

The RESULTS table would have a row per each KEY and up to 20 TEXT fields along with their corresponding type.

As you can see, this RESULTS table was clearly bad designed. It was made in the 70's and we can't change it.

Some questions might pop up when implementing this, here are the answers:

1. What if a KEY has more than 20 TEXT in the COMMENT table? We don't care, we just insert the first 20 of them
2. What if I have 23 type A text and 10 type B text, for example? Then just the first 20 type A text would appear in RESULTS
3. Is there any way the RESULTS table could be changed? Unfortunately there isn't.
4. Does TYPE1 match TEXT1 in the RESULTS table and TYPE2 match TEXT2 and so on? Yes, also, the amount of columns that match is the same.
5. If there are less than 20 TEXT in COMMENT table, the rest of the values of TEXT and TYPE in RESULTS is null? Yes

The question is, what is the cleanest, fastest, scalable, non tedious way of implementing this?

Also, how could it be implemented, such that in a future the RESULTS table could have N more TYPE columns and N more TEXT columns?

I've heard about using PIVOT tables, joins and many other techniques but I don't know how to make this thing happen.

Answer 1

You can read all about pivot operations in the T-SQL documentation. Your answer requires a double pivot: once to pivot the Type for each [Key,Number] combination and once to pivot the Text for each [Key,Number] combination.

In order to avoid additional aggregations on the Key column, you can split these two pivot actions in separate subqueries. The solution below places those two pivot subqueries in two common table expressions (CTE's) named piv1 and piv2. Note that each subquery starts from only those columns required for the output. This column filtering is done with an additional subquery called comm.

The final query joins piv1 and piv2 on their common Key column and inserts the output in the predefined Result table.

A non-tedious way for N Type or Text columns will require dynamic SQL. While such a solution is non-tedious it is also very non-trivial! If you are still new to concepts like join and pivot as your question suggests, then I strongly advise against going that route for now. Copy-pasting some column names will be much faster than developing and maintaining a dynamic SQL query.

Sample data

create table Comment
(
  [Key] int,
  [Type] nvarchar(1),
  [Number] nvarchar(4), --> changing to numeric type will drop the leading zeros
  [Text] nvarchar(20)
)

insert into Comment ([Key], [Type], [Number], [Text]) values
(1, 'A', '0001', 'SAMPLETEXT'),
(1, 'A', '0002', 'SAMPLETEXT2'),
(1, 'B', '0001', 'SAMPLETEXT3'),
(1, 'B', '0002', 'SAMPLETEXT4'),
(1, 'B', '0003', 'SAMPLETEXT5'),
(2, 'C', '0001', 'SAMPLETEXT6'),
(2, 'C', '0002', 'SAMPLETEXT7'),
(3, 'A', '0001', 'SAMPLETEXT8');

create table Result
(
  [Key] int,
  [Type1] nvarchar(1),
  [Type2] nvarchar(1),
  [Type3] nvarchar(1),
  -- repeat for [Type4] to [Type19]
  [Type20] nvarchar(1),
  [Text1] nvarchar(20),
  [Text2] nvarchar(20),
  [Text3] nvarchar(20),
  -- repeat for [Text4] to [Text19]
  [Text20] nvarchar(20)
);

Solution

Combining a double pivot in separate CTE's, a join and an insert operation in a single query statement.

with piv1 as
(
  select piv.[Key] as [Key],
         piv.[0001] as [Type1],
         piv.[0002] as [Type2],
         piv.[0003] as [Type3],
         -- repeat for [Type4] to [Type19]
         piv.[0020] as [Type20]
  from ( select c.[Key], c.[Type], c.[Number] from comment as c ) as comm
  pivot (min(comm.[Type]) for comm.[Number] in ([0001], [0002], [0003], [0020])) piv -- add values [0004] to [0019]
),
piv2 as
(
  select piv.[Key] as [Key],
         piv.[0001] as [Text1],
         piv.[0002] as [Text2],
         piv.[0003] as [Text3],
         -- repeat for [Text4] to [Text19]
         piv.[0020] as [Text20]
  from ( select c.[Key], c.[Text], c.[Number] from comment as c ) as comm
  pivot (min(comm.[Text]) for comm.[Number] in ([0001], [0002], [0003], [0020])) piv -- add values [0004] to [0019]
)
insert into Result ([Key],
                    [Type1], [Type2], [Type3], [Type20], -- add columns [Type4] to [Type19]
                    [Text1], [Text2], [Text3], [Text20]) -- add columns [Text4] to [Text19]
select piv1.[Key],
       piv1.[Type1], piv1.[Type2], piv1.[Type3], piv1.[Type20], -- add columns piv1.[Type4] to piv1.[Type19]
       piv2.[Text1], piv2.[Text2], piv2.[Text3], piv2.[Text20]  -- add columns piv2.[Text4] to piv2.[Text19]
from piv1
join piv2 on piv2.[Key] = piv1.[Key];

Result

Key  Type1  Type2  Type3  Type20  Text1        Text2        Text3        Text20
---  -----  -----  -----  ------  -----------  -----------  -----------  -----------
1    A      A      B      null    SAMPLETEXT   SAMPLETEXT2  SAMPLETEXT5  null
2    C      C      null   null    SAMPLETEXT6  SAMPLETEXT7  null         null
3    A      null   null   null    SAMPLETEXT8  null         null         null

Fiddle to see things in action.