需要SQL过程进行更新，删除动态列和行并绑定到前端的asp.net gridview以进行更新和删除操作
问题描述：
大家好
我有三张桌子
1. [记录集]
Hi All
I have three tables
1.[record set]
RecordSetId InventoryId
701 11
702 11
703 12
704 12
705 13
706 13
707 11
708 11
709 19
710 19
711 20
2.app_fields
2.app_fields
Fieldid FieldTypeid FieldName IsMandatory IsEditable InventoryId ValidationSize
201 100 WebsitetName 1 0 11 NULL
202 100 WebsiteUrl 1 0 11 NULL
203 100 CountryName 1 0 11 NULL
204 100 KraftRegion 1 0 11 NULL
205 100 WebAnalytics 1 0 11 NULL
206 100 HostingProvider 1 0 11 NULL
207 100 Channel 1 0 11 NULL
208 100 Company 1 0 11 NULL
209 100 ArticleName 1 0 13 NULL
210 100 ArrticleLocation 1 0 13 NULL
211 100 CountryName 1 0 13 NULL
212 100 KraftRegion 1 0 13 NULL
213 100 WebAnalytics 1 0 13 NULL
214 100 ArticleProvider 1 0 13 NULL
215 100 ArticleChannel 1 0 13 NULL
216 100 ArticleCompany 1 0 13 NULL
217 100 AssetName 1 0 12 NULL
218 100 AssetLocation 1 0 12 NULL
219 100 CountryName 1 0 12 NULL
220 100 KraftRegion 1 0 12 NULL
221 100 AssetAnalytics 1 0 12 NULL
222 100 AssetProvider 1 0 12 NULL
223 100 AssetType 1 0 12 NULL
224 100 AssetCompany 1 0 12 NULL
225 100 WebsiteName 0 0 17 0
226 100 Region 0 0 17 0
227 100 WebsiteName 0 0 18 0
228 100 Region 0 0 18 0
229 100 AssetName 1 0 19 50
230 102 AssetType 1 0 19 20
231 100 Comobject 1 0 20 20
232 101 Comcomp 0 1 20 100
233 102 ThirdList 1 1 20 0
3. [资产明细]
3.[Asset detail]
AttributeId RecordSetId FieldId FieldValue
801 701 201 Google
802 701 202 www.Google.com/ncr
803 701 203 USA
804 701 204 Washington
805 701 205 Google Search Engine
806 701 206 Google Web Server
807 701 207 Website
808 701 208 GOOGLE INC
809 702 201 Yahoo
810 702 202 www.yahoo.com
811 702 203 USA
812 702 204 New York
813 702 205 Yahoo Website
814 702 206 Yahoo Web Server
815 702 207 Website
816 702 208 Yahoo Inc
817 703 217 Printer
818 703 218 CP-1st floor
819 703 219 Bangalore-India
820 703 220 India
821 703 221 Printing
822 703 222 HP Laser jet
823 703 223 Print
824 703 224 Infy
825 707 201 WebsitetName1
826 707 202 WebsiteUrl1
827 707 203 CountryName1
828 707 204 KraftRegion1
829 707 205 WebAnalytics1
830 707 206 HostingProvider1
831 707 207 Channel1
832 707 208 Company1
833 708 201 WebsitetName2
834 708 202 WebsiteUrl2
835 708 203 CountryName2
836 708 204 KraftRegion2
837 708 205 WebAnalytics2
838 708 206 HostingProvider2
839 708 207 Channel2
840 708 208 Company2
841 709 229 AssetName1
842 709 230 AssetType1
843 710 229 AssetName2
844 710 230 AssetType2
845 711 231 Col1
846 711 232 Col2
847 711 233 Col3
我希望输出基于inventoryid [例如：inventoryid = 11 ]所以我这样查询并加载相应的数据。以及最重要的事情要记住......所有的表都是动态的，并且将由管理员使用excel表进行更新，并且在UI部分中，用户必须单独启用更新操作。我需要更新存储过程中的操作
I want the output based on inventoryid [for example:inventoryid=11] so I queried like this with the corresponding datas to load in it.and the very most important things to keep it in mind that ...all the tables are Dynamic and will updated using excel sheet by administrator and in the UI part the User have to Enabled for update operation alone..I am in need of Updating operation in the stored procedure
SELECT AF.FIELDNAME,AD.FIELDVALUE FROM [APP_FIELDS] AF INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID =11
输出
and the output is
FIELDNAME FIELDVALUE
WebsitetName Google
WebsiteUrl www.Google.com/ncr
CountryName USA
KraftRegion Washington
WebAnalytics Google Search Engine
HostingProvider Google Web Server
Channel Website
Company GOOGLE INC
WebsitetName Yahoo
WebsiteUrl www.yahoo.com
CountryName USA
KraftRegion New York
WebAnalytics Yahoo Website
HostingProvider Yahoo Web Server
Channel Website
Company Yahoo Inc
我想要将结果表转换为inventoryid = 11和表格标题，数据应该排列成这样的行
我想要的输出
I want to transpose the resultant table with for the inventoryid=11 with the table headers and Datas should be arranged in rows like this
the output i want to be
WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company
Google www.Google.com USA Washington Google Search Engine Google Web Server Website GOOGLE INC
yahoo www.yahoo.com .............................................................................
WebsitetName1 ..................................................................................
我到目前为止尝试的是
只是粘贴第一次尝试的伪代码：
what i tried so far is
just pasting the pseudocode of a first try:
DECLARE #temptable table
temptable=select * from app_fields where inventoryid=11 into ##temptable where 1=2
Declare @RecordSetId,FieldId
Declare @TableData
set @ReacordSetId=select RecordSetId from [Record Set] from where inventoryid=11
set FieldId=temptable[0]
foreach row in temptable
TableData+= getFieldValue(@RecordSetId,FieldId) as temptable[row][2]
end foreach
@print TableData
--------------------------------------------------------
create function getFieldValue( @RecordSetId int,@FieldId int)
RETURNS TABLE AS RETURN
( select FieldValue from [Asset Detail] where RecordSetId=@RecordSetId and FieldId=@FieldId )
GO
SELECT * FROM [ASSET DETAIL]
-------------------------------- ----------------------------
第二件事是存储的proc：
------------------------------------------------------------
the second thing is the stored proc :
CREATE PROCEDURE [dbo].[SP_APP_FIELDS]
@inventoryid [int]
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX),
@cols_WITH_MAX AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
-- @inventoryid
SELECT @cols_WITH_MAX =
STUFF((SELECT DISTINCT ',MAX('+ QUOTENAME(fieldname) +') AS ' + QUOTENAME(fieldname) FROM app_fields
WHERE inventoryid = @inventoryid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @cols =
STUFF((SELECT distinct ',' + QUOTENAME(fieldname) from app_fields
where inventoryid = @inventoryid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT '+ @cols_WITH_MAX +' FROM
(SELECT [inventoryid], ' + @cols + ' from
(SELECT FieldValue,fieldname,AF.inventoryid FROM [app_fields] AF join [ASSET DETAIL] AD on AD.FieldId = AF.FieldId
--join [RECORD SET] RS ON AF.inventoryid = RS.inventoryid
where AF.inventoryid ='+cast(@inventoryid as varchar(20)) +')X
pivot
(
max([FieldValue])
for [fieldname] in (' + @cols + ')
) p )a GROUP BY [inventoryid]'
print(@query)
execute(@query)
end
GO
即使我也可以给你们DB数据库脚本......
1.Record Set
Even I can give you guys the DB scripts too...
1.Record Set
CREATE TABLE [dbo].[RECORD SET](
[RecordSetId] [int] NOT NULL,
[InventoryId] [int] NULL,
CONSTRAINT [PK_RECORD SET] PRIMARY KEY CLUSTERED
(
[RecordSetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (701, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (702, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (703, 12)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (704, 12)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (705, 13)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (706, 13)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (707, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (708, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (709, 19)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (710, 19)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (711, 20)
2.资产明细
2.Asset Detail
CREATE TABLE [dbo].[ASSET DETAIL](
[AttributeId] [int] NOT NULL,
[RecordSetId] [int] NULL,
[FieldId] [int] NULL,
[FieldValue] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ASSET DETAIL] PRIMARY KEY CLUSTERED
(
[AttributeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (801, 701, 201, N'Google')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (802, 701, 202, N'www.Google.com/ncr')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (803, 701, 203, N'USA')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (804, 701, 204, N'Washington')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (805, 701, 205, N'Google Search Engine')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (806, 701, 206, N'Google Web Server')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (807, 701, 207, N'Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (808, 701, 208, N'GOOGLE INC')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (809, 702, 201, N'Yahoo')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (810, 702, 202, N'www.yahoo.com')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (811, 702, 203, N'USA')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (812, 702, 204, N'New York')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (813, 702, 205, N'Yahoo Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (814, 702, 206, N'Yahoo Web Server')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (815, 702, 207, N'Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (816, 702, 208, N'Yahoo Inc')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (817, 703, 217, N'Printer')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (818, 703, 218, N'CP-1st floor')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (819, 703, 219, N'Bangalore-India')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (820, 703, 220, N'India')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (821, 703, 221, N'Printing')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (822, 703, 222, N'HP Laser jet')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (823, 703, 223, N'Print')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (824, 703, 224, N'Infy')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (825, 707, 201, N'WebsitetName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (826, 707, 202, N'WebsiteUrl1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (827, 707, 203, N'CountryName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (828, 707, 204, N'KraftRegion1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (829, 707, 205, N'WebAnalytics1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (830, 707, 206, N'HostingProvider1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (831, 707, 207, N'Channel1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (832, 707, 208, N'Company1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (833, 708, 201, N'WebsitetName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (834, 708, 202, N'WebsiteUrl2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (835, 708, 203, N'CountryName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (836, 708, 204, N'KraftRegion2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (837, 708, 205, N'WebAnalytics2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (838, 708, 206, N'HostingProvider2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (839, 708, 207, N'Channel2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (840, 708, 208, N'Company2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (841, 709, 229, N'AssetName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (842, 709, 230, N'AssetType1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (843, 710, 229, N'AssetName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (844, 710, 230, N'AssetType2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (845, 711, 231, N'Col1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (846, 711, 232, N'Col2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (847, 711, 233, N'Col3')
3.App_fields
3.App_fields
CREATE TABLE [dbo].[APP_FIELDS](
[Fieldid] [int] NOT NULL,
[FieldTypeid] [int] NOT NULL,
[FieldName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsMandatory] [bit] NULL,
[IsEditable] [bit] NULL,
[InventoryId] [int] NULL,
[ValidationSize] [int] NULL,
CONSTRAINT [PK_APP_FIELDS] PRIMARY KEY CLUSTERED
(
[Fieldid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (201, 100, N'WebsitetName', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (202, 100, N'WebsiteUrl', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (203, 100, N'CountryName', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (204, 100, N'KraftRegion', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (205, 100, N'WebAnalytics', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (206, 100, N'HostingProvider', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (207, 100, N'Channel', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (208, 100, N'Company', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (209, 100, N'ArticleName', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (210, 100, N'ArrticleLocation', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (211, 100, N'CountryName', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (212, 100, N'KraftRegion', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (213, 100, N'WebAnalytics', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (214, 100, N'ArticleProvider', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (215, 100, N'ArticleChannel', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (216, 100, N'ArticleCompany', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (217, 100, N'AssetName', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (218, 100, N'AssetLocation', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (219, 100, N'CountryName', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (220, 100, N'KraftRegion', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (221, 100, N'AssetAnalytics', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (222, 100, N'AssetProvider', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (223, 100, N'AssetType', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (224, 100, N'AssetCompany', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (225, 100, N'WebsiteName', 0, 0, 17, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (226, 100, N'Region', 0, 0, 17, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (227, 100, N'WebsiteName', 0, 0, 18, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (228, 100, N'Region', 0, 0, 18, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (229, 100, N'AssetName', 1, 0, 19, 50)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (230, 102, N'AssetType', 1, 0, 19, 20)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (231, 100, N'Comobject', 1, 0, 20, 20)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (232, 101, N'Comcomp', 0, 1, 20, 100)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (233, 102, N'ThirdList', 1, 1, 20, 0)
the UI page is like this
the UI page is like this
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Digital_Asset_Inventory.Default1" %>
<%@ Register src="Header.ascx" tagname="Header" tagprefix="uc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link href="/Styles/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="0" cellpadding="0" cellspacing="0" width="950px" align="center">
<%--***** HEADER SECTION *****--%>
<tr><td valign="top"><uc1:Header ID="Header1" runat="server" /></td></tr>
<tr>
<td>
<asp:DropDownList ID="drp_Inventory" runat="server" AutoPostBack="true">
<asp:ListItem Text="Reusable Assets" Value="11"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr><td class="h10"></td></tr>
<%--****** DATA SECTION TO UPDATE ******--%>
<tr>
<td>
<asp:GridView ID="gv_UserContents" runat="server" HeaderStyle-CssClass="GridHeadC" CssClass="GridDataC" AllowPaging="true" AllowSorting="true">
</asp:GridView>
</td>
<
答
While I did not go through the whole thing, looking at the desired output you need a pivot query!
blatant plug for article Pivot two or more columns in SQL Server 2005[^]. This may help it is perfectly valid for 2008 and later!
While I did not go through the whole thing, looking at the desired output you need a pivot query!
blatant plug for article Pivot two or more columns in SQL Server 2005[^]. This may help it is perfectly valid for 2008 and later!
Hi you could try following query:
Hi you could try following query:
DECLARE @FieldName NVARCHAR(MAX)
SET @FieldName = 'SELECT '
SELECT @FieldName = @FieldName + ''''+AD.FIELDVALUE + ''''+ ' AS ' + AF.FIELDNAME+ ',' FROM
[APP_FIELDS] AF
INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID
INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID =11
SET @FieldName = LEFT(@FieldName, LEN(@FieldName) - 1)
EXEC sp_Executesql @FieldName