SQLTeam.com | Weblogs | Forums

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

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;

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)

)
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

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 )

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

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

Hi

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

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

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