Pivot - Multiple Rows into Colums (Unknown Number)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot - Multiple Rows into Colums (Unknown Number)

Author

Topic

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-15 : 09:23:28

Hi there Guys...

I'm New to this realm to programming i'm more a VB.Net bloak...

Espesially this complicated...

I have three tables - transactions, transaction fields, transaction Values...

they are linked with primary keys and setup fairly well...

I need to take all the information out of transaction and add columns by the Id field of transaction fields (as the column name) and finialy populate the data from transaction values.

More info:

tblTransaction:
transactionId (Primary Key)
trasnactionname

tblTransactionFields:
transactionFieldId (Primary Key)
transactionId ( Linked to tbltransaction.transactionid )

tblTransactionValues:
transactionValueId (Primary Key)
transactionValue
transactionField ( Linked to tbltransactionFields.transactionfieldsid )
transactionId ( this is repeted information i know but sure makes life easier...)

the outcome needs to be:
transactionId - trasnactionname - transactionFieldId(1) - transactionFieldId(2)...
with the values from the values table poulated under each heading.

Some of the incomming records have 3 values and others can have over 100...???

I'm Stumped i cant find help anywhere

Please Help...

Vincent Fradnsen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 09:26:53

http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Peter Larsson
Helsingborg, Sweden

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-15 : 09:51:13

Thank you Peso, What is your opinion on the speed of that compared to a pivot table command in 2005 as i need to process that probably every 30 seconds for 200 users, on what could have 50 000 transactions with up to 100 transaction fields per tranaction?

Vincent Fradnsen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 10:12:59

Even with SUM(CASE WHEN ... syntax, PIVOT is slower
AND you still have to hardwire the columns!

You still must use dynamic SQL to cope with this ~~bug~~ feature.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 11:51:27

FYI -- it is much easier, shorter, and quicker to cross tab data in .NET rather than in SQL Server.

see: http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx

Start with that post, and read the links it provides for background about what it is testing and demonstrating.

The performance difference is quite huge, it keeps your SQL code short and concise, and you are letting your presentation layer present the data, not SQL Server.

Peso -- relational databases are not designed to pivot data. A relational database requires fixed set of columns and tables to work with -- varying the # of columns and column names in a resultset means that you cannot do anything with those results in SQL! Why would you want SQL Server to allow you to produce results easily in this manner? What good does it do for the database layer to make this calculation?

Simply summarize the data in SQL and return standard results, and let your presentation layer pivot the data.

My link shows how to do this in .NET, it is even easier in a reporting tool like Reporting Services or Crystal, and even Excel and Access let you do this very easily (since Access blurs the line between a database and presentation tool).

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:56:23

I created that algorithm because of I tried to do it front-end first.
For 200 million record, I either got a time-out or memory problems in front-end.

So I decided to do it in back-end. I have paid the license for SQL Server. Why not let it do for me, what it is best at? Calculate data...

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 12:00:15

Peso -- you are missing the point. You summarize and calculate the data in SQL Server, absolutely. You present the data at your presentation layer. Cross-tabbing is presenting data in a particular format, not calculating.

I am not suggesting that you *summarize* your data at the presentation layer. I am suggesting that you let SQL do this, course, but just return the data in a standard row/column format with aggregate functions like any other SQL result. Then, let your presentation layer take those results and cross-tab it.

Does this make sense? My blog has an example (it is hard to read from post to post in the blog format, you kind of have to go backwards a bit and click lots of links) but if you'd like a clearer/better/more specific one, let me know.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 12:03:20

Yes, it makes sense.

But for my case, even when grouping data to a minimium, there were still 40K records to retreive and calculate, for each online user!
We had at the time some 400+ simultaneous users accesing the web page.

The IIS often hung and ceased to respond. Processor usage went through the roof! Memory was not enough and IIS started to swap to virtual memory, and so on, so on...

I think it is a matter of taste where you want to put the calculation power.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 12:28:14

it also matters how efficiently you can do the calculation in either place and what tools you have, of course. And, generally speaking, your client app is much more efficient when it comes to crosstabs. I doubt that your client code was very well written if it caused those kinds of issues in IIS.....

I plugged your stored procedure into my testing app to see how it performs compared to the other methods ... I am not so sure you want to see the results ...

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 12:30:43

I am not saying it is the fastest there is.
But it works with a minimum of CPU and memory, every time.
No matter which table names and columns name you have! I can't say that to most other dynamic crosstab solutions...

Peter Larsson
Helsingborg, Sweden

EDIT: Table names and Column names

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 12:34:07

Peso -- actually, it doesn't. If you really want to do it in T-SQL, use the stored proc from my blog ....

here's some testing results, you can do it yourself if you like, I can send you the code (it's all available on the blog as well) ...

---------------

Beginning performance test for crosstab techniques.

Please enter the # of iterations to perform:
50

Testing dynamic SQL crosstab procedure....
..................................................-- Result: 5 seconds.

Testing static SQL crosstab procedure....
..................................................-- Result: 4 seconds.

Testing C# crosstab transformation....
..................................................-- Result: 2 seconds.

Testing Peso pivot....
..................................................-- Result: 49 seconds.

Testing complete.

Press ENTER to quit.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 12:39:35

FYI -- the methods I tested against all work with any table or column names, they are completely dynamic as well.

- Jeff
http://weblogs.sqlteam.com/JeffS

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-16 : 02:27:52

Hey guys thanks this seems great i'll start thinking now i hope the web blog is rather hectic...

Vincent

Vincent Fradnsen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 03:36:17

Jeff, what am I doing wrong?
I am trying to understand your sproc, and did this test.


create table Samples (month_name varchar(12), weekday_name varchar(12), s int)

insert samples
select month_name_long, weekday_name_long, 1 from f_table_date('20000101', '20091231')

exec CrossTab2 'select month_name, weekday_name, s from samples', 'weekday_name', 'SUM(s ELSE 0)[veckodag]', 'month_name'

When I run this

exec CrossTab  'select month_name, weekday_name, s from samples', 'weekday_name', 'SUM(s ELSE 0)[]', 'month_name'

I only get results for wednesday.

Peter Larsson
Helsingborg, Sweden

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-16 : 03:37:24

ok so from my research i understand that this whole concept is a mess and complicated i'm battleing to comprehend the T-SQL language. however what about using my asp.net to create a seletion string by opening the recordset then "composing a standard 'pivot' Select" Dynamically created in asp.net i.e. i create all the sum fields in code and then execute that on the database.?

I'm sure it would work but is that a stupid way of doing this?

you'd create a string SELECT x x x then append the values of the rows in the 2nd table then use the pivot command... then run through the records again...to include the values and finally you can specify your criteria...

Vincent Fradnsen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:22:47

I figured it out!

I replaced

from 
    #Temp 
order by 
    Pivot

with

from ( select top 100 percent tpivot from #temp order by tpivot) t

to make the sproc work in both sql 2000 and sql 2005.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:24:40

New problem.
The crosstab2 sproc fails if I have a column with left paranthesis in it...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:30:38

Also I can't put

MAX(s ELSE ''(no data)'')[]

in Summaries parameter...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 05:53:49

Strange results...

I created a very simple test table like this

create table samples (month_name varchar(20), weekday_name varchar(20), s int)

insert into samples (month_name, weekday_name, s)
select month_name_long, weekday_name_long, 1
from f_table_date('19000101', '39991231')

select count(*) from samples
-- 767009 records

Then I run this code 102 times
exec CrossTab2 'select month_name, weekday_name, s from samples', 'weekday_name', 'SUM(s ELSE 0)[]', 'month_name'

The run time averaged about 3 seconds (not counting top 2 slowest values).

The I run this code 102 times
exec uspPesoSamples -- from http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

The run time averaged about 1 second (not counting top 2 slowest values).

Jeff, I do not know how you got 45+ seconds before for my code.
Maybe you did not use the pre-stored VIEW as you have for your own algorithms?

Peter Larsson
Helsingborg, Sweden

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-16 : 06:41:06

Ok so i know i'm a junior when it comes to SQL like this but this does exactly what i need it to do for me... i create a sting in VB and execute it on the DB...

Available Fields Info from my fields table loop through them to poulatethe string as such it also seems to be really Quick...???

SELECT
schBank.tblTransactionFields.TransactionId as transactionId,
Max( schBank.tbltransactions.Call ) Call,
Max( schBank.tblTransactions.CampaignId ) CampaignId,
Max( schBank.tblTransactions.UserId ) USerId,
Max( schBank.tblTransactions.WrapUpMessageId ) WrapUpMessageId,
Max( schBank.tblTransactions.WrapUpMessageNote ) WrapUpMessageNote,
Max( schBank.tblTransactions.SalesWrapUpId ) SalesWrapUpId,
Max( schBank.tblTransactions.SalesWrapUpNote ) SalesWrapUpNote,
Max( schBank.tblTransactions.FollowUpDateTime ) FollowUpDateTime,
Max( schBank.tblTransactions.StatusId ) StatusId,
Max( schBank.tblTransactions.CompletedDate ) CompletedDate,
MAX( CASE WHEN [fieldid] = 1 THEN [Value] END) [1],
MAX( CASE WHEN [fieldid] = 2 THEN [Value] END) [2],
MAX( CASE WHEN [fieldid] = 3 THEN [Value] END) [3],
MAX( CASE WHEN [fieldid] = 4 THEN [Value] END) [4],
MAX( CASE WHEN [fieldid] = 5 THEN [Value] END) [5]
FROM
schBank.tblTransactionFields INNER JOIN
schBank.tblTransactions
ON
schBank.tblTransactionFields.TransactionId =
schBank.tblTransactions.TransactionId
GROUP BY schBank.tblTransactionFields.TransactionId

What can i say it works 4 me.

Vincent Fradnsen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 06:44:18

Really?
If you have more than 1 record for a certain TransactionID, there is ABSOLULETY NO GURANTEE that

Max( schBank.tblTransactions.StatusId ) StatusId,
Max( schBank.tblTransactions.CompletedDate ) CompletedDate,

is from same record. First MAX may come from record 226484 and second MAX may come from record 422323, it they both have same TransactionID.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources