SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop to obtain data using SELECT statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richardwaugh
Starting Member

36 Posts

Posted - 08/21/2014 :  08:45:41  Show Profile  Reply with Quote
Hello again. Here is an updated attempt to clarify the issue I am having. Our web application allows users to fill out documentation on forms that are created through HTML. There are a couple of add buttons on the form which complicate things because what the add button does is repeats the FieldName of the form but puts a combination of characters at the end.

What I need to do is create a query that returns the information in columns, rather than rows (in the case of someone clicking the add button, this should return two rows). Currently I have a UNION going on where I basically UNION the same query 5 times but change the FieldName to match the additional "Add" buttons.

You'll see what I mean by the following example.

Database Table:

CaseFormID     FieldName          FieldValue     CaseFormDataID
123456         Name               John           987654
123456         NameAdd_0          Fred           987656
123456         Relationship       Grandchild     987655
123456         RelationshipAdd_0  Nephew         987657
123456         CaseName           Alpha          987612
123456         CaseDate           2014-08-21     987613
123789         Name               Daisy          951753
123789         Relationship       Grandchild     951754
123789         CaseName           Beta           951755
123789         CaseDate           2014-08-21     951756
...


Result I am looking for:

CaseName     CaseDate     Name     Relationship
Alpha        2014-08-21   John     Grandchild
Alpha        2014-08-21   Fred     Nephew
Beta         2014-08-21   Daisy    Grandchild
...


Any suggestions? This query will be part of a very large query that contains every field that is contained in the form.

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 08/21/2014 :  11:27:01  Show Profile  Reply with Quote
Maybe something like this:



declare @meta table
(
	caseFormID int,
	fieldName varchar(30),
	fieldValue varchar(30),
	caseFormDataID int
)

insert @meta
select '123456', 'Name'              , 'John'       ,    987654 union all
select '123456', 'NameAdd_0'         , 'Fred'       ,    987656 union all
select '123456', 'Relationship'      , 'Grandchild' ,    987655 union all
select '123456', 'RelationshipAdd_0' , 'Nephew'     ,    987657 union all
select '123456', 'CaseName'          , 'Alpha'      ,    987612 union all
select '123456', 'CaseDate'          , '2014-08-21' ,    987613 union all
select '123789', 'Name'              , 'Daisy'      ,    951753 union all
select '123789', 'Relationship'      , 'Grandchild' ,    951754 union all
select '123789', 'CaseName'          , 'Beta'       ,    951755 union all
select '123789', 'CaseDate'          , '2014-08-21' ,    951756
;

--field mappings
declare @fieldRootName table (	fieldName varchar(30) not null primary Key)
insert @fieldRootName 
	select 'Name'  union all 
	select 'Relationship'
;

--result
select
	m1.caseFormID,
	m1.fieldValue as CaseName,
	m2.fieldValue as CaseDate,
	m3.fieldValue as Name,
	m4.fieldValue as Relationship
from @meta m1
	left join @meta m2 on m2.caseFormID = m1.caseFormID
	left join @meta m3 on m3.caseFormID = m1.caseFormID
	left join @meta m4 on m4.caseFormID = m1.caseFormID
	left join @fieldRootName rName on m3.fieldName LIKE rName.fieldName + '%'
	left join @fieldRootName rRelationship on m4.fieldName LIKE rRelationship.fieldName + '%'
where m1.fieldName = 'CaseName'
	and m2.fieldName = 'CaseDate'
	and m3.fieldName LIKE 'Name%'
	and m4.fieldName LIKE 'Relationship%'
	and REPLACE(m3.fieldName,rName.fieldName,'')  = REPLACE(m4.fieldName,rRelationship.fieldName,'')
;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000