Multiple Columns to Rows - 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

General SQL Server Forums

New to SQL Server Programming

Multiple Columns to Rows

Author

Topic

ryfisher
Starting Member

2 Posts

Posted - 2010-03-30 : 14:45:58

I have a table that shows in a column if a certain indicator is present in a patient's hospital visit. If the inidcator is present, there is a 1, if it is not present, it is null.

I have a query that is suming up all the 1's in the indicator column, and counting all the rows for that measure (the are "null"), then dividing them to get a percentage.

There are about 12 of these measures, and i have them all set to do the same thing, however, they are all on one row, but have 3 columns for each row. I want to get it so that each measure is its own row, and the values are in these three columns, Numerator, Denominator, and Month Percentage.

Here is the SQL I am using, it is probably very long and not the best way to do it, but I am new to SQL and am still learning.

Thanks in advance!

with ami as (
Select sum ([ami-1_ind]) as [ami-1_num], count (*) as [ami-1_denom],sum ([ami-2_ind]) as [ami-2_num], count (*) as [ami-2_denom],
sum ([ami-3_ind]) as [ami-3_num], count (*) as [ami-3_denom],sum ([ami-4_ind]) as [ami-4_num], count (*) as [ami-4_denom],
sum ([ami-5_ind]) as [ami-5_num], count (*) as [ami-5_denom],sum ([ami-6_ind]) as [ami-6_num], count (*) as [ami-6_denom],
sum ([ami-7_ind]) as [ami-7_num], count (*) as [ami-7_denom],sum ([ami-7a_ind]) as [ami-7a_num], count (*) as [ami-7a_denom],
sum ([ami-8_ind]) as [ami-8_num], count (*) as [ami-8_denom],sum ([ami-8a_ind]) as [ami-8a_num], count (*) as [ami-8a_denom],
sum ([ami-9_ind]) as [ami-9_num], count (*) as [ami-9_denom],sum ([ami-T1a_ind]) as [ami-t1a_num], count (*) as [ami-t1a_denom],
sum ([ami-t2_ind]) as [ami-t2_num], count (*) as [ami-t2_denom]
from nmh_uhc_dm.core_measures_ami
where disch_dt_tm between '12/1/2009' and '12/31/2009'
)

select [ami-1_num], [ami-1_denom], (SUM([ami-1_num]*1.0/[ami-1_denom])) as [month perecentage],
[ami-2_num], [ami-2_denom], (SUM([ami-2_num]*1.0/[ami-2_denom])) as [month perecentage],
[ami-3_num], [ami-3_denom], (SUM([ami-3_num]*1.0/[ami-3_denom])) as [month perecentage],
[ami-4_num], [ami-4_denom], (SUM([ami-4_num]*1.0/[ami-4_denom])) as [month perecentage],
[ami-5_num], [ami-5_denom], (SUM([ami-5_num]*1.0/[ami-5_denom])) as [month perecentage],
[ami-6_num], [ami-6_denom], (SUM([ami-6_num]*1.0/[ami-6_denom])) as [month perecentage],
[ami-7_num], [ami-7_denom], (SUM([ami-7_num]*1.0/[ami-7_denom])) as [month perecentage],
[ami-7a_num], [ami-7a_denom], (SUM([ami-7a_num]*1.0/[ami-7a_denom])) as [month perecentage],
[ami-8_num], [ami-8_denom], (SUM([ami-8_num]*1.0/[ami-8_denom])) as [month perecentage],
[ami-8a_num], [ami-8a_denom], (SUM([ami-8a_num]*1.0/[ami-8a_denom])) as [month perecentage],
[ami-9_num], [ami-9_denom], (SUM([ami-9_num]*1.0/[ami-9_denom])) as [month perecentage],
[ami-t1a_num], [ami-t1a_denom], (SUM([ami-t1a_num]*1.0/[ami-t1a_denom])) as [month perecentage],
[ami-t2_num], [ami-t2_denom], (SUM([ami-t2_num]*1.0/[ami-t2_denom])) as [month perecentage]
from ami
group by [ami-1_num], [ami-1_denom],[ami-2_num], [ami-2_denom],[ami-3_num], [ami-3_denom],
[ami-4_num], [ami-4_denom],[ami-5_num], [ami-5_denom],[ami-6_num], [ami-6_denom],
[ami-7_num], [ami-7_denom],[ami-7a_num], [ami-7a_denom],[ami-8_num], [ami-8_denom],
[ami-8a_num], [ami-8a_denom],[ami-9_num], [ami-9_denom],[ami-t1a_num], [ami-t1a_denom],
[ami-t2_num], [ami-t2_denom]

ryfisher
Starting Member

2 Posts

Posted - 2010-03-30 : 16:20:08

anyone??

PackRat
Starting Member

26 Posts

Posted - 2010-03-30 : 18:51:00

maybe I'm missing something, but aren't all your _denom columns going to come out with the same count? count(*) doesn't eliminate the nulls, so I would expect all the _denom cols to end up the same value. if that's the case you can go with something like below.

sounds like you want to UNPIVOT the columns into rows
[url]http://technet.microsoft.com/en-us/library/ms177410.aspx[/url]

I can't test this syntax without sample data; but I thijnk you're trying to go in this direction;


with base as (
	select 
		 [ami-1_num],[ami-2_num],[ami-3_num],[ami-4_num]
		,[ami-5_num],[ami-6_num],[ami-7_num],[ami-7a_num]
		,[ami-8_num],[ami-8a_num],[ami-9_num],[ami-t1a_num],[ami-t2_num]
	from nmh_uhc_dm.core_measures_ami where disch_dt_tm between '12/1/2009' and '12/31/2009'
)
SELECT 
	 [Column]=pcts.col
	,[Numerator]=pcts.num
	,[Denominator]=pcts.denom
	,[Month Percentage]=pcts.num*1.0/d.denom 
FROM (
		select 
			 (select count(*) from base) denom
			,sum([ami-1_ind])   as [ami-1_num]
			,sum([ami-2_ind])   as [ami-2_num]
			,sum([ami-3_ind])   as [ami-3_num]
			,sum([ami-4_ind])   as [ami-4_num]
			,sum([ami-5_ind])   as [ami-5_num]
			,sum([ami-6_ind])   as [ami-6_num]
			,sum([ami-7_ind])   as [ami-7_num]
			,sum([ami-7a_ind])  as [ami-7a_num]
			,sum([ami-8_ind])   as [ami-8_num]
			,sum([ami-8a_ind])  as [ami-8a_num]
			,sum([ami-9_ind])   as [ami-9_num]
			,sum([ami-T1a_ind]) as [ami-t1a_num]
			,sum([ami-t2_ind])  as [ami-t2_num]
		from base
	) sums UNPIVOT (
		num FOR col in (
			 [ami-1_num],[ami-2_num],[ami-3_num],[ami-4_num]
			,[ami-5_num],[ami-6_num],[ami-7_num],[ami-7a_num]
			,[ami-8_num],[ami-8a_num],[ami-9_num],[ami-t1a_num],[ami-t2_num]
		) 
	) pcts

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 20:12:08

PackRat's query is close. I've threw some test data at it, and there were a few minor errors compiling it. Fixed those, then compared the results to the original. Seems to work fine just fine (just fine). NOTE: I've remove the WHERE clause for testing purposes, so you'll need to add it again.


CREATE TABLE #tmp (
		[ami-1_ind] INT, [ami-2_ind] INT, [ami-3_ind] INT, [ami-4_ind] INT, 
		[ami-5_ind] INT, [ami-6_ind] INT, [ami-7_ind] INT, [ami-7a_ind] INT, 
		[ami-8_ind] INT, [ami-8a_ind] INT, [ami-9_ind] INT, [ami-t1a_ind] INT, [ami-t2_ind] INT
)
GO

INSERT INTO #tmp
SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1
union ALL SELECT 1, 0, 0, 1, 0, 1, 1, 1, 1, 0, 1, 1, 1
union ALL SELECT 0, 1, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 1
union ALL SELECT 1, 1, 1, 1, 0, 1, 0, 1, 1, 0, 0, 0, 1
union ALL SELECT 1, 0, 1, 1, 0, 1, 0, 1, 1, 0, 1, 0, 1
union ALL SELECT 1, 0, 0, 1, 0, 1, 0, 1, 0, 1, 1, 1, 1;
GO

-- UNPIVOTed query
with base as (
	select 
		 [ami-1_ind],[ami-2_ind],[ami-3_ind],[ami-4_ind]
		,[ami-5_ind],[ami-6_ind],[ami-7_ind],[ami-7a_ind]
		,[ami-8_ind],[ami-8a_ind],[ami-9_ind],[ami-t1a_ind],[ami-t2_ind]
	from #tmp
)

SELECT 
	 [Column]=pcts.col
	,[Numerator]=pcts.num
	,[Denominator]=pcts.denom
	,[Month Percentage]=pcts.num*1.0/pcts.denom 
FROM (
		select 
			 (select count(*) from base) denom
			,sum([ami-1_ind])   as [ami-1_num]
			,sum([ami-2_ind])   as [ami-2_num]
			,sum([ami-3_ind])   as [ami-3_num]
			,sum([ami-4_ind])   as [ami-4_num]
			,sum([ami-5_ind])   as [ami-5_num]
			,sum([ami-6_ind])   as [ami-6_num]
			,sum([ami-7_ind])   as [ami-7_num]
			,sum([ami-7a_ind])  as [ami-7a_num]
			,sum([ami-8_ind])   as [ami-8_num]
			,sum([ami-8a_ind])  as [ami-8a_num]
			,sum([ami-9_ind])   as [ami-9_num]
			,sum([ami-T1a_ind]) as [ami-t1a_num]
			,sum([ami-t2_ind])  as [ami-t2_num]
		from base
	) sums UNPIVOT (
		num FOR col in (
			 [ami-1_num],[ami-2_num],[ami-3_num],[ami-4_num]
			,[ami-5_num],[ami-6_num],[ami-7_num],[ami-7a_num]
			,[ami-8_num],[ami-8a_num],[ami-9_num],[ami-t1a_num],[ami-t2_num]
		) 
	) pcts;

-- Original query
with ami as (
Select sum ([ami-1_ind]) as [ami-1_num], count (*) as [ami-1_denom],sum ([ami-2_ind]) as [ami-2_num], count (*) as [ami-2_denom],
sum ([ami-3_ind]) as [ami-3_num], count (*) as [ami-3_denom],sum ([ami-4_ind]) as [ami-4_num], count (*) as [ami-4_denom],
sum ([ami-5_ind]) as [ami-5_num], count (*) as [ami-5_denom],sum ([ami-6_ind]) as [ami-6_num], count (*) as [ami-6_denom],
sum ([ami-7_ind]) as [ami-7_num], count (*) as [ami-7_denom],sum ([ami-7a_ind]) as [ami-7a_num], count (*) as [ami-7a_denom],
sum ([ami-8_ind]) as [ami-8_num], count (*) as [ami-8_denom],sum ([ami-8a_ind]) as [ami-8a_num], count (*) as [ami-8a_denom],
sum ([ami-9_ind]) as [ami-9_num], count (*) as [ami-9_denom],sum ([ami-T1a_ind]) as [ami-t1a_num], count (*) as [ami-t1a_denom],
sum ([ami-t2_ind]) as [ami-t2_num], count (*) as [ami-t2_denom]
from #tmp
)

select [ami-1_num], [ami-1_denom], (SUM([ami-1_num]*1.0/[ami-1_denom])) as [month perecentage],
[ami-2_num], [ami-2_denom], (SUM([ami-2_num]*1.0/[ami-2_denom])) as [month perecentage],
[ami-3_num], [ami-3_denom], (SUM([ami-3_num]*1.0/[ami-3_denom])) as [month perecentage],
[ami-4_num], [ami-4_denom], (SUM([ami-4_num]*1.0/[ami-4_denom])) as [month perecentage],
[ami-5_num], [ami-5_denom], (SUM([ami-5_num]*1.0/[ami-5_denom])) as [month perecentage],
[ami-6_num], [ami-6_denom], (SUM([ami-6_num]*1.0/[ami-6_denom])) as [month perecentage],
[ami-7_num], [ami-7_denom], (SUM([ami-7_num]*1.0/[ami-7_denom])) as [month perecentage],
[ami-7a_num], [ami-7a_denom], (SUM([ami-7a_num]*1.0/[ami-7a_denom])) as [month perecentage],
[ami-8_num], [ami-8_denom], (SUM([ami-8_num]*1.0/[ami-8_denom])) as [month perecentage],
[ami-8a_num], [ami-8a_denom], (SUM([ami-8a_num]*1.0/[ami-8a_denom])) as [month perecentage],
[ami-9_num], [ami-9_denom], (SUM([ami-9_num]*1.0/[ami-9_denom])) as [month perecentage],
[ami-t1a_num], [ami-t1a_denom], (SUM([ami-t1a_num]*1.0/[ami-t1a_denom])) as [month perecentage],
[ami-t2_num], [ami-t2_denom], (SUM([ami-t2_num]*1.0/[ami-t2_denom])) as [month perecentage]
from ami
group by [ami-1_num], [ami-1_denom],[ami-2_num], [ami-2_denom],[ami-3_num], [ami-3_denom],
[ami-4_num], [ami-4_denom],[ami-5_num], [ami-5_denom],[ami-6_num], [ami-6_denom],
[ami-7_num], [ami-7_denom],[ami-7a_num], [ami-7a_denom],[ami-8_num], [ami-8_denom],
[ami-8a_num], [ami-8a_denom],[ami-9_num], [ami-9_denom],[ami-t1a_num], [ami-t1a_denom],
[ami-t2_num], [ami-t2_denom]
GO

DROP TABLE #tmp
GO

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources