SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Iterating thru a Tables columns


Iterating thru a Tables columns

Author
Message
richard.noordam
richard.noordam
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 442
I have a dataset that i need to iterate thru in a row wise manner where each row has columns that will either be filled with a value or zero.

I need to find the first occurance in each row(column) that a non-zero value occurs.

I can think of at least one way i can do this but it is very inelegant, and something a bit more clever would suit my vanity... :-D

ideas/examples?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26177 Visits: 17539
richard.noordam (11/21/2012)
I have a dataset that i need to iterate thru in a row wise manner where each row has columns that will either be filled with a value or zero.

I need to find the first occurance in each row(column) that a non-zero value occurs.

I can think of at least one way i can do this but it is very inelegant, and something a bit more clever would suit my vanity... :-D

ideas/examples?


ddl/sample data/desired output?

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
richard.noordam
richard.noordam
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 442

9671 9672 9673 9674 9781 9782 9783 9784 9891 9892 9893 9894 9901 9902
0 9672 9673 0 0 0 0 9784 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 9894 9901 0


First row is the header.

each row has an identifier as well, but as i go thru the rows, what i should get the first occurance only.

the columns are varchar(4)

ie.
row 1: 9672
row 2: 9894

sry should of left data example.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4017 Visits: 5828
Not sure how it'll perform (and could depend on your definition of "first"), but:

SELECT COALESCE(
NULLIF([9671], 0),
NULLIF([9672], 0),
NULLIF([9673], 0),
NULLIF([9674], 0),
NULLIF([9781], 0),
NULLIF([9782], 0),
NULLIF([9783], 0),
NULLIF([9784], 0),
NULLIF([9891], 0),
NULLIF([9892], 0),
NULLIF([9893], 0),
NULLIF([9894], 0),
NULLIF([9901], 0),
NULLIF([9902], 0)
)
FROM tbl


richard.noordam
richard.noordam
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 442
I should also add that the column list changes and gets added to on a quarterly basis (as defined by the institution i work at) and the data set i show is only a small set of the column list.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4017 Visits: 5828
And if the 0's can be NULLs in the table instead, you can get rid of all the NULLIF's.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4017 Visits: 5828
richard.noordam (11/21/2012)
I should also add that the column list changes and gets added to on a quarterly basis (as defined by the institution i work at)


Can these changes/additions be codified? You'll almost certainly end up having to modify the query otherwise.


the data set i show is only a small set of the column list.


Not too difficult to produce a select list containing all columns that you can then modify.

Cheers
Gaz
richard.noordam
richard.noordam
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 442
well this is what was done to accomplish it. inelegant as it seems to me.

1. generates the SQL YearQuarter list.
declare YRQList cursor for 
SELECT DISTINCT YearQuarterID from CCSGen.dbo.vwODSSM_YearQuarter where YearQuarterID between @YRQSTART and @YRQMax and firstClassDay < GETDATE() and YearQuarterID >= @YRQStart


2. runs thru a series of updates for each column in order.
DYN2 = fixing nulls to 0's
DYN3 = where they are NOT null put a 1
DYN4 = because of the progression i can update the YRQStart field if there is a 1 in the current YRQ column and YRQSTART is null. (being first occurrance)
OPEN YRQList
FETCH NEXT FROM YRQList
INTO @YRQ
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DYN2 as nvarchar(4000)
DECLARE @DYN3 as nvarchar(4000)
DECLARE @DYN4 as nvarchar(4000)
SET @DYN2 = 'UPDATE [CCSIRDev].[dbo].[CCSRetention] SET [' + @YRQ + ']=''0'' WHERE [' + @YRQ + '] IS NULL'
SET @DYN3 = 'UPDATE [CCSIRDev].[dbo].[CCSRetention] SET [' + @YRQ + ']=1 WHERE [' + @YRQ + '] <> ''0'''
SET @DYN4 = 'UPDATE CCSRetention SET YRQStart = ''' + @YRQ + ''' WHERE [' + @YRQ + '] = 1 and YRQStart IS NULL'
PRINT '#1:' + @DYN2
EXEC sp_executesql @DYN2
PRINT '#2:' + @DYN3
EXEC sp_executesql @DYN3
PRINT '#3:' + @DYN4
EXEC sp_executesql @DYN4
print 'Column Update Complete ' + @YRQ
--PRINT @DYN2
FETCH NEXT FROM YRQList INTO @YRQ
END
CLOSE YRQList
DEALLOCATE YRQList



now there is probably a few ways that this could be solved... maybe better? this most definitely works though.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26177 Visits: 17539

now there is probably a few ways that this could be solved... maybe better? this most definitely works though.


Agreed there are better ways to do this. I doubt you need a **cough**cursor**cursor** for this. But without anything to work with there is nothing anybody can do to help. You have certainly been around here long enough to know the format of what is expected when posting questions. If you are unsure, please read the article at the first link in my signature.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
richard.noordam
richard.noordam
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 442
first of all i was NOT looking for a specific solution. So your comment, and snarkyness are not appreciated, nor warranted.

I was indeed looking for a pattern of behavior to apply, to a problem i have, which this problem is a very minor piece of the whole problem that i'm trying to solve.

I was already using a coalesce statement with a select to generate the column list, and the solution wasn't to put the entire set of code here, and ask that it be tore apart. (FYI: another part is a crosstab using said column list, but that is outside of what i'm asking, which works awesome btw). Mainly because the problem that I asked about here can be stated simply.

What is the best way to deal with a set of rows and determine/return the first occurance of a non-zero column value in each row, using a set of column fields that are determined at runtime?

There should be a 'best-practice' on this, i would think, maybe not.
in any case.... there should be a pattern of behavior in assessing and attempting to solve, that doesn't rely on specific data.

So datasets were 'extra' and unnecessary, even though I provided in the second post.

Can anyone else help?
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