April 27, 2009 at 5:18 pm
I have 2 tables as follows.
TableName:InputFields Columns: InputFieldsID, InputFieldName
TableName:InputValue Columns: InputValueID, InputFieldsID, InputFieldValue, RowValue
The InputFields table contains dynamic information about the customer. For e.g., Name, Claim#, Claim Date, Email etc...
The InputValue table contains the actual value for InputFields.
For e.g.,
Table: InputFields
InputFieldsID InputFieldName
-----------------------------------
1 Name
2 Claim #
3 Email
Table: InputFields
InputValueID InputFieldsID InputFieldValue RowValue
---------------------------------------------------------------
1 1 Joseph 1
2 2 A-123 1
3 3 a@a.com 1
4 1 Mary 2
5 2 B-123 2
6 3 b@b.com 2
I need an output as follows:
Name Claim# Email
---------------------------------
Joseph A-123 a@a.com
Mary B-123 b@b.com
Or in short, I need to have the InputFieldName as the header (alias) and InputFieldValue as the values. InputFieldsID is the foreign key and RowValue tells that this is the first record. Again both the InputFieldName and InputFieldValue cannot be predicted in advance. User may input any values.
Appreciate if anyone could help!!!!!
April 28, 2009 at 3:00 am
inputfieldid is limited to three otr it can be more then 3 also.
April 28, 2009 at 7:07 am
Check out this article[/url] and the follow-up[/url] on Pivoting. I believe the first article will address your situation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 9:22 am
First of all answer to Mithun's question is NO. The InputFields values are dynamically entered by the user and its real values are stored in the InputValues table.
Jack, I had gone through that Pivoting section which you sent. In fact, it talks about the Pivoting based on the values or fields which are existing. The queries mentioned there will get you the results. In my case, it is a bit different. For me the header/field names itself are stored in the InputFields table and thier values in InputFields. The values stored in the InputFields should come as the header and based on the RowNumber the values stored in the InputValues table should show us the result.
April 28, 2009 at 9:35 am
try this
CREATE TABLE InputFields
(
InputFieldsID INT,
InputFieldName VARCHAR(100)
)
-----------------------------------
INSERT INTO InputFields
SELECT 1 ,'Name' UNION ALL
SELECT 2, 'Claim #' UNION ALL
SELECT 3, 'Email'
CREATE TABLE InputValues
(
InputValueID INT,
InputFieldsID INT,
InputFieldValue VARCHAR(100),
RowValue INT
)
---------------------------------------------------------------
INSERT INTO InputValues
SELECT 1, 1, 'Joseph', 1 UNION ALL
SELECT 2, 2, 'A-123', 1 UNION ALL
SELECT 3, 3, 'a@a.com', 1 UNION ALL
SELECT 4, 1, 'Mary', 2 UNION ALL
SELECT 5, 2, 'B-123', 2 UNION ALL
SELECT 6, 3, 'b@b.com', 2
SELECT *
FROM InputFields f
INNER JOIN InputValues v ON f.InputFieldsID = v.InputFieldsID
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT InputFieldName
FROM InputFields
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT InputFieldName ,InputFieldValue,RowValue
FROM InputFields f
INNER JOIN InputValues v ON f.InputFieldsID = v.InputFieldsID
) t
PIVOT (MAX(InputFieldValue) FOR InputFieldName IN (' + @ColsList + ')) PVT')
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 9:43 am
Hi Chris,
This is exactly what I wanted. Thank you so much. You are the best........
Thanks,
Sharmin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply