Can you convert table from verticfal to horizontal using SQL?

[PruSQLer]

Here's the situation:

ID1 Event1
ID1 Event2
ID1 Event3
ID2 Event1
ID2 Event2

I want to convert the table to this:

ID1 Event1 Event2 Event3
ID2 Event1 Event2

I know I can pull the table in to my PC and write a VB app to do this but I'm dealing with huge tables so I'd like to do it via SQL. Is this possible?

Thanks, Iggy

 

[grega]

Have a read through previous posts on this board. The general consensus seems to be that's it isn't possible with raw SQL.

Greg.

 

[tlbroadbent]

It is possible with SQL. If you have a limited number of events and the events are known in advance, you can do the following.

Select
ID,
Max(Case When Event='Event1'
Then Event Else Null End) As Event1,
Max(Case When Event='Event2'
Then Event Else Null End) As Event2,
Max(Case When Event='Event3'
Then Event Else Null End) As Event3,
Max(Case When Event='Event4'
Then Event Else Null End) As Event4
From table
Group By ID

For more info see...

http://support.microsoft.com/support/kb/articles/q175/5/74.asp

There are other ways to accomplish this with dynamic SQL. Some SQL Sever T-SQL solutions are available at

http://www.swynk.com/sqlscripts/misc7.asp

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

A Synase solution is offered at...

http://www.isug.com/Sybase_FAQ/ASE/section6.2.html

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[PruSQLer]

Thanks Terry. I managed to do it a similar way using
"Sum(Case when event = 1 then event else null)"
and
"Group by ID"

But now I have 35 columns with mostly nulls. I may be pushing my luck here but do you know a way to reduce the table to containing only columns with values? The max number of events per ID is probably 6.

Thanks again. Iggy

 

[rac2]

Not to detract from the creative methods offered by Mr. Broadbent but I think the purpose of SQL is to store and retrieve data from relational databases; other languages and applications are intended for the presentation of data. So it may be that VB plus SQl is the appropriate way to meet your requirements.

 

[tlbroadbent]

Rac2,

While I agree that the main purpose of SQL is to store, manipulate and retrieve data, it is a rich language with many possibilities. There are reasons for performing functions like this at the server rather than at the client. Among those reasons are reduction of network traffic and increased speed of processing sets of data on the server.

There are also valid reasons to perform some manipulations in a client application. A developer needs to analyze the possibilities and determine the best methods for the application.

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

Iggy,

Here is a solution that works in SQL Server. It involves a couple of sub-queries. It will not be very efficient if you have a very large table. In my test, it took about 11 seconds to process a table of 60,000 records and about 26 seconds to process 150,000 records.

Select
ID,
Max(Case When EventNo=1 Then Event Else '' End) As Event1,
Max(Case When EventNo=2 Then Event Else '' End) As Event2,
Max(Case When EventNo=3 Then Event Else '' End) As Event3,
Max(Case When EventNo=4 Then Event Else '' End) As Event4,
Max(Case When EventNo=5 Then Event Else '' End) As Event5,
Max(Case When EventNo=6 Then Event Else '' End) As Event6
From
(Select
Id,
Event,
EventNo=(Select count(*)
From EventsTable
Where Id = a.ID
And Event<=a.Event)
From EventsTable As a) As q
Group By ID
Order By 1 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.