December 9, 2011 at 6:24 am
Table Name :- DocumentLog
Id FieldName FieldValue
1 Name John Lucas
2 DOB 12/11/1990
3 Amount 5000
I want to show the record in the following manner.
Name DOB Amount
John Lucas 12/11/1990 5000
December 9, 2011 at 6:31 am
Table Name :- DocumentLog
Id FieldName FieldValue
1 Name John Lucas
2 DOB 12/11/1990
3 Amount 5000
4 Name Dev
5 DOB 06/06/xx
6 Amount 0000
Just one question. I added few rows in your table. Does it match with your actual data? I mean can this table have multiple records like I showed above?
December 9, 2011 at 6:32 am
We need more details about your data model.
How are the rows connect to each other?
December 9, 2011 at 6:32 am
I don't like that there is no ID to show where 1 record ends and another begins, but try this
BEGIN TRAN
SELECT Id, FieldName, FieldValue
INTO #temp
FROM (SELECT 1, 'Name', 'John Lucas'
UNION ALL SELECT 2, 'DOB', '12/11/1990'
UNION ALL SELECT 3, 'Amount', '5000') a(Id, FieldName, FieldValue)
SELECT Name, DOB, Amount
FROM (SELECT FieldName, FieldValue
FROM #temp) DataTable
PIVOT (MAX(FieldValue) FOR FieldName IN (Name,DOB,Amount) ) AS p
ROLLBACK
December 9, 2011 at 6:36 am
This is vertically data storage
I can not put the hard code fieldname bcz user can add more field name in it.
December 9, 2011 at 6:38 am
prashant-507002 (12/9/2011)
This is vertically data storageI can not put the hard code fieldname bcz user can add more field name in it.
You answered my question indirectly. Is there any good reason for doing it?
December 9, 2011 at 6:40 am
Yes
Bcz we don't want to restrict user to entere fixed column data.
The application requirement is user can add custom column and its value while processing documents.
December 9, 2011 at 6:44 am
But you should use a unique key for each user input like:
UID ID Fieldname Fieldvalue
1 1 Name John
1 2 DOB 12/11/1990
1 3 Amount 5000
So you could select and group the rows by UID
December 9, 2011 at 6:46 am
can you provide me the query?
December 9, 2011 at 6:53 am
prashant-507002 (12/9/2011)
This is vertically data storageI can not put the hard code fieldname bcz user can add more field name in it.
BEGIN TRAN
SELECT Id, FieldName, FieldValue, RecordID
INTO #temp
FROM (SELECT 1, 'Name', 'John Lucas', 1
UNION ALL SELECT 2, 'DOB', '12/11/1990', 1
UNION ALL SELECT 3, 'Amount', '5000', 1
UNION ALL SELECT 4, 'Name', 'Some One', 2
UNION ALL SELECT 5, 'DOB', '12/11/1990', 2
UNION ALL SELECT 6, 'Species', 'Elephant!', 2) a(Id, FieldName, FieldValue, RecordID)
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL, '') + 'MAX(CASE WHEN FieldName = ''' + FieldName + ''' THEN FieldValue ELSE NULL END) AS ''' + FieldName + ''','
FROM #temp
GROUP BY FieldName
SET @sql = 'SELECT ' + SUBSTRING(@SQL,1,LEN(@SQL)-1) + ' FROM #temp GROUP BY RecordID'
EXEC(@SQL)
ROLLBACK
Results in
Amount DOB Name Species
---------- ---------- ---------- ----------
5000 12/11/1990 John Lucas NULL
NULL 12/11/1990 Some One Elephant!
December 9, 2011 at 6:54 am
Please try to understand As per your query i have to modify query each when user added new fieldName
December 9, 2011 at 6:57 am
prashant-507002 (12/9/2011)
Please try to understand As per your query i have to modify query each when user added new fieldName
No you don't.
Read what I wrote again, it uses dynamic SQL to get all of the fieldName's from the table then builds your query string from that. Once the string is built, it executes the string, so no matter how many new fieldName's are added to the table it will always work.
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL, '') + 'MAX(CASE WHEN FieldName = ''' + FieldName + ''' THEN FieldValue ELSE NULL END) AS ''' + FieldName + ''','
FROM #temp
GROUP BY FieldName
SET @sql = 'SELECT ' + SUBSTRING(@SQL,1,LEN(@SQL)-1) + ' FROM #temp GROUP BY RecordID'
EXEC(@SQL)
December 9, 2011 at 7:15 am
GO
/****** Object: Table [dbo].[ProcessDocumentLog] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProcessDocumentLog](
[strDocumentLogId] [varchar](255) NOT NULL,
[strLogId] [varchar](255) NOT NULL,
[strDocTypeName] [varchar](255) NULL,
[strFieldCaptionName] [varchar](255) NULL,
[strFieldValue] [nvarchar](max) NULL,
[dateTimeCreated] [smalldatetime] NULL,
[dateTimeLastModified] [datetime] NULL,
CONSTRAINT [PK_ProcessDocumentLog] PRIMARY KEY CLUSTERED
(
[strDocumentLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ProcessDocumentLog] ([strDocumentLogId], [strLogId], [strDocTypeName], [strFieldCaptionName], [strFieldValue], [dateTimeCreated], [dateTimeLastModified]) VALUES (N'2011110818110627621787', N'201111081811061361', N'Prepaid Mobile', N'Name', N'jodeph', CAST(0x9F950443 AS SmallDateTime), CAST(0x00009F95012BAEA8 AS DateTime))
INSERT [dbo].[ProcessDocumentLog] ([strDocumentLogId], [strLogId], [strDocTypeName], [strFieldCaptionName], [strFieldValue], [dateTimeCreated], [dateTimeLastModified]) VALUES (N'201111081811063237461', N'201111081811061361', N'Prepaid Mobile', N'City', N'New York', CAST(0x9F950443 AS SmallDateTime), CAST(0x00009F95012BAEB0 AS DateTime))
INSERT [dbo].[ProcessDocumentLog] ([strDocumentLogId], [strLogId], [strDocTypeName], [strFieldCaptionName], [strFieldValue], [dateTimeCreated], [dateTimeLastModified]) VALUES (N'2011110818112247921787', N'201111081811224321', N'Prepaid Mobile', N'Name', N'kevin', CAST(0x9F950443 AS SmallDateTime), CAST(0x00009F95012BC199 AS DateTime))
INSERT [dbo].[ProcessDocumentLog] ([strDocumentLogId], [strLogId], [strDocTypeName], [strFieldCaptionName], [strFieldValue], [dateTimeCreated], [dateTimeLastModified]) VALUES (N'201111081811224797461', N'201111081811224321', N'Prepaid Mobile', N'City', N'New York', CAST(0x9F950443 AS SmallDateTime), CAST(0x00009F95012BC199 AS DateTime))
/****** Object: Default [DF_ProcessDocumentLog_dateTimeCreated] Script Date: 12/09/2011 19:44:04 ******/
ALTER TABLE [dbo].[ProcessDocumentLog] ADD CONSTRAINT [DF_ProcessDocumentLog_dateTimeCreated] DEFAULT (getdate()) FOR [dateTimeCreated]
GO
/****** Object: Default [DF_ProcessDocumentLog_dateTimeLastModified] Script Date: 12/09/2011 19:44:04 ******/
ALTER TABLE [dbo].[ProcessDocumentLog] ADD CONSTRAINT [DF_ProcessDocumentLog_dateTimeLastModified] DEFAULT (getdate()) FOR [dateTimeLastModified]
GO
December 9, 2011 at 7:19 am
Again : -
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL, '') + 'MAX(CASE WHEN [strFieldCaptionName] = ''' + [strFieldCaptionName] + ''' THEN [strFieldValue] ELSE NULL END) AS ''' + [strFieldCaptionName] + ''','
FROM [ProcessDocumentLog]
GROUP BY [strFieldCaptionName]
SET @sql = 'SELECT ' + SUBSTRING(@SQL,1,LEN(@SQL)-1) + ' FROM [ProcessDocumentLog] GROUP BY [strLogId]'
EXEC(@SQL)
Results in
City Name
----------- ------------
New York jodeph
New York kevin
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply