Iterating thru a Tables columns

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 28, 2012 at 1:35 pm

  #1563426

  richard.noordam (11/28/2012)

  ---

  i do have to say, you should give yourself a bit more credit, as our private message indicated you did understand my problem, and did suggest a potential solution, which did indeed change the approach i was using for that section of the problem....

  to a 75% decrease in run time as well.

  and it's beginning to apease my need of a bit more elegant solution. 😉

  THANKS!!!

  Thanks Richard. It still uses a cursor but it is better at least. As I said in our PM the problem in helping come up with a generic solution is the language required to explain it is sql. And since we didn't have ddl to work with I couldn't write said sql. It would be really cool of you to post the solution I sent you (I didn't save a copy of it myself). That way anybody in the future who stumbles on this thread will be able to see a way to solve it. 😀

  _______________________________________________________________

  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/

• WILLIAM MITCHELL

  SSChampion

  Points: 13766

  More actions

  December 6, 2012 at 5:37 am

  #1566118

  Here is an example of a possible method you could use:

  -- set up sample table with data

  IF OBJECT_ID('dbo.my_table') IS NOT NULL

  DROP TABLE dbo.my_table

  GO

  CREATE TABLE dbo.my_table

  (

  ID int identity(1,1) NOT NULL,

  F1 varchar(4),

  F2 varchar(4),

  F3 varchar(4),

  F4 varchar(4),

  F5 varchar(4)

  )

  SET NOCOUNT ON

  INSERT my_table (F1,F2,F3,F4,F5) SELECT '0','0','9673','0','9781'

  INSERT my_table (F1,F2,F3,F4,F5) SELECT '0','9672','9673','0','0'

  INSERT my_table (F1,F2,F3,F4,F5) SELECT '0','9672','9673','9674','0'

  INSERT my_table (F1,F2,F3,F4,F5) SELECT '0','0','0','9674','9781'

  SELECT * FROM my_table

  -----------------------------

  -- build up sql statement & execute

  DECLARE

  @my_columns nvarchar(MAX),

  @sql nvarchar(MAX)

  SELECT @my_columns =

  COALESCE(LTRIM(RTRIM(@my_columns)) + ',' ,'') + 'NULLIF([' + c.name + '],''0'')'

  FROM

  sys.tables t

  INNER JOIN sys.columns c

  ON t.object_id = c.object_id

  INNER JOIN sys.types st

  ON c.system_type_id = st.system_type_id

  WHERE

  t.name = 'my_table'

  ANDst.name = 'varchar'

  ANDc.max_length = 4

  ANDc.column_id >= 2

  ORDER BY

  c.column_id

  SELECT @my_columns = 'COALESCE(' + @my_columns + ')'

  SET @sql = 'SELECT ID, ' + @my_columns + ' AS my_value FROM my_table'

  PRINT @sql

  EXEC sp_executesql @sql