Need a way to write clever query with dynami columns

Question

Need a way to write clever query with dynami columns

Misha_SQL

SSCertifiable

Points: 5407
More actions
April 19, 2011 at 2:38 pm

#235022

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),
CustomerIDint,
CommentSeq smallint,
EnteredByvarchar(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_1EnteredBy_2 EnteredDate_2 CommentText_2 EnteredBy_3 EnteredDate_3CommentText_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.

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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

WayneS SSC Guru Points: 95409 More actions · Answer 2

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

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 3

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