June 21, 2012 at 9:14 am
Hi,
I have the following table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QuestionFields](
[ID] [int] NULL,
[Field1] [varchar](200) NULL,
[FieldVal1] [varchar](1000) NULL,
[Field2] [varchar](200) NULL,
[FieldVal2] [varchar](1000) NULL,
[Field3] [varchar](200) NULL,
[FieldVal3] [varchar](1000) NULL,
[Field4] [varchar](200) NULL,
[FieldVal4] [varchar](1000) NULL,
[Field5] [varchar](200) NULL,
[FieldVal5] [varchar](1000) NULL,
[Field6] [varchar](200) NULL,
[FieldVal6] [varchar](1000) NULL,
[Field7] [varchar](200) NULL,
[FieldVal7] [varchar](1000) NULL,
[Field8] [varchar](200) NULL,
[FieldVal8] [varchar](1000) NULL,
[Field9] [varchar](200) NULL,
[FieldVal9] [varchar](1000) NULL,
[Field10] [varchar](200) NULL,
[FieldVal10] [varchar](1000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into QuestionFields values (1,'Company Name','tet','Organisation Type','Check 2','First Name','test','Last Name','test','Telephone','0','Mobile','','Address1','test','Villedd','test','Town/City','test','Region','')
insert into QuestionFields values (2,'Company Name','Test','Organisation Type','Secteur public','Last Name','Test','Last Name','User','Telephone','1','Mobile','','Address1','1','Villedd','2','Town/City','4','Region','')
insert into QuestionFields values (3,'Company Name','test crditit 1','Company Name','TPE et Professions Libérales (jusqsu’à 5fd0 employés)','Last Name','john','First Name','brggagg','Telephone','111','Mobile','','Town/City','222','Villedd','','Address1','pariss','Region','')
insert into QuestionFields values (4,'Company Name','windland','Company Name','PME/PMI (de 50 à 1000 employés)','Last Name','Yann','Last Name','Lebrdeton','Telephone','4221','Mobile','','Town/City','s','Villedd','','Address1','montrouge','Region','')
insert into QuestionFields values (5,'Company Name','steve own card 1','Company Name','Secteur pdubligggc','Last Name','steve own card 1','Last Name','steve own card 1','Telephone','12345','Mobile','','Town/City','steve own card 1','Villedd','','Town/City','steve own card 1','Region','')
insert into QuestionFields values (6,'First Name','ssss','Company Name','TPE et Professions Libérales (jusqfdu’à 5fd0 employés)','Last Name','ssss','Last Name','sss','Telephone','111','Mobile','','Town/City','dd','Villedd','','Town/City','ssss','Region','')
insert into QuestionFields values (7,'First Name','sd','Company Name','Partenaires','Last Name','BORIS','Last Name','BERdsTAZZON','Telephone','56212','Mobile','','Town/City','f','Villedd','','Town/City','LA POSTsdE','Region','')
insert into QuestionFields values (8,'First Name','steve test','Organisation Type','Secfdur gf','Last Name','steve test','Last Name','steve test','Telephone','0','Mobile','','Town/City','steve test','Villedd','','Telephone','stefve test','Region','')
insert into QuestionFields values (9,'First Name','test','Organisation Type','Secteur pubgdflic','Last Name','ben','Last Name','grimm','Telephone','2','Mobile','','Town/City','test','Villedd','test','Telephone','parisd','Region','')
insert into QuestionFields values (10,'Company Name','fs','Organisation Type','TPE et Professions Libérales (jusqu’à 5350 emddployés)','First Name','patrockda','Last Name','prian','Telephone','2','Mobile','','Address1','dee','Villedd','','Town/City','oxygedne','Region','')
I want a function created (dbo.fncCheck) that would do thje following:
Select ID, dbo.fncCheck('Company Name') as CompanyName
From [QuestionFields]
Where ID = 1 or ID = 5
Results
ID, CompanyName
1, tet
2 steve own card 1, Secteur pdubligggc
So as you can ID 1 is only found in Field1 and the Value for this from FieldVal1 is 'tet'
ID 2 has found 'Company Name' in Field1 and Field2 therefore I would want to show two results but seperated by a comma:
2 'steve own card 1, Secteur pdubligggc'
Hope this makes sense.
June 21, 2012 at 9:51 am
You have a couple of serious issues going on here. First this table structure is a type of EAV which is absolutely horrible to work with. This is even worse than a typical EAV type of structure because you have the entities in a denormalized table. If at all possible you should normalize this stuff instead. This type of structure is going to cause you countless sleepless nights and make you want to kill yourself.
Secondly you are asking for a scalar function. Scalar functions, especially ones that require this type of stuff are horrible for performance. I don't mean bad performance like it might be kind of slow. I mean the kind of performance where you have to tell your users to go get lunch while this thing executes.
Not trying to sound negative but a total redesign of your table structure will go much further than adding a scalar function kludge on top of this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2012 at 10:05 am
Hi, So what would your table design look like compared to the that i have? Thanks
June 21, 2012 at 10:11 am
Well I am not totally sure what this table holds. Is this answers to questionnaire or something along those lines?
I would create tables for Company, Organisation, Region, Contact (or person) based on what I see in your tables. Without some basis of context it is pretty hard to figure out what this could look like.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2012 at 12:57 pm
Hi, Basically these are custom fields that the user can add on the application side, they can add the same custom fields multiple times in any order, so the table source is in a unpivot format, i then pivot this table so its in the format as the table created (QuestionFields). Do you think you can do this scalar function then?
June 21, 2012 at 2:43 pm
Can you explain the rules behind why ID 1 returns 1 value and ID 5 would return 2?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2012 at 2:53 pm
Sean Lange (6/21/2012)
Can you explain the rules behind why ID 1 returns 1 value and ID 5 would return 2?
Because there are two columns with the value Company Name:
insert into QuestionFields values (5,'Company Name','steve own card 1','Company Name','Secteur pdubligggc','Last Name','steve own card 1','Last Name','steve own card 1','Telephone','12345','Mobile','','Town/City','steve own card 1','Villedd','','Town/City','steve own card 1','Region','')
June 21, 2012 at 3:03 pm
Lynn Pettis (6/21/2012)
Sean Lange (6/21/2012)
Can you explain the rules behind why ID 1 returns 1 value and ID 5 would return 2?Because there are two columns with the value Company Name:
insert into QuestionFields values (5,'Company Name','steve own card 1','Company Name','Secteur pdubligggc','Last Name','steve own card 1','Last Name','steve own card 1','Telephone','12345','Mobile','','Town/City','steve own card 1','Villedd','','Town/City','steve own card 1','Region','')
Ahh...I am still in disbelief of this data structure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2012 at 3:11 pm
At the very least you should normalize your EAV pairs. This is worst of the two worst data structures combined into a single table. This just makes we want to slit my wrists.
How about something like this to at least get the pairs normalized. Now we are only fighting against an EAV type of structure.
create table QuestionFieldsNormalized
(
RowID int identity primary key,
QFID int, --this is the ID from your original table
MappedField varchar(200),
StoredValue varchar(1000),
SortOrder smallint
)
insert QuestionFieldsNormalized
select * from
(
select ID, Field1, FieldVal1, 1 as SortOrder
from QuestionFields
union all
select ID, Field2, FieldVal2, 2
from QuestionFields
union all
select ID, Field3, FieldVal3, 3
from QuestionFields
union all
select ID, Field4, FieldVal4, 4
from QuestionFields
union all
select ID, Field5, FieldVal5, 5
from QuestionFields
union all
select ID, Field6, FieldVal6, 6
from QuestionFields
union all
select ID, Field7, FieldVal7, 7
from QuestionFields
union all
select ID, Field8, FieldVal8, 8
from QuestionFields
union all
select ID, Field9, FieldVal9, 9
from QuestionFields
union all
select ID, Field10, FieldVal10, 10
from QuestionFields
) Data
order by ID, SortOrder
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2012 at 3:20 pm
OK so once we have this somewhat normalized this type of data manipulation becomes a whole lot more manageable.
Something like this produces the desired results from your sample data.
declare @MappedField varchar(200) = 'Company Name'
select QFID,
STUFF((select ',' + StoredValue
FROM QuestionFieldsNormalized QFN2
where QFN1.QFID = QFN2.QFID
and QFN2.MappedField = @MappedField
order by QFN2.SortOrder
FOR XML PATH('')), 1, 1, ' ')
from QuestionFieldsNormalized QFN1
where QFID in (1, 5)
and MappedField = @MappedField
group by QFID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2012 at 7:33 pm
Here's another approach directly from the data you provided (normalization issues aside):
declare @MappedField varchar(200) = 'Company Name'
;WITH CTE AS (
SELECT ID, a, b
FROM QuestionFields q
CROSS APPLY (
VALUES (Field1, FieldVal1), (Field2, FieldVal2)
,(Field3, FieldVal3), (Field4, FieldVal4)
,(Field5, FieldVal5), (Field6, FieldVal6)
,(Field7, FieldVal7), (Field8, FieldVal8)
,(Field9, FieldVal9), (Field10, FieldVal10)) x(a,b)
WHERE a = @MappedField AND ID IN (1,5)
),
CTE2 AS (
SELECT ID, STUFF(
(SELECT ',' + b FROM CTE x WHERE x.ID = q.ID FOR XML PATH('')), 1, 1, '') AS b
FROM CTE q
)
SELECT ID, [Company Name]=MAX(b)
FROM CTE2
GROUP BY ID
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 22, 2012 at 2:56 am
Hi,
Thanks for your advice so far and what you have shown me.
The table you created QuestionFieldsNormalized was what the table originally looked like that when pulling the data from the Source Table, the reason I have it like the table "QuestionFields" is because when joing to another table it would only show one line and not multiple lines if I was to join using the QuestionFieldsNormalized table.
So I take it a function cannot be createde with the way I have done my new table structure?
June 22, 2012 at 3:21 am
SQL_Kills (6/22/2012)
So I take it a function cannot be createde with the way I have done my new table structure?
Not true! You could use my version in a FUNCTION. I was just too lazy to make it so.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 22, 2012 at 5:08 am
So did you create a similar function
June 22, 2012 at 7:15 am
SQL_Kills (6/22/2012)
Hi,Thanks for your advice so far and what you have shown me.
The table you created QuestionFieldsNormalized was what the table originally looked like that when pulling the data from the Source Table, the reason I have it like the table "QuestionFields" is because when joing to another table it would only show one line and not multiple lines if I was to join using the QuestionFieldsNormalized table.
So I take it a function cannot be createde with the way I have done my new table structure?
I would guess there was something else going on there preventing you from getting the data the way you wanted it. The real problem with the not normalized version is look closely at the query dwain wrote. If you need to add an 11th you have to modify the table and the function (and any other code you have pulling data from this). The way I put it together you can have any number of mapped fields and the code will work with no modification.
The code I wrote or the code Dwain wrote will both work in a function with a little modification. Why are you so determined to turn this into a scalar function? The queries that we both posted don't use a scalar function and both return the data you said you wanted in your first post.
I would HIGHLY recommend a more normalized route but you are the person who has to support this. In the end it is your system and your code you have to support, so the decision is yours.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply