I have a query that is several hundred columns wide with a single row. I want to manipulate the data in Excel, and having a tall, narrow result is my goal. Every column is a data value, and I'm not sure how to UNPIVOT but that seems like the correct statement to use. There are hundreds of columns like these examples:
declare @rwcnt int
select @rwcnt = count(1)
from dbo.table_name
select
sum(cast(case when ButchID is null then 1 else 0 end *100 as numeric(5,2)))/@rwcnt ButchID_null_prct
,sum(cast(case when PatID is null then 1 else 0 end *100 as numeric(5,2)))/@rwcnt PatID_null_prct
These correctly find the number of null values in a column. Do I need to add a dummy column to unpivot on?
Any help appreciated.
Thank you
select
100 - 100.0 * count(nullif(ButchID,'')) / count(1) as ButchID
,100 - 100.0 * count(nullif(PatID,'')) / count(1) as PatID
into #temp
from pat
select field_name, field_value
from #temp
unpivot (field_value for field_name in (
ButchID
,PatID)) as results
Here's the solution I came up with. Thanks for the responses!