June 4, 2014 at 4:08 am
Hi All,
In my current project due to the new requirement we have decomposed the table Asset in to three tables Asset--having the serial number of the asset alone, Asset attribute- having the list of attributes as rows which were columns in previous approach and asset data which will have serialnumber id from asset and attribute id from assetattribute and the actual value of the attribute.
This we database becomes more extensible if new attributes flows in.
Now in order to use the data further in normal other process i have written the below code to pivot the data.
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @ColumnName1 AS NVARCHAR(MAX)
SELECT @ColumnName1= ISNULL(@ColumnName1 + ',','') + 'min('+QUOTENAME(description)+')'+' as '+description
FROM (SELECT DISTINCT description FROM AssetAttribute) AS attributes
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(description)
FROM (SELECT DISTINCT description FROM AssetAttribute) AS attributes
SET @DynamicPivotQuery =
N' (SELECT ' + @ColumnName1 + '
into #asset FROM
(select ad.id,a.description,ad.attribute_id,ad.serialnumber_id,ad.attribute_value,at.serialnumber from AssetAttribute a
join assetdata ad on ad.Attribute_id=a.id
join asset at on at.id=ad.Serialnumber_id) AS SOURCE
pivot (min(attribute_value) FOR [DESCRIPTION] IN(' + @ColumnName + ')) AS PVTTable
group by serialnumber)
select * from #asset'
EXEC sp_executesql @DynamicPivotQuery
--select * from #asset
--select @DynamicPivotQuery
Can some one please help some other better approach?
table definitions are below
Create Table Asset(
id int identity(1,1),
Serialnumber varchar(65),
createddate datetimeoffset,
createdby varchar(15),
PRIMARY KEY CLUSTERED(
id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Unify_DM_Mater]
) ON [Unify_DM_Mater]
Create Table AssetAttribute(
Id int identity(1,1),
Description varchar(50),
Datatype varchar(30),
Createddate datetimeoffset,
createdby varchar(15),
Primary Key Clustered(
Id Asc)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Unify_DM_Mater]
) ON [Unify_DM_Mater]
Create Table AssetData(
Id int identity(1,1),
Serialnumber_id int,
Attribute_id int,
Attribute_Value varchar(65),
Createddate datetimeoffset,
createdby varchar(15),
Primary Key Clustered(
id Asc)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Unify_DM_Mater]
) ON [Unify_DM_Mater]
June 4, 2014 at 6:26 am
Does your approach work? If not, what is wrong with it? Does it have performance problems? If so, have you looked at the execution plans? What do they show?
June 4, 2014 at 9:14 am
There's an alternative you can read about in here:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply