Transposing a table - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Transposing a table

Author

Topic

TestEngineer
Starting Member

29 Posts

Posted - 2005-12-15 : 14:00:07

I'm attempting to create a view in SQL Server 2000 that will transpose a result set. I can get this far with a basic SELECT statement:
Board_ID, Serial_Value, Serial_Type, Serial_Number_Style_ID, Serial_Transaction_ID

Each board ID will have:
1 serial value with serial_number_style_id=1
1 serial value with serial_number_style_id=2
1 serial value with serial number style id=3
3 unique serial values with serial number style id=4
1 serial value with serial number style id=5

So the resultset is vertical. Example:
21,10001,1,1
21,AB1,2,2
21,100102011351231,3,3
21,1010100010101,4,4
21,1001012010020,4,5
21,0110110122101,4,6
21,110001010,5,7
22,10002,1,8
22,AB2,2,9
22,11010223013512,3,10
...

I'd like to create a view that will have the following schema:
BoardID,Style_1_Serial,Style_2_Serial,Style_3_Serial,Style_4_Serial_1,Style_4_Serial_2,Style_4_Serial_3,Style_5_Serial

This way I have one record per board ID. I started out by getting the above formatted result set. Then I started creating views based on each style. Ultimately, I'd create a view that joined all my individual views into the schema just above. The problem I run into is with Serial style 4. There are 3 of these per board id (all unique). I figured I can use the unique transaction ID attached to this serial to sort using Max, Min and maybe another nested select.

Can anyone help me try to figure this out?
Thanks a bunch in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources