Need some advice on Pivot.

[demopro]

Hello,
I am stuck on trying to figure out the best solution to the issue below. Any thoughts on this would be greatly appreciated. I have solved this by using a pivot (code at bottom) but want to see if there is another way to do it that is more efficient. This data resides in MSSQL 2005.

Table format:
ID(PK) ActID DisplayField ValueField
1 AA1 Machine PC
2 AA1 DD UNK
3 AA1 TCP 24

Desired format:
ActID DisplayField DisplayField DisplayField
ValueField ValueField ValueField

Example:
ActID Machine DD TCP
AA1 PC UNK 24

The current table has the ActID field multiply times in individual rows. In the desired format I want all data from the ActID field on one row.

For each ActID there can be more than 1 ID (PK) field. For each ActID field there can be anywhere from 1 to 10 or more records for each.
In the example above I display 3 records for this ActID but the next ActID could have 19 records to it and so on.
This table will contain 100,000 to 150,000 records.




Here is the code from the pivot:

SELECT *
FROM (SELECT ActID, DisplayField, ValueField
FROM TableNAME) AS D PIVOT (MAX([ValueField])
FOR DisplayField IN ([Closed TCP Ports], [Closed UDP Ports], [Credentials], [Detection Method], [Device Type], [Domain Name], [Machine], [NetBIOS], [Netbios Domain/Group], [Netbios Name], [NetBIOS-Desc], [Open or Filtered UDP Ports], [Open TCP Ports], [OS Detected], [OS Name], [REM NAV], [Remote MAC], [Vendor], [Version])) AS P;

 

[AlexCuse]

want to see if there is another way to do it that is more efficient

Do it in your reporting client

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[demopro]

AlexCuse, thanks for the advice but I need to put this into a table. Sorry, I didn’t mention it before.