Database

Use myDatabase
GO

select so.name,sc.name 
from sysobjects so, syscolumns sc 
where so.xtype = 'U' 
  and so.name = 'myTable1' 
  and so.id = sc.id

Col1    Col2    Col3
10      20      30
100     200     300
1000    2000    3000
10000   20000   30000

SELECT T2.CODE, T1.COLS,T1.INDICOLVALS 
FROM 
(
	SELECT INDICOLVALS,COLS

    FROM 

         (SELECT Col1, Col2, Col3 FROM Table1) P

    UNPIVOT

         (INDICOLVALS FOR COLS IN (Col1, Col2, Col3)) AS U
) AS T1

INNER JOIN TABLE2 T2

ON T1.INDICOLVALS = 10*T2.CODE

declare @cols nvarchar(2000)
declare @sql nvarchar(4000)

set @cols='Col1, Col2, Col3'

set @query='SELECT T2.CODE, 
T1.COLS,
T1.INDICOLVALS 
FROM 
(	
SELECT INDICOLVALS,COLS    FROM          

(SELECT Col1, Col2, Col3 FROM Table1) P    
UNPIVOT         
(INDICOLVALS FOR COLS IN ('+ @cols +')
) AS U
) AS T1
INNER JOIN TABLE2 T2
ON T1.COLS = T2.[Name]'

--print @query

execute(@query)

Select name from syscolumns where id=object_id('Table 1') 

SELECT C.CARCODE
FROM (
			SELECT A.CARCODE,A.AMMCODE 
			FROM (SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty 
			WHERE AMMCODE = 1) A

			INNER JOIN 

			(SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty 
			WHERE AMMCODE = 2) B

			ON A.CARCODE = B.CARCODE) C

		INNER JOIN 

(SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty 
WHERE AMMCODE = 3) D

ON C.CARCODE = D.CARCODE

SELECT CarCode
FROM tblCarAmnesty
WHERE AmmCode in (1,2) 
GROUP BY CarCode
HAVING COUNT(*) = 2

SELECT A.CarCode 
FROM tblCarAmnesty A, tblCarAmnesty B
WHERE A.CarCode = B.CarCode 
  AND A.AmmCode = 1
  AND B.AmmCode = 2

SELECT CarCode
FROM tblCarAmnesty
WHERE AmmCode in (1,2,3) 
GROUP BY CarCode
HAVING COUNT(*) = 3

SELECT A.CarCode 
FROM tblCarAmnesty A, tblCarAmnesty B,tblCarAmnesty C
WHERE 
	    A.CarCode = B.CarCode 
	AND B.CarCode = C.CarCode
  
		AND A.AmmCode = 1
		AND B.AmmCode = 2
		AND C.AmmCode = 3