How to provide custom name to column in pivoting

Question

I have a table like this:

id  unit
1   mm  
2   cm 
3   kg

When I perform pivot operation on this, I am getting result as follows:

1  2  3
mm cm kg

Is it possible to get custom column names here, something like this:

d1  d2  d3
mm  cm  kg

I am using Pivot for this as:

IF OBJECT_ID('tempdb..#t') IS NOT NULL 
DROP TABLE #t
GO

CREATE  table #t 
(id varchar(max),unit varchar(max))
insert into #t (id,unit)values 
(1,'kg'),
(2,'cm'),
(3,'mm'),
(4,'m')

DECLARE @statement NVARCHAR(max)
   ,@columns NVARCHAR(max)

SELECT @columns = ISNULL(@columns + ',', '') + N'[' + cast(tbl.id as varchar(max)) + ']'
   FROM (
   SELECT DISTINCT id
   FROM #t
   ) AS tbl

   SELECT @statement =  'select * 
                  INTO ##temp 
                  from (
                    SELECT id,[unit]
                    FROM #t
                    ) as s 
PIVOT
(max(unit) FOR id in(' + @columns + ')) as pvt
'


EXEC sp_executesql @statement = @statement




SELECT * FROM ##temp
DROP TABLE #t
DROP TABLE ##temp

Is it possible? Thanks

Answer 1

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t (
    id VARCHAR(10),
    unit VARCHAR(100)
)
INSERT INTO #t (id, unit)
VALUES
    ('1', 'kg'),
    ('2', 'cm'),
    ('3', 'mm'),
    ('4', 'mm')

DECLARE @SQL NVARCHAR(MAX), @columns NVARCHAR(MAX)

SELECT @columns = STUFF((
    SELECT ',[D' + id + ']'
    FROM #t
    FOR XML PATH('')), 1, 1, '')

SELECT @SQL =  '
    SELECT * 
    FROM (
        SELECT [unit], col = N''D'' + id
        FROM #t
    ) s 
    PIVOT (MAX(unit) FOR col IN (' + @columns + ')) p'

EXEC sys.sp_executesql @SQL