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:
- What if a KEY has more than 20 TEXT in the
COMMENTtable? We don’t care, we just insert the first 20 of them - 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 - Is there any way the
RESULTStable could be changed? Unfortunately there isn’t. - Does TYPE1 match TEXT1 in the
RESULTStable and TYPE2 match TEXT2 and so on? Yes, also, the amount of columns that match is the same. - If there are less than 20 TEXT in
COMMENTtable, 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.
Advertisement
Answer
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.