Changing row format

Question

Changing row format

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Heh... you still used dynamic sql... not so bad as RBAR, but post the spreadsheet you're trying to resolove... lemme take a whack at it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 2

Thanks man.

Really simple, I work mostly with financial clients who want to analyse all kinds of things the whole time.

The problem with most of the guys is that they know too well how to use a pivot, so normally when you put something down on the SQL supper table, they expect the pivot kind of functionality.

So basically in SQL 2000, and for the first couple of months of SQL 2005 all I knew was how to write case statements.

I posted a really simple example file, lets say you have a person, and total sales done in an area, and the value. But the person can move (really simple example, you dont wanna know the examples that these FI buffs can come up with :hehe:

For a case statement, I would have done it as following:

Select person,

sum(case when ranking group = 'Grasshopper' then TotalPosts else 0 end) Grasshopper,

sum(case when ranking group = 'Newbie' then TotalPosts else 0 end) Newbie,

sum(case when ranking group = 'Enthusiast' then TotalPosts else 0 end) Enthusiast,

sum(case when ranking group = 'Dedicated' then TotalPosts else 0 end) Dedicated,

sum(case when ranking group = 'Mr or Mrs 500' then TotalPosts else 0 end) [Mr or Mrs 500],

sum(case when ranking group = 'Babe Ruth' then TotalPosts else 0 end) [Babe Ruth],

sum(case when ranking group = 'SQL Master Guru' then TotalPosts else 0 end) [SQL Master Guru],

sum(case when ranking group = 'SQL Textbook' then TotalPosts else 0 end) [SQL Textbook]

from SSCCountsExample

group by person

Now, I would most probably have set up some control table, and written a cursor to build a dynamic SQL statement because I seriously dont want to be typing for a living, and copying and pasting has never been a dream job.

That was until the introduction of Pivots took away a lot of the grunt work required.

However, the pivot examples provided would still tell you to reference the fields as 'Newbie','Enthusiast' etc....

The dynamic pivot statement sorta cures most of the problem. But if there is an even better way that you can recommend I will owe you about 1000 points on my fridge

~PD

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 3

Dynamic SQL Pivot equivalent for the case statements would look something like so....

declare @colList varchar(max)

declare @selList varchar(max)

select @colList = COALESCE(@colList + ',', '') + RankingGroup

from (

select distinct '[' + RankingGroup + ']' as RankingGroup from dbo.SSCCountsExample

) ColList

select @selList = COALESCE(@selList + ',', '') + RankingGroup

from (

select distinct 'isnull([' + RankingGroup + '], 0) as [' + RankingGroup + ']' as RankingGroup from dbo.SSCCountsExample

) as SelList

exec (

'select piv.* from (

SELECT p.person, ' + @sellist + ' FROM dbo.SSCCountsExample

PIVOT

(

SUM(TotalPoints)

FOR RankingGroup

IN ('+ @colList +')

) p

) piv

'

)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

My turn to appologize... I though you were talking about unpivoting. What you did with the pivot is just fine. You can also do something similar with dynamic case statements in SQL Server 2000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 5

Thanks man,

I seriously get to write a lot of case statements for different reasons, such as 1/0 kind of fields, so I am always looking out for different more intelligent (and less coding) kind of ways. Anything to drive down that maintenance and make it easier to explain to other developers.

Out of interest sake, if I was to supply the Pivoted data, and it had to be unpivotted with dynamic SQL, how would I go about doing that?

Phil

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Who does the pivoted data start out? In a spreadsheet or in a table?

If it's in a spreadsheet and you can't unpivot it there for some reason, then I'd save the spreadsheet in tab delimited format, import into into a "wide column", and split the column using the Tally table method in the following URL. See the section titled "One Final "Split" Trick with the Tally Table"...

http://www.sqlservercentral.com/articles/TSQL/62867/

That's the part I meant about no dynamic SQL.

If the data started out as a table, then a dynamic unpivot similar to what you wrote for a pivot would do the trick just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)