Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

query logic question... Expand / Collapse
Author
Message
Posted Friday, November 15, 2013 10:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
currently i have a table that has the following layout, exact details of table not needed.

table1
pkey,
mf_1 char(5),
mf_2 char(5),
mf_3 char(5),
mf_4 char(5),
mf_5 char(5),
mf_6 char(5),
...
mf_50 char(5);

the field mf is repeated from _1 thru _50, i know it is an efficient way to input/mnt that data but that is the way they did it.
now my problem is that the information stored in mf_# can and does vary by row.
rows
1,'ABC','XYZ','MNO',....
2,'XYZ','JKL','UVW','ABC',....
3,'RST',DEF','GHI',....

Currently i am testing everyone of the mf_# to find any rows that contain 'ABC',
IS there a better way to do that or am i stuck with checking everyone of the mf_# columns?
Post #1514796
Posted Friday, November 15, 2013 10:29 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Other than Full-Text Indexes (maybe, not sure), there is no way to do this: you are stuck.

And this is only one of the reasons not to make non-relational unnormalized tables like this.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1514797
Posted Friday, November 15, 2013 10:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
i agree with that my suggestion was to change the table into 2 tables master/detail but had no say in the final outcome. so i am stuck with using the long query...
Post #1514800
Posted Friday, November 15, 2013 1:02 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 3:19 PM
Points: 157, Visits: 608
Would UNPIVOT possibly be helpful in this type of scenario?


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1514835
Posted Friday, November 15, 2013 1:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
Not sure how to use UNPIVOT but i will do some checking to see if it will do the job.
Thanks...
Post #1514839
Posted Friday, November 15, 2013 1:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 3:19 PM
Points: 157, Visits: 608
Yeah, honestly I'm not clear on the specifics of it either. But conceptually I understand what it does, and this seems to fit the bill AFAIK... I hope you'll report back!


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1514841
Posted Friday, November 15, 2013 2:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
OK I can use the UNPIVOT to get me the information of the mf_# that contains a matching value.
select pkey, mf_value, mf_field from table1
unpivot
(mf_value for mf_field in (mf_1, mf_2, mf_3,..., mf_50)) as pvtTable where mf_value = 'ABC'
Now my returned rows are...
1, 'ABC',mf_1
2,'ABC',mf_4
and this is good, how would i go about using mf_field value mf_1 and mf_4 to reference a corresponding field in another table.
i would like get the values for cf_1 and cf_4 to go along with the results above...
Table2
pkey,
cf_1 int,
cf_2 int,
cf_3 int,
cf_4 int,
...
cf_50;
Post #1514863
Posted Friday, November 15, 2013 9:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 35,772, Visits: 32,443
roy.tollison (11/15/2013)
OK I can use the UNPIVOT to get me the information of the mf_# that contains a matching value.
select pkey, mf_value, mf_field from table1
unpivot
(mf_value for mf_field in (mf_1, mf_2, mf_3,..., mf_50)) as pvtTable where mf_value = 'ABC'
Now my returned rows are...
1, 'ABC',mf_1
2,'ABC',mf_4
and this is good, how would i go about using mf_field value mf_1 and mf_4 to reference a corresponding field in another table.
i would like get the values for cf_1 and cf_4 to go along with the results above...
Table2
pkey,
cf_1 int,
cf_2 int,
cf_3 int,
cf_4 int,
...
cf_50;


If it's always cf_1 and cf_4, a normal inner join is all that's required.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1514888
Posted Friday, November 15, 2013 11:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
actually i am looking more for something like this
select pkey, mf_value, mf_field, REPLACE(mf_field,'mf_','cf_') as cf_field from table1, table2
unpivot
(mf_value for mf_field in (mf_1, mf_2, mf_3,..., mf_50)) as pvtTable where mf_value = 'ABC'

Yeah i know that isn't the way to do it but that is what i am looking for...
Post #1514890
Posted Saturday, November 16, 2013 1:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
i guess i could put the results into a table then use a cursor and process all the rows and use the value in the cf_field(real column name is stored in there) and then build an update command to load up the int value that is assigned to that column for that row.
will be a while before i get all the basics in place and all the bugs worked out...
Post #1514898
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse