Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
1632 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  
 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.04 seconds. Powered By: Snitz Forums 2000