Bdtjtk

Is there a way to use SQL Pivot/Unpivot to produce the output under Converted below from the table Original?

No, there is no variation of PIVOT/UNPIVOT that will let you create that first row in your "converted" output, with the id in one column and the values of type for each of the other columns.

That's going to require some custom select to create the first row, and then UNION with an UNPIVOT to create the rest of the rows.

Since no aggregation is being performed, I don't know why you want to do this in SQL. Personally I would do this in the presentation layer.

You can achieve the "converted" layout with dynamic sql, but be aware of performance and security problems of this approach.

Moreover as said by Tab Alleman in his answer this is clearly a work for the presentation layer.

If you still want to do this in SQL Server, here is a possible solution:

CREATE TABLE [dbo].[Test] (

     [id]     int

    ,[type]   varchar(50)

    ,NBR      varchar(50)

    ,c1_pos   int

    ,c1_neg   int

    ,c2_pos   int

    ,c2_neg   int   )



INSERT INTO [dbo].[Test]

values 

 (8375, 'Type #1', 'P #1', 1, 0, 0, 0)

,(8375, 'Type #2', 'P #2', 0, 1, 0, 0)

,(8375, 'Type #3', 'P #1', 0, 1, 0, 0)

,(8375, 'Type #4', 'P #1', 1, 0, 0, 0)

,(8375, 'Type #5', 'P #1', 0, 0, 0, 0)



--this variable holds the value that will become the header of the first column 

declare @first_column_header nvarchar(10)

--this variable holds all the dates that will become column names 

declare @headers nvarchar(max)=''

--this variable contains the TSQL dinamically generated 

declare @sql nvarchar(max)=''  



SELECT @first_column_header = max(id) FROM [dbo].[Test]  group by [id]

select @headers = @headers + ', ' + QUOTENAME([type]) from [dbo].[Test] 

set @headers = RIGHT(@headers, len(@headers) - 2) 



set @sql = @sql + 'select piv.col as ' + QUOTENAME(@first_column_header) + ', ' + @headers + ' ' 

set @sql = @sql + 'from ' 

set @sql = @sql + '( ' 

set @sql = @sql + ' select [type], col, val, ord ' 

set @sql = @sql + ' from [dbo].[Test] ' 

set @sql = @sql + ' CROSS APPLY (' 

set @sql = @sql + '  VALUES (''NBR''    , cast(NBR    as varchar(10)) , 1), ' 

set @sql = @sql + '         (''c1_pos'' , cast(c1_pos as varchar(10)) , 2), ' 

set @sql = @sql + '         (''c1_neg'' , cast(c1_neg as varchar(10)) , 3), ' 

set @sql = @sql + '         (''c2_pos'' , cast(c2_pos as varchar(10)) , 4), ' 

set @sql = @sql + '         (''c2_neg'' , cast(c2_neg as varchar(10)) , 5) ' 

set @sql = @sql + ' )CS (col, val, ord) ' 

set @sql = @sql + ') src ' 

set @sql = @sql + 'pivot ( max(val) for [type] in (' + @headers + ') ) piv ' 

set @sql = @sql + 'order by ord' 



exec(@sql)

Result: