Post reply

Looping through select statement

  • November 4, 2013 at 1:23 am

    #278893

    I have a select statement in which the total number of columns is dynamic.

    First 2 columns are static , and the rest would be dynamic.

    Consider the following query

    SELECT

    Newsequence,

    CASE WHEN Newsequence = 'Source' THEN Source ELSE '' END Source,

    MAX( CASE WHEN rn = 1 THEN Value ELSE '' END) Option1,

    MAX( CASE WHEN rn = 2 THEN Value ELSE '' END) Option2,

    MAX( CASE WHEN rn = 3 THEN Value ELSE '' END) Option3

    FROM CTE

    CROSS APPLY (VALUES('Source', CAST( Target AS nvarchar(100))),

    ('Property1', CAST( Property1 AS nvarchar(100))),

    ('Property2', CAST( Property2 AS nvarchar(100))),

    ('Property3', CAST( Property3 AS nvarchar(100))),

    ('Property4', CAST( Property4 AS nvarchar(100))),

    ('Property5', CAST( Property5 AS nvarchar(100))),

    ('Property6', CAST( Property6 AS nvarchar(100)))

    )x(Newsequence, Value)

    GROUP BY Newsequence,

    Source

    ORDER BY CTE.Source,

    CASE WHEN Newsequence = 'Source' THEN 'a' ELSE Newsequence END;

    In the bold statement above, i want to replace the hardcode 1,2,3 with @cnt number of times. I have been trying this. I tried it with CTE, Subquery and While loop but to no avail. Does anybody know how could be this done.

  • November 4, 2013 at 8:13 am

    #1663841

    Complete story at http://www.sqlservercentral.com/Forums/Topic1510608-392-1.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

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