Dynamic Pivot Columns for Changing column starting points

Question

Dynamic Pivot Columns for Changing column starting points

Viewing 6 posts - 16 through 20 (of 20 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Glad I could help 😀

If you have any further questions regarding the dynamic solution, let me know. But give it a try first. If the code goes into production, you'll need to maintain it, hence you need to understand the concept. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

flam SSC Journeyman Points: 79 More actions · Answer 2

Yes, will do. I generally break the code down to all the subcomponents so I can see what's going on before implementing, so I'm definitely going to make sure I understand.

Thanks again

LutzM SSC Guru Points: 107049 More actions · Answer 3

Here's the "middle part" that actually needs to be dynamic:

SELECT

'MAX(CASE WHEN pos='

+ CAST(ROW_NUMBER() OVER(partition by periodid, routedirid ORDER BY sequence) AS VARCHAR(10))

+ ' THEN CONVERT(CHAR(5),tt.[time],114) ELSE '' --- '' END) AS ['

+ stpabbr

+'],'

FROM routestop rs

INNER JOIN stp ON rs.stpid = stp.stpid

WHERE periodid = 44 AND routedirid =7750

This will generate the MAX(CASE) statements and assign the related column name.

But you'll need to make sure that the code that generates the dynamic SQL will use the same parameter for periodid and routedirid as the finaly SQL statement will use. Otherwise it'll be a mess... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

flam SSC Journeyman Points: 79 More actions · Answer 4

Thanks, I'll keep it in mind. Most of the reports that I run against this data typically starts with:

declare @periodid int

declare @dayofweekid in

declare @route varchar(10)

declare @pttrn char(2)

set....

So we should be good there. I have been reading your articles on crosstab and dynamic crosstab. I'm fairly new to pivot and very much prefer the case expression approach as well.

Very informative stuff!

LutzM SSC Guru Points: 107049 More actions · Answer 5

I'm glad you like the two articles.

But here's a disclaimer: I'm not the author. 😉

Those articles are written by Jeff Moden, so the credit belongs to him. 😎

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

flam SSC Journeyman Points: 79 More actions · Answer 6

Details details 🙂 Probably should have noticed that