Need a way to write clever query with dynami columns

  • Hello,

    I am facing a challenge of writing a query with dynamic columns. Here is the setup script:

    CREATE TABLE #Comments (

    CommentID int IDENTITY(1,1),

    CustomerID int,

    CommentSeq smallint,

    EnteredBy varchar(16),

    EnteredDate smalldatetime,

    CommentText varchar(256)

    )

    INSERT INTO #Comments (CustomerID, CommentSeq, EnteredBy, EnteredDate, CommentText)

    VALUES

    (1, 1, 'Joe Shmoe', GETDATE(), 'Comment 1 for Cust 1'),

    (1, 2, 'John Smith', GETDATE(), 'Comment 2 for Cust 1'),

    (1, 3, 'Bob Johns', GETDATE(), 'Comment 3 for Cust 1'),

    (2, 1, 'Joe Shmoe', GETDATE(), 'Comment 1 for Cust 2'),

    (2, 2, 'John Smith', GETDATE(), 'Comment 2 for Cust 2'),

    (3, 1, 'Joe Shmoe', GETDATE(), 'Comment 1 for Cust 3'),

    (4, 1, 'Joe Shmoe', GETDATE(), 'Comment 1 for Cust 4'),

    (4, 2, 'John Smith', GETDATE(), 'Comment 2 for Cust 4'),

    (4, 3, 'Bob Johns', GETDATE(), 'Comment 3 for Cust 4'),

    (4, 4, 'Jim Slim', GETDATE(), 'Comment 4 for Cust 4')

    Now I need to write a query, which results in the output with columns similar to the following:

    CustomerID EnteredBy_1 EnteredDate_1 CommentText_1 EnteredBy_2 EnteredDate_2 CommentText_2 EnteredBy_3 EnteredDate_3 CommentText_3, etc....

    I hope this is clear. Bascially I want to keep it one record per customer, but as more comments are added, the number of columns will expand. In the example above, since we have maximum of 4 comments per customer the output will go up to: EnteredBy_4 EnteredDate_4 CommentText_4. Obviously for all the customers with less then 4 comments, these fields will be NULLs.

    Is there a way to do this dymically?

    Thank you!

    P.S. Please don't ask why I have to do this :unsure:, I would much prefer more normalized output, but at this point I am stuck with this requirement.

  • mishaluba (4/19/2011)


    P.S. Please don't ask why I have to do this :unsure:, I would much prefer more normalized output, but at this point I am stuck with this requirement.

    I hate those...

    This can be done dynamically, yes. Do a google for 'dynamic pivot', that's pretty much what you need here. Then just rename the result columns on their outbound.

    This seemed like a good place to start as I breezed through it:

    http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Look at the two links in my signature dealing with Cross-Tabs and Pivot tables. Part 2 covers dynamic pivoting.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank you both Craig and Wayne! This points me in the right direction.

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

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