Let create simple two column table:
CREATE TABLE [dbo].[LotNo]( [IDNo] [bigint], [LotNo] [varchar](50) NULL )
Now insert some records to the table:
insert into LotNo ([IDNo], LotNo)
select 1, 'A' union all
select 2, 'A' union all
select 3, 'A' union all
select 4, 'A' union all
select 5, 'A' union all
select 6, 'A' union all
select 1, 'B' union all
select 2, 'B' union all
select 3, 'B' union all
select 1, 'C' union all
select 2, 'C' union all
select 3, 'C' union all
select 4, 'C' union all
select 1, 'D' union all
select 2, 'D' union all
select 1, 'E' union all
select 2, 'E' union all
select 3, 'E' union all
select 1, 'F' union all
select 2, 'F' union all
select 3, 'F' union all
select 4, 'F' union all
select 5, 'F' union all
select 6, 'F' union all
select 1, 'G' union all
select 2, 'H' union all
select 3, 'I' union all
select 4, 'J' union all
select 6, 'K'
Now, this is the sql statement to show the the result in columns
select LotNO, max(case when IDNo = 1 then IDNo else '' end) as Col1,
max(case when IDNo = 2 then IDNo else '' end) as Col2,
max(case when IDNo = 3 then IDNo else '' end) as Col3,
max(case when IDNo = 4 then IDNo else '' end) as Col4,
max(case when IDNo = 5 then IDNo else '' end) as Col5,
max(case when IDNo = 6 then IDNo else '' end) as Col6
from LotNo
group by LotNO
You can use this approach to produce permutation table.
