I need Four Column, Single Row derived table

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

SQL Server Development (2000)

I need Four Column, Single Row derived table

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-01-24 : 13:08:30

Hiya,
I'd like to get a single row, four column derived table like this:
Voucher Diag1 Diag2 Diag3 Diag4
10 783.4 852.6 621.2 687.1

From such data:
Voucher Diag1 Diag2 Diag3 Diag4
10 783.4 NULL NULL NULL
10 NULL 852.6 687.1 NULL
10 621.2 NULL NULL NULL

The diagnosis numbers do not matter (e.g. if in my table 687.1 is in diagnosis3, row 2, I don't care if it's diagnosis 1 in the derived table). What I really want is all the non-null diagnoses in a voucher with n rows, not like:
select Coalesce(diag1,diag2,diag3,diag4)AS Diag... where voucher = 10
will return one value for each row in the voucher like this:
Diag
783.4
852.6
621.2
BTW,I'd rather have four columns than one long concatenated string, which can also be done with coalesce, so I can join the derived table on my outer query.

Thanks!

Sarah Berger MCSD

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-24 : 20:55:08

Well, this was quite a mind bender, I could only make it work for say voucher 10.. With a little modification you could probably make it work for all your vouchers.. Now this solution uses dynamic sql.
I think I can see about 3 major steps.
Step1) To get your table to something like


voucher value
------- -----
10      783.4
10      852.6
10      687.1
10      621.2

Now, the only way I could see you achieving this is by doing UNION selects on each field, eg.

 
Select voucher, diag1 value where diag1 is not null and voucher = 10
UNION
Select voucher, diag2 value where diag2 is not null and voucher = 10
...

Step2) You create the string that will be your SELECT INTO statement

 
Declare @Cols varchar(200)
Declare @SQLStr varchar(2000)
Declare @Counter int
Set @Counter = 1
set @Cols = ''

select @Cols = @Cols +  cast(A.value as varchar(10)) + 
        ' Diag' + cast(@Counter as varchar(2)) + ', ',
	@Counter = @Counter + 1
from 
(Select voucher, diag1 value where diag1 is not null and voucher = 10
UNION
Select voucher, diag2 value where diag2 is not null and voucher = 10
...) A

set @Cols = left(@Cols, len(@Cols) - 1)
set @SQLStr = 'select 1 voucher, ' + @Cols + ' INTO #ALLDIAG '

Step3) exec(@SQLStr)

Now you can use #ALLDIAG to join on, or what ever... Hope that helps..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-24 : 20:59:36

Sorry, the top will build a string like:

 
Print @SLQStr

select 1 voucher, 1 Diag1, 2 Diag2, 3 Diag3, 4 Diag4, 5 Diag5, 6 Diag6, 1 Diag7 INTO #ALLDIAG

thanks

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-24 : 21:57:31

Sarah,

Here is query that will do the trick for you in one pass...

I have called the table "Voucher"

It is really just a complex CASE Statement.....



Select Voucher, 
	Case when max(Diag1) is null 
		then Case when Count(Diag2) > 1 then min(Diag2) 
			  else Case when Count(Diag3) > 1 then min(Diag3) 
				else Case when Count(Diag4) > 1 then min(Diag4) 
					else Max(Diag1) 
				      end
			  	end
			  end	
	else Max(Diag1) end as Diag1,
	Case when max(Diag2) is null 
		then Case when Count(Diag1) > 1 then min(Diag1) 
			  else Case when Count(Diag3) > 1 then min(Diag3) 
				else Case when Count(Diag4) > 1 then min(Diag4) 
					else Max(Diag2) 
				      end
			  	end
			  end	
	else Max(Diag2) end as Diag2,
	Case when max(Diag3) is null 
		then Case when Count(Diag2) > 1 then min(Diag2) 
			  else Case when Count(Diag1) > 1 then min(Diag1) 
				else Case when Count(Diag4) > 1 then min(Diag4) 
					else Max(Diag3) 
				      end
			  	end
			  end	
	else Max(Diag3) end as Diag3,
	Case when max(Diag4) is null 
		then Case when Count(Diag2) > 1 then min(Diag2) 
			  else Case when Count(Diag3) > 1 then min(Diag3) 
				else Case when Count(Diag1) > 1 then min(Diag1) 
					else Max(Diag4) 
				      end
			  	end
			  end	
	else Max(Diag4) end as Diag4
from Voucher
group by Voucher

DavidM

Tomorrow is the same day as Today was the day before.

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-24 : 22:50:19

Dave, but wont that just limit it to 4 columns??

quote:
...What I really want is all the non-null diagnoses in a voucher with n rows...

I was thinking more along the lines of generating a column for each of the diagnoses, as to Sarah's requirements?? That's why I thought it was a bit of a challenge.

I suppose Sarah will have to be clearer about what she really wants..

Happy birthday to me

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-24 : 22:57:40

fisherman_jake,

Of course it will, that is what she asked for!!!

Re-read the statement "n ROWS"!!!!!

DavidM

Tomorrow is the same day as Today was the day before.

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-24 : 23:14:28

Yes, but she did also SAY

quote:
...not like:
select Coalesce(diag1,diag2,diag3,diag4)AS Diag... where voucher = 10
...

What happens when you have 8 none null values in 2 rows, will you only return 4 values??

quote:
...What I really want is all the non-null diagnoses in a voucher ...

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-01-28 : 13:32:17

Hiya,
I'll try to be clearer, although this query is a very illogical thing to begin with, as 'most everything in the medical field is.
Ideally, I only want four columns in one row representing the first four nonidentical,nonnull diagnoses, which are not necessarily in one row in the table. The reason for four: An insurance form only has space for four, and I'm using this resultset for a claim form report.

quote:
What happens when you have 8 none null values in 2 rows, will you only return 4 values??

Quick thinking, Fisherman_Jake. This is a problem, but has no decent solution. If a claim has e.g. 3 diagnosis on line 1, 2 diff. diagnoses on line 2, and 2 diff. diagnoses on line 3, diagnosis 2 from line 2, and both diagnoses from line 3 will be skipped. It's an uncommon situation,thankfully, because usually a single claim contains related items, but it's possible.

Also,to make things easier: Diag1 may never be null. So basically that leaves us with 3 possible nulls for each line.

I can easily see how to do this with a cursor, but I need this in s/t like a derived table so I can join it with the outer part of the query which contains all the rest of the claim info on its voucher number. If it's in a #table or cursor, I'll get two resultsets, which the reporting tool won't be able to bind to.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources