Post reply

Increment Name Tag

  • July 6, 2012 at 12:57 pm

    #260893

    Hi Guys,

    Do you have an idea how to automatically increment the two colomun name "Sample_TDate_X" and "Sample_Value_X" of the code below ? (X from 1 to 36)

    Actually I can write the 36 code lignes, but I'm sure there is a "cleanest way" to do it.

    Thanks

    Julien

    SELECT Signal_Index, Sample_TDate_1, Sample_Value_1 from dbo.Trend_1

    UNION

    SELECT Signal_Index, Sample_TDate_2, Sample_Value_2 from dbo.Trend_1

    UNION

    SELECT Signal_Index, Sample_TDate_3, Sample_Value_3 from dbo.Trend_1

    ---

    --- And so on until 36

    ---

    UNION

    SELECT Signal_Index, Sample_TDate_36, Sample_Value_36 from dbo.Trend_1

  • July 6, 2012 at 1:00 pm

    #1509870

    I'm not sure I completely understand... but could you use the ROW_NUMBER() function?

    Jared
    CE - Microsoft

  • July 6, 2012 at 1:09 pm

    #1509873

    this will generate the statements so you can copy and paste them with just a little bit of cleanup:

    SELECT

    'SELECT Signal_Index, Sample_TDate_' + convert(varchar,N) + ', Sample_Value_' + convert(varchar,N) + ' from dbo.Trend_1 UNION'

    FROM (SELECT top 36

    N = row_number() over (order by name)

    FROM sys.columns)myAlias

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 6, 2012 at 1:22 pm

    #1509878

    Thanks Lowell! Actually it generate the statement very-well. I was looking for a code that directly give me the desired dataset, is there a way to execute a string directly from SQL ?

  • July 6, 2012 at 1:27 pm

    #1509879

    julien.dutel (7/6/2012)

    Thanks Lowell! Actually it generate the statement very-well. I was looking for a code that directly give me the desired dataset, is there a way to execute a string directly from SQL ?

    well you may notice there is a trailing UNION at teh end of what was generated. that needs to be cleaned up.

    the only way to do what you are asking is with dynamic SQL;

    DECLARE @cmd varchar(max)

    SET @cmd=''

    SELECT

    @cmd = @cmd + 'SELECT Signal_Index, Sample_TDate_' + convert(varchar,N) + ', Sample_Value_' + convert(varchar,N) + ' from dbo.Trend_1 UNION '

    FROM (SELECT top 36

    N = row_number() over (order by name)

    FROM sys.columns)myAlias

    --clean up the extra union

    SET @cmd = LEFT (@cmd,datalength(@cmd) - datalength('UNION '))

    print @cmd

    exec(@cmd)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 6, 2012 at 1:35 pm

    #1509882

    It seems so easy for you. Thank you, that's work perfectly !

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply