Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query logic question...


query logic question...

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44977 Visits: 39869
roy.tollison (11/16/2013)
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...


Heh... yeah. Let us know how the cursor thing works you for you. ;-)

Since folks still have questions about what you're actually trying to do, I recommend that you try to clarify the problem using the suggestions from the 1st link under "Helpful Links" in my signature line below. Obviously, you don't have to post the whole table or a million rows of data but there's nothing like cold hard data in a table to clarify a problem.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
roy.tollison
roy.tollison
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 194
CREATE TABLE [dbo].[nTable](
   [p_key] [int] NOT NULL,
   [mf_1] [char](5) NULL,
   [mf_2] [char](5) NULL,
   [mf_3] [char](5) NULL,
   [mf_4] [char](5) NULL,
   [mf_5] [char](5) NULL,
   [cf_1] [int] NULL,
   [cf_2] [int] NULL,
   [cf_3] [int] NULL,
   [cf_4] [int] NULL,
   [cf_5] [int] NULL,
CONSTRAINT [PK_nTable] PRIMARY KEY CLUSTERED
(
   [p_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



Sample Data...
p_key   mf_1   mf_2   mf_3   mf_4   mf_5   cf_1   cf_2   cf_3   cf_4   cf_5
1   AAA    BBB    CCC    DDD    EEE    5   5   5   5   5
2   BBB    DDD    BBB    BBB    BBB    5   5   5   5   5
3   EEE    DDD    CCC    BBB    AAA    5   5   5   5   5
4   FFF    HHH    III    AAA    NULL   4   4   4   4   NULL
5   ZZZ    NULL   NULL   NULL   NULL   1   NULL   NULL   NULL   NULL
6   GGG    BBB    EEE    AAA    NULL   4   4   4   4   NULL
7   MMM    EEE    AAA    NULL   NULL   3   3   3   NULL   NULL
8   NULL   NULL   NULL   NULL   NULL   0   0   0   0   0
9   RRR    TTT    DDD    BBB    CCC    5   5   5   5   5
10   BBB    CCC    AAA    NULL   NULL   3   3   3   NULL   NULL
11   CCC    AAA    NULL   BBB    NULL   3   3   NULL   3   NULL
12   WWW    UUU    AAA    CCC    EEE    5   5   5   5   5
13   SSS    AAA    DDD    EEE    NULL   4   4   4   4   NULL
14   EEE    FFF    BBB    AAA    NULL   4   4   4   4   NULL
15   TTT    NULL   NULL   NULL   NULL   1   NULL   NULL   NULL   NULL
16   YYY    AAA    NULL   NULL   NULL   2   2   NULL   NULL   NULL
17   GGG    XXX    BBB    NULL   NULL   3   3   3   NULL   NULL
18   BBB    AAA    CCC    EEE    DDD    5   5   5   5   5
19   PPP    BBB    TTT    JJJ    NULL   4   4   4   4   NULL
20   KKK    LLL    NULL   NULL   NULL   2   2   NULL   NULL   NULL


drop table pvtTable

select p_key, mf_value, mf_field, REPLACE(mf_field,'mf_','cf_') as cf_field into pvtTable from nTable
unpivot
(mf_value for mf_field in
(mf_1, mf_2, mf_3, mf_4, mf_5)) as pvtTable where mf_value in ('AAA','BBB')

declare getCSField cursor for select p_key, cf_field from pvtTable
open getCSField
declare @cmd varchar(max),
      @P_Key   int,
@CSField as varchar(100);
fetch next from getCSField into @P_Key, @CSField
while @@FETCH_STATUS = 0
begin
   begin try
    execute ('select ' + @P_Key + ' as p_key, ' + @CSField + ' from nTable where p_key = ' + @P_Key)
   end try
   begin catch
    select @P_Key, @CSField
   end catch
   fetch next from getCSField into @P_Key, @CSField;
end
CLOSE getCSField
DEALLOCATE getCSField


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search