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»»

Iterating thru a Tables columns Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 9:20 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:54 PM
Points: 727, Visits: 425
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...

ideas/examples?
Post #1387479
Posted Wednesday, November 21, 2012 9:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
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...

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 Moden's 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)
Post #1387483
Posted Wednesday, November 21, 2012 9:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:54 PM
Points: 727, Visits: 425
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.
Post #1387489
Posted Wednesday, November 21, 2012 9:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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

Post #1387500
Posted Wednesday, November 21, 2012 9:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:54 PM
Points: 727, Visits: 425
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.
Post #1387501
Posted Wednesday, November 21, 2012 9:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
And if the 0's can be NULLs in the table instead, you can get rid of all the NULLIF's.
Post #1387502
Posted Wednesday, November 21, 2012 10:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1387505
Posted Tuesday, November 27, 2012 9:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:54 PM
Points: 727, Visits: 425
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.
Post #1389278
Posted Tuesday, November 27, 2012 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062

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 Moden's 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)
Post #1389307
Posted Tuesday, November 27, 2012 11:11 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:54 PM
Points: 727, Visits: 425
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?
Post #1389326
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse