1

Topic: Incomplete connection

T-sql
The code:

Create table #t (Name Varchar (20), Num int)
Insert into #t Values (' Vasja ', 5)
Insert into #t Values (' Vasja ', 6)
Insert into #t Values (' Vasja ', 7)
Insert into #t Values (' Vasja ', 8)
Insert into #t Values (' Petja ', 1)
Insert into #t Values (' Attracting ', 2)
Insert into #t Values (' Kohl ', 3)
Insert into #t Values (' Kohl ', 4)
Select P.Num as Petja, K.Num as Kohl, M.Num as Attracting v. Num as Vasja
From #t P
left join #t K on K.Name = ' Kohl'
left join #t M on M.Name = ' Attracting'
left join #t V on V.Name = ' Vasja'
where P.Name = ' Petja'
Drop table #t

Returns:
Petja Kohl Attracting Vasja
1 3 2 5
1 3 2 6
1 3 2 7
1 3 2 8
1 4 2 5
1 4 2 6
1 4 2 7
1 4 2 8
It is necessary to receive somehow from the initial table:
Petja Kohl Attracting Vasja
1 3 2 5
0 4 0 6
0 0 0 7
0 0 0 8
(Instead of 0 it is possible NULL)
I.e. if in "subquery" of lines it is less, than in maximum its value not to repeat.
It is possible?

2

Re: Incomplete connection

4E.6yPAIIIKA, make the table from 1 to the maximum quantity of lines for one Name, enumerate all values for everyone name, connect this table to numbers on coincidence of ordinal value of value to number in the table.

3

Re: Incomplete connection

declare @t table (Name Varchar (20), Num int)
Insert into @t Values (' Vasja ', 5)
Insert into @t Values (' Vasja ', 6)
Insert into @t Values (' Vasja ', 7)
Insert into @t Values (' Vasja ', 8)
Insert into @t Values (' Petja ', 1)
Insert into @t Values (' Attracting ', 2)
Insert into @t Values (' Kohl ', 3)
Insert into @t Values (' Kohl ', 4)
;with t_with_row_no as (
select *
, row_number () over (partition by Name order by Num) as row_no
from @t
)
select Petja, Kohl, Attracting, Vasja
from t_with_row_no
pivot (max (Num) for Name in (Petja, Kohl, Attracting, Vasja)) as p

4

Re: Incomplete connection

Thanks.