problem
How to add extra row structure to SQL query pivot table using sql server 2012 ?
I need to add row structure of table as first row so that actually I need to do that as following
as below
SET @SQLHeader = SELECT 'ItemId' as ItemId,'IPN' as IPN,'PartnerName' as PartnerName,'CustomerName' as CustomerName,@Columns union all @SQls
meaning i will display data plus one record display as first row as structure of data
the following data is desired result
bold row i need to added
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
What I have tried:
Create proc [CustomerLocations].[sp_ItemFeaturesExporter]
@ImportFilePath varchar(500) ,
@ExportFilePath varchar(500)
as
begin
create table #ItemFeatures
(
CustomerName nvarchar(200),
CustomerId nvarchar(50)
)
Declare @sql nvarchar(max)
Set @sql= 'insert into #ItemFeatures
(
CustomerName
)
SELECT
CustomerName
FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0 Xml;HDR=YES;Database=' + @ImportFilePath + ''',''SELECT * FROM [Sheet1$]'')'
Exec(@sql)
update tmp
set tmp.CustomerId = c.CustomerID
from #ItemFeatures tmp inner join pcn.Customers c on c.CustomerName = tmp.CustomerName
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
FROM
--select distinct Features
(select distinct FeatureName from [CustomerLocations].[FeatureTypes]
) AS B
ORDER BY B.FeatureName
on features Name
DECLARE @SQLs as VARCHAR(MAX)
SET @SQLs = 'SELECT ItemId,IPN,PartnerName,CustomerName,' + @Columns + '
FROM
(
select F.ItemId ,t.FeatureName,F.FeatureValue,I.IPN,I.PartnerName,FI.CustomerName
from [CustomerLocations].[ItemFeatures] F
Inner Join [CustomerLocations].[Items] I ON F.ItemId=I.ItemId
inner join CustomerLocations.FeatureTypes T on T.FeatureId=F.FeatureId
inner join #ItemFeatures FI on I.CustomerID=FI.CustomerID
) as PivotData
PIVOT
(
max(FeatureValue)
FOR FeatureName IN (' + @Columns + ')
) AS PivotResult
'
EXEC(@SQLs)
drop table #ItemFeatures
END;
links for data
add extra row as first