Taking a wide, one-row query and making it a one-column result

Question

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

Answer 1

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!

Answer 2

You don't need to get the row count separately.

You can just use AVG to get it divided by the count as in this example

DECLARE @demo TABLE
(
Col1 INT NULL,
Col2 INT NULL,
Col3 INT NULL
)

INSERT @demo 
VALUES 
(1,2,NULL),
(1,2,NULL),
(1,NULL,NULL);


select
     avg((case when Col1 is null then 100.00 else 0 end)) Col1_null_prct
    ,avg((case when Col2 is null then 100.00 else 0 end)) Col2_null_prct
    ,avg((case when Col3 is null then 100.00 else 0 end)) Col3_null_prct
from @demo

Fiddle

As for how to unpivot that you can of course just copy the row from the SSMS results grid and use the "transpose" option when pasting that to Excel. To do it in SQL one way is to list out all the columns

WITH T AS
(
select
     avg((case when Col1 is null then 100.00 else 0 end)) Col1_null_prct
    ,avg((case when Col2 is null then 100.00 else 0 end)) Col2_null_prct
    ,avg((case when Col3 is null then 100.00 else 0 end)) Col3_null_prct
from @demo
)
SELECT col, val
FROM T 
UNPIVOT (val FOR col IN (Col1_null_prct, Col2_null_prct, Col3_null_prct)) U

Another way is to round trip it through XML

WITH T1 AS
(
select
     avg((case when Col1 is null then 100.00 else 0 end)) Col1_null_prct
    ,avg((case when Col2 is null then 100.00 else 0 end)) Col2_null_prct
    ,avg((case when Col3 is null then 100.00 else 0 end)) Col3_null_prct
from @demo
)
SELECT col = n.value('local-name(.)', 'sysname'), 
       val = n.value('./text()[1]', 'numeric(5,2)')
FROM (SELECT * FROM T1 FOR XML PATH('row'), TYPE) T2(x)
CROSS APPLY x.nodes('row/*') n(n)