Loop to obtain data using SELECT statement

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Loop to obtain data using SELECT statement

Author

Topic

richardwaugh
Starting Member

36 Posts

Posted - 2014-08-21 : 08:45:41

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
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2014-08-21 : 11:27:01

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,'')
;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources