Posted by wgerrard on 12/23/05 13:56

I have the following table (which is an import from another system I
can't mod):-

CREATE TABLE [tbl_wsg_maternity_observations] (
[documentname] [varchar] (40),
[clientguid] [decimal](16, 0) ,
[docguid] [decimal](16, 0) ,
[displayname] [varchar] (80),
[valuetext] [varchar] (255) ,
[valuenum] [float] NULL
) ON [PRIMARY]
GO

Where
documentname is the name of the document
clientguid is the unique identifier for my patient
docguid is the unique id for the document
displayname is the dataitem (e.g. diagnosis)
valuetext is the "answer" (e.g. kidney failure)
valuenum is used instead if the valuetext is an integer (e.g.
number of toes)

I am trying to split/change this table so that I have a different table
per document, with one row per patient occurance with the displaynames
as columns.

I have been using the following but it is slow and for large tables
takes hours (literally) to run:-

SELECT distinct
clientguid,
(SELECT DISTINCT case when t2.[ValueText] is null then
cast(t2.[Valuenum] as varchar(10)) else t2.[ValueText]end FROM
tbl_wsg_maternity_observations t2 WHERE 'How many vessels present in
cord' = t2.[Displayname] AND t1.ClientGUID = t2.ClientGUID AND
t1.docGUID = t2.docGUID) as [How many vessels present in cord],

<SNIP...more identical lines, one per dataitem>

INTO tbl_wsg_baby_delivery_details

FROM
tbl_wsg_maternity_observations t1

WHERE
documentname = 'Mat Baby Delivery Details'


Does anyone have any ideas how to do this faster and preferably more
simply?

Will

[Back to original message]