Click here to Skip to main content
14,494,314 members
Rate this:
Please Sign up or sign in to vote.
See more:
i need to compare table-1 columns name with table-2 row values.

for example:-

Table-1 have 3 columns like :-

col-1 | col-2 | col-3

and Table-2 have one column with 3 row:-

col
----
col-1
col-2
col-3

now i need to compare table-1 column names with table-2 rows name. please any one help me?

What I have tried:

searching solution on google but not getting any solution right now.
Posted
Updated 25-Mar-20 23:42pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

The technique you need is UNPIVOT[^]

If you first cross join your tables
select *
	from table2
	cross join table1
You will get
col	col-1	col-2	col-3
col-1	1	2	3
col-2	1	2	3
col-3	1	2	3
As you can see - the values you want are across the top, but you want them in rows. So ..
select columnname, colvalue from
(select *
	from table2
	cross join table1
) src
unpivot (colvalue for columnname in ([col-1],[col-2],[col-3])) AS pvt
will give you
columnname	colvalue
col-1		1
col-1		1
col-1		1
col-2		2
col-2		2
col-2		2
col-3		3
col-3		3
col-3		3
Closer to what you want but you will need to use DISTINCT to get exactly what you need. I'll leave that as an exercise for you
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100