How to add extra row structure to SQL query pivot table as first row?

Transact-SQL
1

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;
2

Welcome

In order to help us help you please provide sample data in the following format for all tables involved?


create table #Customers(CustomerId int, CustomerName varchar(50))
insert into #Customers
select 1, 'Darth Vader' union
select 2, 'Ob1 K-nobby' union
select 3, 'Look Sky Water'

create table #FeatureTypes(FeatureName varchar(50))

insert into #FeatureTypes
select 'Light Saber' union
select 'Cloacking'

create table #Items(IPN int, PartnerName varchar(50))


create table #ItemFeatures
(

CustomerName nvarchar(200),
CustomerId nvarchar(50)

)
3 
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'),
(4,'DataParametric')

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 Features
(select distinct FeatureName from #FeatureTypes

) AS B
ORDER BY B.FeatureName

--select @Header=
--replace(@Columns , ',',''',''') 
select @Header=
replace(@Columns , ',',''',''') 

--select @Columns
--pivot table make count for item to every Feature Based on features Name
DECLARE @SQLs as VARCHAR(MAX)
DECLARE @SQLs1 as VARCHAR(MAX)
--SET @SQLs1 = 'UNION ALL
--SELECT 

--''ItemId'',''IPN'',''PartnerName'',''CustomerName'',''@Columns''

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)
1 Like
4

pretty ugly stuff. not sure why you want to do this? What are you actually trying to solve?

select @Header = 'select ''ItemId'' as ItemId, ''IPN '' as IPN, ''PartnerName as PartnerName'' as PartnerName, ''CustomerName''  as CustomerName, ''' +  replace(@Columns,',',''',''') + ''' '

SET @SQLs ='

 
SELECT cast(ItemId as nvarchar(50)) as ItemId,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 + ' union ' + @Header )
5

this is correct but i need line added by union not last row
i need it first row

6

requires adding a stub orderby column, still looks oogly

use sqlteam
go

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'),
(4,'DataParametric')

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 @Header varchar(max)
declare @SQLs varchar(max)
declare @Columns varchar(max)

SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
FROM
--select distinct Features
(select distinct FeatureName from #FeatureTypes

) AS B
ORDER BY B.FeatureName

select @Header = 'select ''ItemId'' as ItemId, ''IPN '' as IPN, 
''PartnerName as PartnerName'' as PartnerName, ''CustomerName''  as CustomerName, 
-1 _orderBy,''' +  replace(@Columns,',',''',''') + ''' '

SET @SQLs ='

 
SELECT cast(ItemId as nvarchar(50)) as ItemId,IPN,PartnerName,
CustomerName, 1 _orderBy, ' + @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
'

select @SQLs 
select @Header 

EXEC(  @SQLs + ' union ' + @Header + ' order by _orderBy')


drop table #ItemFeatures
drop table #temp
drop table #Customer
drop table #FeatureTypes
drop table #Items
7

Hi

Hope this helps..:+1::+1:

Select 1 as orderbyseq , column names
Union
Select 2 as orderbyseq , data
Order by orderbyseq

8

Using temporal tables it is possible to insert, delete or modify the SQL Database.