Transpose columns in ROW2 as columns in ROW1

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

Transpose columns in ROW2 as columns in ROW1

Author

Topic

SQLNoviceUser
Starting Member

1 Post

Posted - 2011-07-18 : 17:04:26

Hi,

I have an SQL statement which finds the difference between two tables having same structure.
The query is:

select ID, NAME from (
select ID, NAME,
sum(case when src=1 then 1 else 0 end) cnt1,
sum(case when src=2 then 1 else 0 end) cnt2
from
(
select ID, NAME, 1 src from TABLE1 S1
union all
select ID, NAME, 2 src from TABLE2 S2
)
group by ID, NAME
order by ID
)
where cnt1 <> cnt2

The output of the below is:

ID NAME
------------------------
AA NAME1
AA NAME12
NL NEW
NL NEW12

I want an output as below:

ID Table1NAME Table2Name
----------------------------------------
AA NAME1 NAME12
NL NEW NEW12

So basically what I wanted is: I want to transpose columns in ROW2 as columns in ROW1.

Give me some idea on how to do it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-18 : 17:50:29

You can use PIVOT operator to pivot the results from column-wise display to row-wise display. PIVOT is available in SQL 2005 and higher. However, in your example, you may not need to do that. A query like this should work.


SELECT 
	COALESCE(s1.id,s2.id) AS Id,
	s1.Name AS Table1Name,
	s2.Name AS Table2Name
FROM 
	(SELECT Id,Name,COUNT(*) AS S1Count FROM Table1 GROUP BY Id,Name) S1
FULL JOIN
	(SELECT Id,Name,COUNT(*) AS S2Count FROM Table1 GROUP BY Id,Name) S2
		ON s1.id = s2.id AND s2.Name = s2.Name
WHERE
	ISNULL(S1Count,0) <> ISNULL(S2Count,0)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-18 : 21:10:33

(Table1
UNION
Table2)
EXCEPT
(Table1
INTERSECT
Table1)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources