Convert Rows to Columns in SQL Server
This is the simple code to convert records rows to columns:
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.
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.
