Converting vertical data to horizontal in SQL 2000

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

General SQL Server Forums

New to SQL Server Programming

Converting vertical data to horizontal in SQL 2000

Author

Topic

Simonje
Starting Member

5 Posts

Posted - 2012-09-03 : 10:42:14

I have a view that contains two columns of data:

[Invoice Line ID] and [Approver Name]

Some of the [Invoice Line ID]'s have multiple [Approver Name] entries {possibly as many as 10 in some cases} in the ouput and an indicative output might look like below:

Table: [Supplier_Invoice_ApproverNames]
[Invoice Line ID] [Approver Name]
163804 john.brown
163805 john.brown
192667 bill.swinton
192667 paul.knowles
192667 john.brown
182216 brian.lawrence
...

I need to produce a result set that looks like the following:

[Invoice Line ID] [Approver Names]
163804 john.brown
163805 john.brown
192667 bill.swinton or paul.knowles or john.brown
182216 brian.lawrence
...

I have looked at the PIVOT function and using a cte, but they don't seem to work in SQL 2000. Can anyone show me how to do this in SQL 2000?

Many Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 15:08:23

you need to use a udf for this.

create a udf like


CREATE FUNCTION ConcatApprovers
(
@InvoiceLineID int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @ApproverList varchar(8000)

SELECT @ApproverList = COALESCE(@ApproverList + ' or ','') + [Approver Name]
FROM [Supplier_Invoice_ApproverNames]
WHERE [Invoice Line ID] = @InvoiceLineID

RETURN (@ApproverList)
END


then call it like


SELECT DISTINCT [Invoice Line ID],dbo.ConcatApprovers([Invoice Line ID]) AS [Approver Names]
FROM [Supplier_Invoice_ApproverNames]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Simonje
Starting Member

5 Posts

Posted - 2012-09-04 : 05:18:58

Thanks Visakhm, this end result is exactly what I am looking for.

However the view I am running this on currently only has 284 rows (and takes 2 seconds to open) but this function takes 11 minutes 53 seconds to run against this view. I then need to take this result set and append it to some data that exists in another view with the ultimate aim to deploy the final dataset onto a web dashboard, but no-one is going to wait 12 minutes for the web page to poupulate.

I have also been given an alternative script, which generates the same results, though is less dynamic {as you effectively need to define an upper limit for the maximum number of approvers per ID}, which only takes 30 seconds to run:

SELECT [Invoice Line ID],

RTRIM(MAX( CASE seq WHEN 1 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 2 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 3 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 4 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 5 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 6 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 7 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 8 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 9 THEN [Approver Name] ELSE '' END ) +
' ' + MAX( CASE seq WHEN 10 THEN [Approver Name] ELSE '' END ))

FROM ( SELECT p1.[Invoice Line ID], p1.[Approver Name],

( SELECT COUNT(*)

FROM [wkc_Supplier_Invoices_ApproverList] p2

WHERE p2.[Invoice Line ID] = p1.[Invoice Line ID]

AND p2.[Approver Name] <= p1.[Approver Name] )

FROM [wkc_Supplier_Invoices_ApproverList] p1 ) D ( [Invoice Line ID], [Approver Name], seq )

GROUP BY [Invoice Line ID]
ORDER BY [Invoice Line ID]

But when I add this functionality to the script that appends the main view and this additional data together it exceeds 1 minute processing time and as their are 2 similar scripts runing simoultaneously on the web dashboard page I get a SQL Timeout error for at least one (sometimes both) of the scripts every time.

Is there any way to improve the processing speed of either of the above options?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-05 : 21:23:37

if its a frequently executing query, you can consider making it an indexed view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources