Need help with Unpivot

Question

Need help with Unpivot

dndaughtery

SSChampion

Points: 11290
More actions
December 12, 2011 at 4:27 pm

#249596

I have a sproc that counts the number of records where certain columns from matching records in 2 tables aren't eual in value as below.
Select
sum(case when WS.[PID] <> C.[PID] then 1 else 0 end) as '[PID]'
,sum(case when WS.[Date] <> C.[Date] then 1 else 0 end) as '[Date]'
,sum(case when WS.[Address] <> C.[Address] then 1 else 0 end) as '[Address]'
,sum(case when WS.[Desc] <> C.[Desc] then 1 else 0 end) as '[Desc]'
FROM [DB_A].[dbo].[Tble_A] C Left Outer Join
[DB_B].[dbo].[Tble_B] WS
on WS.[BACLoanNum] = C.[LoanNum]
Where C.RunDt = (Select Max(RunDt) From [DB_A].[dbo].[CleanSBO])
It outputs the results in a single row but I'd like to have each column name and count in its own row such as:
Column Total_Rows_Unequal
PID 100
Date 0
Address 100000
Desc 44455
How can I do this? I'm assuming it can be done with an Unpivot but I'm having difficulty finding information on it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 1

dndaughtery (12/12/2011)
I have a sproc that counts the number of records where certain columns from matching records in 2 tables aren't eual in value as below.
Select
sum(case when WS.[PID] <> C.[PID] then 1 else 0 end) as '[PID]'
,sum(case when WS.[Date] <> C.[Date] then 1 else 0 end) as '[Date]'
,sum(case when WS.[Address] <> C.[Address] then 1 else 0 end) as '[Address]'
,sum(case when WS.[Desc] <> C.[Desc] then 1 else 0 end) as '[Desc]'
FROM [DB_A].[dbo].[Tble_A] C Left Outer Join
[DB_B].[dbo].[Tble_B] WS
on WS.[BACLoanNum] = C.[LoanNum]
Where C.RunDt = (Select Max(RunDt) From [DB_A].[dbo].[CleanSBO])
It outputs the results in a single row but I'd like to have each column name and count in its own row such as:
Column Total_Rows_Unequal
PID 100
Date 0
Address 100000
Desc 44455
How can I do this? I'm assuming it can be done with an Unpivot but I'm having difficulty finding information on it.

Treat the query you have as if it were a table in a FROM clause complete with a "table aliase" and unpivot that. Or, put your query in a CTE and Unpivot the CTE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

Instead of unpivot you can also use "cross apply" to create a row for each result found. This avoids some costly query plan steps, which can not be avoided when using "unpivot". Something like this:

select x.name,

x.value

from (

select col1, col2, col3

from dbo.yourtable

) t

cross apply (

select 'col1' as [name], t.col1 as [value]

union all select 'col2', t.col2

union all select 'col3', t.col3

) x

However, as can be seen clearly from this example, it requires the columns col1, col2, and col3 to be of the same type, or at least implicitly convertable into one and the same type. "unpivot" has the same limitation. If your data columns have different data types, you're better of sticking to showing the values per column, so that each column can be kept in it's own data type instead of having to convert all values into one common data type. In your case, each column is an int (being the result of sum()), so both solutions should work fine.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

I put that into your example plus I added an additional tip: if any of the columns are optional, the comparison you used may not give the desired result. If you change the equal comparison by isnull(nullif(),nullif()) is not null you can check for either or both of the values being null too. The trick is that in the both nullif's the parameters have to be reversed.

select x.[column], x.[Differences]

from (

Select sum(case when isnull(nullif(WS.[PID], C.[PID]),nullif(C.[PID],WS.[PID])) is not null then 1 else 0 end) as [PID]

,sum(case when isnull(nullif(WS.[Date],C.[Date]),nullif(C.[Date],WS.[Date])) is not null then 1 else 0 end) as [Date]

,sum(case when isnull(nullif(WS.[Address],C.[Address]),nullif(C.[Address],WS.[Address])) is not null then 1 else 0 end) as [Address]

,sum(case when isnull(nullif(WS.[Desc],C.[Desc]),nullif(C.[Desc],WS.[Desc])) is not null then 1 else 0 end) as [Desc]

FROM [DB_A].[dbo].[Tble_A] C Left Outer Join

[DB_B].[dbo].[Tble_B] WS

on WS.[BACLoanNum] = C.[LoanNum]

Where C.RunDt = (Select Max(RunDt) From [DB_A].[dbo].[CleanSBO])

) t

cross apply (

select 'PID' as [column], t.PID as [Differences]

union all select 'Date', t.[Date]

union all select 'Address', t.[Address]

union all select 'Desc', t.[Desc]

) x

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

dndaughtery SSChampion Points: 11290 More actions · Answer 4

dndaughtery

SSChampion

Points: 11290

December 13, 2011 at 1:05 pm

#1420848

Thanks, the cross apply did the trick!