How to add extra row structure contains columns name as first row?

Question

problem

I work on SQL server 2012 and I facing problem I cannot add Row contain column

names as first row meaning i need to show columns name within data as rows

as you see Required Result I added columns name within rows data

So How to do that ?

This is Current result

ItemId  IPN PartnerName CustomerName    Fan Motor   Refrigator  temprature
    1   1233    Saico   NULL    NULL    NULL    NULL    55567
    2   5433    Mbaby   NULL    23444   NULL    NULL    NULL
    3   590444  nagieb  NULL    NULL    NULL    556666  NULL

Required or desired Result

ItemId  IPN PartnerName CustomerName    Fan Motor   Refrigator  temprature
   ItemId   IPN PartnerName CustomerName    Fan Motor   Refrigator  temprature
    1   1233    Saico   NULL    NULL    NULL    NULL    55567
    2   5433    Mbaby   NULL    23444   NULL    NULL    NULL
    3   590444  nagieb  NULL    NULL    NULL    556666  NULL

Code below achieve my current result

create table #temp
(
CustomerName nvarchar(200),
CustomerId nvarchar(50)    
)
insert into #temp
(
CustomerId,
CustomerName
)    
values
('1','Avidyne')

create table #FeatureTypes
(
FeatureId int,
FeatureName nvarchar(200)
)
insert into #FeatureTypes
(
FeatureId,
FeatureName
)

values
(1,'Fan'),
(2,'Refregator'),
(3,'Cars')

    create table #Customer
    (
    CustomerId int,
    CustomerName nvarchar(200)
    )
    insert into #Customer values
    ('1','Avidyne')

    create table #Items
    (
    ItemId int,
    CustomerId int,
    IPN nvarchar(50),
    PartnerName nvarchar(50)
    )
    insert into #Items (ItemId,CustomerId,IPN,PartnerName)
    values
    (1, 1,'1055','Magic'),
    (2, 1,'4077','DataValidation'),
    (3, 1,'3034','Moran')

    create table #ItemFeatures
    (
    ItemId int,
    FeatureId int,
    FeatureValue nvarchar(50)
    )
    insert into #ItemFeatures (ItemId,FeatureId,FeatureValue)
    values
    (1, 1,'10'),
    (2, 2,'40'),
    (2, 1,'30'),
    (1, 2,'20'),
    (3, 1,'90'),
    (1, 3,'180')

    DECLARE @Columns as VARCHAR(MAX),@Header as VARCHAR(MAX)
    SELECT @Columns =
    COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
    FROM
    (select distinct FeatureName from #FeatureTypes

    ) AS B
    ORDER BY B.FeatureName
    DECLARE @SQLs as VARCHAR(MAX)



    SET @SQLs = 'SELECT cast(ItemId as nvarchar(50)),IPN,PartnerName,CustomerName,' + @Columns + '
    FROM
    (
    select F.ItemId ,t.FeatureName,F.FeatureValue,I.IPN,I.PartnerName,FI.CustomerName
    from #ItemFeatures F
    Inner Join #Items I ON F.ItemId=I.ItemId
    inner join #FeatureTypes T on T.FeatureId=F.FeatureId
    inner join #temp FI on I.CustomerID=FI.CustomerID

    ) as PivotData
    PIVOT
    (
    max(FeatureValue)
    FOR FeatureName IN (' + @Columns + ')
    ) AS PivotResult
    '

    EXEC(@SQLs)

Answer 1

You need something like SELECT 'Cars' AS Cars, 'Fan' AS Fan, 'Refregator' AS Refregator and then union all your existing query result

this will generate the header string

DECLARE @ColHeader as VARCHAR(MAX)
SELECT @ColHeader = COALESCE(@ColHeader + ', ','') + '''' + FeatureName + ''' AS ' + FeatureName
FROM
(
    select distinct FeatureName from #FeatureTypes
) AS B
ORDER BY B.FeatureName

Then change your existing query to as below

SET @SQLs = 'SELECT ''ItemId'' as ItemId, ''IPN'' as IPN, ''PartnerName'' as PartnerName, ''CustomerName'' as CustomerName,' + @ColHeader + ' '
          + 'UNION ALL '
          + 'SELECT cast(ItemId as nvarchar(50)),IPN,PartnerName,CustomerName,' + @Columns + '

if you encounter error, do a PRINT @SQLs and check the query