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

by

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

  • 3

    If you are going to manipulate in Excel, why not do the UNPIVOT (I.e. transpose) in Excel rather than SQL?

    – 

  • @NickW I hadn’t thought of that. Looking to learn some SQL first.

    – 

  • 1

    PIVOT/UNPIVOT in SQL tends to be complex/inflexible – so people often try and avoid it unless the pivot capabilities of your DBMS happen to match what you are trying to achieve

    – 

  • I’ll second @nickw. If you are ultimately looking to pivot this for use in excel, you’d be better served doing it there. Pivot requires each header to be listed, either statically or dynamically using something like string_agg. You’d be better off doing it in excel in this instance.

    – 

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)

Leave a Comment